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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Indu - 12 Oct 2005 16:51 GMT

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

Reply to this Message

Michel Walsh - 12 Oct 2005 18:21 GMT

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

> Hi Friends,
> I would be very grateful if you could give any solution for my following
[quoted text clipped - 20 lines]
> Thank in advance
> Indu UK

Reply to this Message

George Nicholson - 12 Oct 2005 18:59 GMT

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,

Signature

George Nicholson

Remove 'Junk' from return address.

> Hi Friends,
> I would be very grateful if you could give any solution for my following
[quoted text clipped - 20 lines]
> Thank in advance
> Indu UK

Reply to this Message