Example of how to limit DoCmd.FindRecord to a specific column?

[mdlueck]

Greetings,

To enhance the UI of the application I am building, I decided to locate the newly added record on the list of records form. To force that "record list" form to jump to the newly added record, I am using: DoCmd.FindRecord as in:

  'Update the Form UI with the table contents
  Me.RecordSource = "tmptblqry_products"
  Me.Refresh
  
  'Locate the record if we received a valid ID
  If intRecordID > 0 Then
    Call DoCmd.FindRecord(intRecordID)
  End If

Since I am using Surrogate Keys in my database, the ID is just a number.

How can I limit the FindRecord API to only searching the ID column looking for the match?

What I have coded at least works, but I would like to lock down the FindRecord() call to only searching through the ID column.

And I have an index on that column of the FE temp table. I assume FindRecord() will use indexes if provided?

Thanks!

 

[vbaInet]

Look into Findfirst of a recordset.

 

[mdlueck]

All right...

Syntax
expression.FindFirst(Criteria)
expression A variable that represents a Recordset object.

So how do I obtain the Form's Recordset object? This does not work...

  Dim daoRS As DAO.Recordset

  'Update the Form UI with the table contents
  Me.RecordSource = "tmptblqry_projects"
  Me.Refresh
  
  'Locate the record if we received a valid ID
  If intRecordID > 0 Then
    daoRS = Me.Recordset
    daoRS.FindFirst ("[id] = " & intRecordID)
  End If

Compile error:
Invalid use of property

Indicating the daoRS in line:
daoRS = Me.Recordset

 

[vbaInet]

Here's some aircode:

dim rs as dao.recordset

' set it to a clone of the form's recordset so that whatever you do doesn't affect the form just yet
set rs = me.recordset[COLOR=Red]clone[/COLOR]

rs.findfirst "[id]" & intRecordID

' if there's a match, set the form's bookmark to that of the rs' bookmark
if not rs.nomatch then
    me.bookmark = rs.bookmark
end if

set rs = nothing

 

[mdlueck]

Yes, of course... must use Set when working with DAO objects, dohh!

Me.RecordsetClone did not do what I needed it to. However removing the "clone" part arrived at the desired result:

  Dim daoRS As DAO.Recordset

  'Update the Form UI with the table contents
  Me.RecordSource = "tmptblqry_projects"
  Me.Refresh

  'Locate the record if we received a valid ID
  If intRecordID > 0 Then
    Set daoRS = Me.Recordset
    daoRS.FindFirst ("[id] = " & intRecordID)
  End If

Thank you for your assistance, vbaInet!

 

[vbaInet]

You're welcome! ... but

... the logic of your code isn't right. You need to use the syntax I gave, especially the IF block I wrote. And you still need the clone for that. This is how you work with the recordset's FindFirst method. Please read the comments.

On the other hand, if you want the cursor to jump to the first record for anytime a match isn't found, then your code is sufficient... with clone and setting the bookmarks.

 

[mdlueck]

This is code to jump to the newly added record. I have already received good return codes from API's adding the record, thus the ID is not 0. So this code will only get executed if we have a valid ID, thus guaranteed that it IS in the table.

This code is connected to the Commit button of the Add Record forms.

The form's Load() API also calls this same RefreshUI API as it also enables / disables buttons based on user perms. In that case, the number is 0 and this locate record logic is skipped.

I think I am covered well enough... hhhmmm....

 

[vbaInet]

As long as you're happy with it

 

[mdlueck]

All right, after lunch and a brisk walk, I got your version working. My implementation of it is as follows:

  'Locate the record if we received a valid ID
  If intRecordID > 0 Then
    Set daoRS = Me.RecordsetClone
    daoRS.FindFirst ("[id] = " & intRecordID)
    'If we could find the newly added record, jump to it
    If Not daoRS.NoMatch Then
      Me.Bookmark = daoRS.Bookmark
    End If
  End If

Updating my code. Thanks!

 

[vbaInet]

All right, after lunch and a brisk walk,

Good strategy!