Access 2003 forms: dropdown navigation

Question

I have a form with a dropdown for quicker navigation of records in a form. It is based on the table the form is bound to. When the value is changed (i.e. click on dropdown, select an entry), it should then jump to the correct record.

Access 2007 has the action 'SearchForRecord' which does what I want. However, Access 2003 does not have this. How can I perform this task in Access 2007?

Answer 1

You are probably looking for the FindFirst method.

Here is an example of how it might be used:

Function FindRecord (rstTemp As Recordset, _
    strFind As String) As Boolean

    rstTemp.FindFirst strFind

    FindAny = IIf(rstTemp.NoMatch, False, True)
End Function

Answer 2

Hi Sam,

You can try the following code in the OnChange event of your combobox or associate it with a button after a selection has been made:

Private Sub cboExample_Change()
  'Requires reference to Microsoft DAO 3.x Object Library '
  Dim rs As DAO.Recordset

  Set rs = Forms!frmExample.RecordsetClone

  rs.FindFirst "[ExampleID] = " & Me.cboExample

  If Not rs.NoMatch Then 
      Forms!frmExample.Bookmark = rs.Bookmark
  End If

End Sub

Hope this helps,

Robert

Answer 3

Was a lot simpler than expected, used DoCmd.ApplyFilter.

Private Sub PK_Combo_Change()
    DoCmd.ApplyFilter , "[PrimaryKey] = " & PK_Combo.Value
End Sub

Answer 4

I would edit @Robert's code to be this:

  With Forms!frmExample.RecordsetClone
    FindFirst "[ExampleID] = " & Me!cboExample
    If Not .NoMatch Then 
       If Me.Dirty Then Me.Dirty = False
       Forms!frmExample.Bookmark = .Bookmark
    End If
  End With

I see absolutely no reason to initialize a recordset variable when that recordset already exists and can be easily worked with inside a WITH block.

Also, it's essential to save any edits before departing a record with bookmark navigation, because that forces all validation to happen explicitly, rather than implicitly in the record departure. Historically speaking, Jet bookmark navigation was prone to eating errors that occured in the implicitly invoked save. Presumably, along with the other bookmark bugs, that has been stamped out, but I just think it's better to save a dirty record explicitly rather than just assume everything is going to go swimmingly.

Rant:

The code for this operation that created by the wizards in recent versions of Access (before 2007, i.e., 2002 and 2003) is dreadful. It uses a declared recordset variable (as @Robert's code did), but doesn't clean it up after using it (i.e., setting it to Nothing). It also uses the form's Recordset.Clone, instead of the already existing .RecordsetClone (they are distinct objects). Last of all, instead of using .NoMatch, it uses "If Not rs.EOF", which just makes little sense. I've been told that the reason for this was so that it would work with forms that have ADO recordsets, but ADO recordsets don't have a .FindFirst method, so this is ludicrous. True, you could change just the .FindFirst to .Find and it would work, but it's just awful, awful code.