Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 6th, 2013, 12:50 PM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #2 (permalink)  
Old April 7th, 2013, 09:46 AM
Authorized User
Points: 380, Level: 6
Points: 380, Level: 6 Points: 380, Level: 6 Points: 380, Level: 6
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
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.
Reply With Quote
The Following User Says Thank You to malcolmdixon For This Useful Post:
poolmd (April 7th, 2013)
  #3 (permalink)  
Old April 7th, 2013, 08:00 PM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #4 (permalink)  
Old April 8th, 2013, 02:01 AM
Authorized User
Points: 380, Level: 6
Points: 380, Level: 6 Points: 380, Level: 6 Points: 380, Level: 6
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
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.
Reply With Quote
The Following User Says Thank You to malcolmdixon For This Useful Post:
poolmd (April 8th, 2013)
  #5 (permalink)  
Old April 8th, 2013, 05:26 AM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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!
Reply With Quote
  #6 (permalink)  
Old April 8th, 2013, 10:49 AM
Authorized User
Points: 380, Level: 6
Points: 380, Level: 6 Points: 380, Level: 6 Points: 380, Level: 6
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:59 AM.


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