Combo Box Message

  • Thread starter Thread starter Antonio
  • Start date Start date
A

Antonio

I have been using combo boxes to locate data on the
current form that is displayed. On one form I use it to
locate a record belonging to a specific person...which is
by the persons last name. To create them I use the wizard
(know little about VBA)and it works fine. Here is an
example of the code the wizard sets up for me.

Private Sub Combo36_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

End Sub

I would like a message box to pop up stating "that name
does not exist" or "that person has not been entered
yet". Right now, if you enter a name that is not in the
table, the form seems to just sit there and do nothing.
Is there a way to add a line of code to the end of the
code above to produce a message box?
TIA
 
1. Direct answer:

Change the code to:

If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Sorry - not found."
End If

2. A more proper answer:

The idea is that the ComboBox Box should only contain the names of the
people whose names are in the Form RecordSource so the user can only select
the name that has Record in the Form's RecordSource and the case "Not found"
should never happen. This is why the Wizard doesn't create the "Not found"
message.

More often, each row in the ComboBox Rowsource should correspond to a Record
/ row in the Form RecordSource. For this the Form RecordSource and the
ComboBox RowSource are nearly identical, i.e. based on the same Tables and
the same criteria, possibly different in the Fields selected.

It sound like your Form RecordSource and ComboBox RowSource are not "nearly
identical" (as above) and therefore you need the "Not found" message.
 
Back
Top