Find Record from a Combo Box

[Paul H]

This is very simple, but I'm stubbing my toe. I put this code in my afterupdate event of my Combo Box.

DoCmd.FindRecord Me.cboInvoice, acEntire, False, acSearchAll

I am getting a Runtime Error # 2162
A macro set to one of the current fields properties is failed because of an error in Find Record action argument.

What am I missing?

[ItsMe]

I usually use DAO and bookmark

Looking at the code, it seems like you are searching for a record within the combo and not the Form's RecordSet.

[burrina]

Normally, this is the way it;s done.

Find the record that matches the control.
Dim rs As Object



Set rs = Me.Recordset.Clone
rs.FindFirst "[acEntire] = " & Str(Nz(Me![cboInvoice], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

HTH

[Paul H]

Hmmm... "Microsoft Access Database Engine does not recognize acEntire as a valid field name or expression."

Just to be clear I want my form to jump to the record with the Invoice number.

[ItsMe]

acEntire is a constant. maybe see what it is in the immediate window.

Not sure what this is supposed to do
rs.FindFirst "[acEntire] = " & Str

If you are iterating the combo, why not go after the original SQL that the RowSource is based on?

[Paul H]

This is all a little unintuitive for me. Clones and Bookmarks don't penetrate my skull too well. I'll revisit this on Monday and get back to you.

[ItsMe]

Still not sure what you are trying to do.

If you want your form to navigate to a specific record, I would not use the FindRecord method. Maybe .GoToRecord would be more appropriate but, error trapping that method can be tricky. I would employ something like this.

Dim rs As DAO.Recordset
If Not IsNull(Me.ComboboxName) Then
    If Me.Dirty Then
        Me.Dirty = False
    End If
    Set rs = Me.RecordsetClone
    'rs.FindFirst "[FieldName] = '" & Me.ComboboxName.Column(0) & "'"    'for text field
    rs.FindFirst "[FieldName] = " & Me.ComboboxName.Column(0)             'for number field
    If rs.NoMatch Then
        MsgBox ("Not found")
    Else
          If rs.Bookmarkable = False Then
          MsgBox "Can't bookmark this record"
          Else
          Me.Bookmark = rs.Bookmark
          End If
    End If
    Set rs = Nothing
End If

If you are trying to use VBA to change the value in a combo, you will want to use a different approach. Let us know.

[Paul H]

Just want to navigate to a record on my form. I'll try this out on Monday.

[June7]

I got the FindRecord to work only by running the command in the immediate window. As code behind form, could not get to work. GoToRecord doesn't have text search.

Use bookmarks and RecordsetClone. It is not necessary to open a recordset object. I've never used the Bookmarkable property, didn't know about it.

If Not IsNull(Me.ComboboxName) Then
    If Me.Dirty Then
        Me.Dirty = False
    End If
    With Me.RecordsetClone
    '.FindFirst "[FieldName] = '" & Me.ComboboxName.Column(0) & "'"    'for text field
    .FindFirst "[FieldName] = " & Me.ComboboxName.Column(0)             'for number field
    If .NoMatch Then
        MsgBox ("Not found")
    Else
        If .Bookmarkable = False Then
          MsgBox "Can't bookmark this record"
        Else
          Me.Bookmark = .Bookmark
        End If
    End If
    End With
End If

[ItsMe]

... I've never used the Bookmarkable property, didn't know about it...

I use it to make sure there is a record that matches between the two recordsets. When using recordset clone, nesting bookmarkable in the following example may be redundant.

    If .NoMatch Then
        MsgBox ("Not found")
    Else
        If .Bookmarkable = False Then
          MsgBox "Can't bookmark this record"
        Else

[Paul H]

June,

Your code worked.

I'm not crazy about canabalizing it whole. Like I said, I've never used bookmarks. I'll take time to understand it later.