Find using Combo - how to display my own msg if not found

[Guest]

Hi Friends,
I would be very grateful if you could give any solution for my following
problem.
I have created a form based on customer table. On this form I have created
combo box to find the customer no. If user type the customer number, it will
pop up the customer details on this form.
I type a cusno, If that cusno not in my table it will come up error.
How can I display my own customized meg if cusno not in table.
I wont to display a my own customized message if customer number not in
table how can I do this. Cusno is text field it is ID is autonumber field.

My combo box coding is:-
Private Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

Thank in advance
Indu UK

 

[Michel Walsh]

Hi,


Try


If Not rs.NoMatch then Me.Bookmark=rs.Bookmark



I would also go with

Dim rs As DAO.Recordset

rather than

Dim rst As Object


or plainly with a with:




Private Sub Combo12_AfterUpdate()

' Find the record that matches the control.
With Me.RecordsetClone ' no dot between t and C
' dot-Find
.FindFirst "[ID] = " & Str(Nz(Me![Combo12], 0))

' dot-NoMatch
If Not .NoMatch Then

' = dot Bookmark
Me.Bookmark = .Bookmark
End If

End WIth

End Sub



Since dot can be missed, I add few comments, be careful with the syntax!



Hoping it may help,
Vanderghast, Access MVP

 

[George Nicholson]

The AfterUpdate event is the correct event to use to navigate to the user's
entry.

However, if the "Limit To List" property of the comboBox is set to Yes then
you need to add code to the "NotInList" event to handle "new" entries (or
simply provide your own message). If LimitToList is Yes, the AfterUpdate
won't fire for new entires (since the entry isn't allowed, the field doesn't
update so AfterUpdate doesn't occur).

For more information on NotInList, check out the Help entry in Help from the
VB editor and/or:
http://support.microsoft.com/default.aspx?scid=kb;en-us;197526

HTH,