Wrox Programmer Forums
|
BOOK: Access 2010 VBA Programmer's Reference
This is the forum to discuss the Wrox book Access 2010 Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Jerry Dennison; ISBN: 978-0-470-59166-6
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2010 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 6th, 2013, 12:50 PM
Registered User
 
Join Date: Apr 2013
Posts: 6
Thanks: 3
Thanked 1 Time in 1 Post
Default Using Bookmark and RecordsetClone

Hello,

I'm trying to use the Bookmark and RecordsetClone code from pages 194-195.
I am receiving an error Me.PCKey Method or data Member not found
What am I doing incorrectly? I have tried this in the After Update event and the On Click event of a Save Command Button

Thanks,

Here is my Code

On Error GoTo Error_Handler
Dim db As Database
Dim rstPC As DAO.rstordset
Dim rstSpecFrom As DAO.rstordset
Dim rstSpecTo As DAO.Recordset
Dim lngPCKey As Long

Set db = CurrentDb

If Not IsNull(Me.PCKey) Then

Set rstPC = db.OpenRecordset("tblPC", dbOpenDynaset)

' copy the parent record and remember its key
rstPC.AddNew
rstPC!PCName = "Copy of " & Me!PCName
rstPC.Update
rstPC.Bookmark = rstPC.LastModified
lngPCKey = rstPC!PCKey

rstPC.Close
Set rstPC = Nothing

Set rstSpecTo = db.Openrstordset("tblSpecification", dbOpenDynaset)
Set rstSpecFrom = db.OpenRecordset _
("Select * From tblSpecification Where PCKey = " & Me!PCKey)

Do While Not rstSpecFrom.EOF

rstSpecTo.AddNew
rstSpecTo!PCKey = lngPCKey 'set to the new parent key
rstSpecTo!SpecificationName = rstSpecFrom!SpecificationName
rstSpecTo!SpecificationQty = rstSpecFrom!SpecificationQty
rstSpecTo.Update

rstSpecFrom.MoveNext
Loop

rstSpecTo.Close
Set rstSpecTo = Nothing
rstSpecFrom.Close
Set rstSpecFrom = Nothing

Me.Requery

'reposition form to new record
Set rstPC = Me.RecordsetClone
rstPC.FindFirst "PCKey = " & lngPCKey
If Not rstPC.EOF Then
Me.Bookmark = rstPC.Bookmark
End If
rstPC.Close
Set rstPC = Nothing

End If


Exit_Procedure:
On Error Resume Next
Set db = Nothing
Exit Sub

Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

Last edited by poolmd; April 6th, 2013 at 01:50 PM.. Reason: Added code
 
Old April 7th, 2013, 09:46 AM
Authorized User
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hi,

The form needs to have a control named PCKey that is bound to the primary key field of the tblPC that you've created.

The purpose of this code is to copy a PC record and its Specification records, so you should have a Copy button on the form and place the code in the OnClick event.

HTH.

Malc.
The Following User Says Thank You to malcolmdixon For This Useful Post:
poolmd (April 7th, 2013)
 
Old April 7th, 2013, 08:00 PM
Registered User
 
Join Date: Apr 2013
Posts: 6
Thanks: 3
Thanked 1 Time in 1 Post
Default

I appreciate the info. That fixes the PCKey issue. I don't think I have a good understanding of what this is doing. Now I get an error "cannot find input table tblPC. Am I supposed to use a direct reference to the actual table name. I tried that and now get an error cannot find PCName
 
Old April 8th, 2013, 02:01 AM
Authorized User
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hi again,

Your table must have a field called PCName. If I understand correctly your table is not called tblPC but something else?

You'll also need another table called tblSpecification with 3 fields, PCKey, SpecificationName and SpecificationQty. Ensure the two tables are populated with data of at least one PC and its specification records.

HTH.

Malc.
The Following User Says Thank You to malcolmdixon For This Useful Post:
poolmd (April 8th, 2013)
 
Old April 8th, 2013, 05:26 AM
Registered User
 
Join Date: Apr 2013
Posts: 6
Thanks: 3
Thanked 1 Time in 1 Post
Default

I really appreciate your response. That clears up the concept. I will experiment with that. I had gone ahead and used macros to accomplish what I needed on my forms using 2 macros. One using requery and a variable for the OldIDValue. Then a FindIDValue macro to find the old ID value. This seems to work OK.
Thanks again!
 
Old April 8th, 2013, 10:49 AM
Authorized User
 
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hi,

Glad you got your form working as required, but macros make me shudder! Give me VBA any day.

Malc.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Bookmark problem ybg1 Access VBA 3 November 16th, 2011 04:12 PM
inserting a break within a bookmark cjcant Word VBA 1 June 28th, 2011 06:42 AM
Me.Bookmark = Me.RecordsetClone.Bookmark rkellogg Access VBA 5 September 8th, 2006 06:28 AM
Bookmark Style Terry Joseph Migliorino CSS Cascading Style Sheets 2 February 10th, 2006 07:52 AM
Altering a recordsetclone search?? timmaher Access VBA 2 May 15th, 2005 10:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.