Find record on form with Composite PK

[QueenKirsty]

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.findrecor d before but I can't see how to do this with a composite PK.

Any suggestions?

Thanks
Kirsty

[ChipR]

I use something like:

Dim rs As Object   'rs short for recordset
Dim strWhere As String
 
Set rs = Me.RecordsetClone
 
strWhere = "[field1] = " & value1 & " AND [field2] = " & value2
 
rs.FindFirst strWhere
 
If Not rs.nomatch Then
    Me.Bookmark = rs.Bookmark
End If
 
set rs = Nothing

[QueenKirsty]

Excellent. I'll try that!

Thanks.

[NeoPa]

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?

[ADezii]

@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)!

Dim rs As DAO.Recordset
Dim strWhere As String
Dim varBookmark As Variant
 
varBookmark = Me.Bookmark
 
Set rs = Me.Recordset
 
strWhere = "[LastName] = 'NeoPa'"
 
rs.FindFirst strWhere
 
If Not rs.NoMatch Then
  'No action needed, the Current Record in the Recordset
  'will now become the Form's Current Record
Else
  'Even though the Criteria is not met, the First Record now
  'becomes the Current Record, so the Original Bookmark must be
  'restored
    MsgBox "The Criteria of " & strWhere & " could not be found"
      Me.Bookmark = varBookmark
End If
 
Set rs = Nothing 

[Stewart Ross]

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

[NeoPa]

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.