By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Find record on form with Composite PK

P: 14
Hi. I am trying to move to a particular record on a form where the form has a composite PK.

The PK is made of 3 fields: ModuleID, Precedence and RecordTypeID

I have always used docmd.findrecord before but I can't see how to do this with a composite PK.

Any suggestions?

Thanks
Kirsty
Nov 12 '09 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,287
I use something like:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As Object   'rs short for recordset
  2. Dim strWhere As String
  3.  
  4. Set rs = Me.RecordsetClone
  5.  
  6. strWhere = "[field1] = " & value1 & " AND [field2] = " & value2
  7.  
  8. rs.FindFirst strWhere
  9.  
  10. If Not rs.nomatch Then
  11.     Me.Bookmark = rs.Bookmark
  12. End If
  13.  
  14. set rs = Nothing
Nov 12 '09 #2

P: 14
Excellent. I'll try that!

Thanks.
Nov 12 '09 #3

NeoPa
Expert Mod 15k+
P: 31,273
Nice solution Chip.

For my curiosity, what would happen if one were to use Recordset instead of RecordsetClone? Would the Bookmark code still be required? Would there be any negative side-effects?
Nov 15 '09 #4

ADezii
Expert 5K+
P: 8,607
@NeoPa
This is my understanding of how Recordset works in this specific situation. Assuming that I am correct, its behavior is quirky to say the least. I really didn't have much time to investigate this further, so take it with a grain of salt (LOL)!
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim strWhere As String
  3. Dim varBookmark As Variant
  4.  
  5. varBookmark = Me.Bookmark
  6.  
  7. Set rs = Me.Recordset
  8.  
  9. strWhere = "[LastName] = 'NeoPa'"
  10.  
  11. rs.FindFirst strWhere
  12.  
  13. If Not rs.NoMatch Then
  14.   'No action needed, the Current Record in the Recordset
  15.   'will now become the Form's Current Record
  16. Else
  17.   'Even though the Criteria is not met, the First Record now
  18.   'becomes the Current Record, so the Original Bookmark must be
  19.   'restored
  20.     MsgBox "The Criteria of " & strWhere & " could not be found"
  21.       Me.Bookmark = varBookmark
  22. End If
  23.  
  24. Set rs = Nothing 
Nov 15 '09 #5

Expert Mod 2.5K+
P: 2,545
Hi NeoPa. There is a side-effect of using the Recordset property that I think rules it out for searching/matching applications. From the help file (with emphasis added):

"The Recordset property returns the recordset object that provides the data being browsed in a form, report, list box control, or combo box control. If a form is based on a query, for example, referring to the Recordset property is the equivalent of cloning a Recordset object by using the same query. However, unlike using the RecordsetClone property, changing which record is current in the recordset returned by the form's Recordset property also sets the current record of the form."

Using RecordsetClone, the underlying recordset has not been altered no matter what is done before or after the search. If the Recordset property was to be used, any FindFirst, MoveFirst or the like would alter the current record position in the form itself.

-Stewart
Nov 15 '09 #6

NeoPa
Expert Mod 15k+
P: 31,273
Thanks for these chaps.

I would have thought then (from what's been posted here) that (disregarding ADezii's point about when a match is not found for a second) this would actually be quite appropriate, as that is exactly what is intended in this situation (to move the record currently being browsed on the form itself).

Having said that, we cannot disregard ADezii's point (that we ignored temporarily for clarity) totally. It seems that, after all, this is less helpful than the original technique of using the RecordsetClone property, due to the unfortunate side-effect of setting the current record to the start where no match is found.

Back to where we started with Chip's post then, but at least I (& hopefully other curious readers) have a clearer understanding of what is going on. My thanks again to all who contributed.
Nov 15 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.