Auto Number Lookup

[James C.]

Hoping someone can help....

I have a main table that has an auto number field. On my form there is a
dropdown box at the top that queries this field in ascending order. When my
user selects the record id (auto number) then it populates all of the bound
fields on the form. I keep receiving an error. I never have this problem when
linking any other field type.

The afterupdate code that runs is, where AcctLU is the dropdown and Acct# is
the bound Auto number field in my table and on my form:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Acct#] = '" & Me![AcctLU] & "'"
Me.Bookmark = rs.Bookmark

Any help would be appreciated.

 

[Allen Browne]

If Acct# is a Number field (not a Text field), drop the extra quotes.

The code could also fail if:
a) AcctLU is null, or
b) The current edits cannot be saved (e.g. required field missing), or
c) if the FindFirst fails (e.g. form is filtered, or opened in Data Entry
mode.)

Try something like this:

Dim rs As DAO.Recordset
If Not IsNull(Me.AcctLU) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.Recordset.Clone
rs.FindFirst "[Acct#] = " & Me.AcctLU
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing