RecordSet.FindFirst

[Stephanie]

Hi. I have a combo box to find an exiting member:

Private Sub CmbSearch_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContactID] = " & Me![cmbSearch]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me!cmbSearch = Null
End Sub

The user uses the combo box to locate a member and if the member doesn't
exist, receives an Access box: the test you entered doesn't exist.

Then the user clears out the combo box field and clicks on the new record
button and receives a run-time error 3077: syntax error (missing operator) in
expression.
Debug takes me to:
Me.RecordsetClone.FindFirst "[ContactID] = " & Me![cmbSearch]

How can I gracefully (without an error msg) let the user enter a new record
after the combo box was unable to find an existing record?

Thanks for your input!

 

[Carl Rapson]

You need to test for a Null value in your combo box:

If Not IsNull(Me![cmbSearch]) Then
Me.RecordsetClone.FindFirst "[ContactID] = " & Me![cmbSearch]
' ...
End If

The problem is, the AfterUpdate event is firing after you clear the combo
box, but there is no value to use in the FindFirst call.

Carl Rapson

 

[Stephanie]

You are fabulous! Thanks for the help.

You need to test for a Null value in your combo box:

If Not IsNull(Me![cmbSearch]) Then
Me.RecordsetClone.FindFirst "[ContactID] = " & Me![cmbSearch]
' ...
End If

The problem is, the AfterUpdate event is firing after you clear the combo
box, but there is no value to use in the FindFirst call.

Carl Rapson

Hi. I have a combo box to find an exiting member:

Private Sub CmbSearch_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContactID] = " & Me![cmbSearch]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me!cmbSearch = Null
End Sub

The user uses the combo box to locate a member and if the member doesn't
exist, receives an Access box: the test you entered doesn't exist.

Then the user clears out the combo box field and clicks on the new record
button and receives a run-time error 3077: syntax error (missing operator)
in
expression.
Debug takes me to:
Me.RecordsetClone.FindFirst "[ContactID] = " & Me![cmbSearch]

How can I gracefully (without an error msg) let the user enter a new
record
after the combo box was unable to find an existing record?

Thanks for your input!