Access VBA select a record on a form (2000, 2003)

Topic

New Reply

Hi,

I have a form based on a query.
The form’s recordset is filtered by setting its Filter property through some code, which works fine.

What is the fastest way to find (and select) a record with a specific Id within the filtered recordset?

Reply | Quote

Replies

You can use code like this:

Dim lngID As Long
Dim rst As DAO.Recordset

lngID = …
Set rst = Me.RecordsetClone
rst.FindFirst “ID=” & lngID
If rst.NoMatch Then
MsgBox “ID ” & lngID & ” not found.”, vbExclamation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

The code requires that you have set a reference to the Microsoft DAO 3.6 Object Library.
I have assumed that the field you’re looking for is named ID and that it is a number field. If it is a text field, use

rst.FindFirst “ID=” & Chr(34) & lngID & Chr(34)

Reply | Quote

Thanks Hans, looks simple enough.

Reply | Quote

It’s basically the code generated by the Combo Box Wizard if you select the option “Find a record on my form based on the value I selected in my combo box”.

Reply | Quote

Ah. Followup: if I use the scroll wheel to scroll down so the record goes out of view, how do I ensure it comes back when I click my button?

I tried:

Me.SelTop = Me.CurrentRecord

but that doesn’t seem to do the trick?

Reply | Quote

Setting SelTop isn’t very dependable. You could try something like

Dim n As Long
n = Me.CurrentRecord
Me.SelTop = Me.RecordsetClone.RecordCount
Me.SelTop = 1
Me.SelTop = n

Reply | Quote

Looks promising, thanks.

Reply | Quote

Hi Hans, what’s the purpose of setting Me.SelTop = Me.RecordsetClone.RecordCount and then Me.SelTop = n?
I’ve tried it on an unfiltered continuous form where the current record on opening is 24 rows from the bottom and Me.RecordsetClone.RecordCount and Me.CurrentRecord give me the same number.

Reply | Quote

In a form with only a few records, setting SelTop has little effect anyway. In a form with more records, setting SelTop to RecordCount and then to 1 forces Access to jump. The value of n is the current record number before the jumps.

Reply | Quote