Combo Box Search

[Guest]

I have an unbound combo box which allows a user to select an invoice number
from the drop down list so that the record for that invoice will be displayed
in the form. The invoice number stored in the Invoice table is an AUTONUMBER
field. The AfterUpdate property of the combox box look like this:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[lngInvoiceNo] = " & Str(Nz(Me![cboInvSelect], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

However, I discovered that when a user enters a number that is greater than
the invoice number available, the record for the first record will be
displayed. In other words, let's say the last invoice number is 100. If a
user enters 120 or 500 and hit enter, the information for the first record
will be displayed.

How do I check the number entered against the last available invoice number
so that I can prompt the user if the number exceeds the number in the table?
Thanks.

ck

 

[Steve Schapel]

CK,

Try it something like this...
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[lngInvoiceNo] = " & Nz(Me.cboInvSelect, 0)
If rs.NoMatch Then
MsgBox "No such Invoice"
Else
Me.Bookmark = rs.Bookmark
End If

 

[Guest]

Thanks Steve. That worked.
ck

CK,

Try it something like this...
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[lngInvoiceNo] = " & Nz(Me.cboInvSelect, 0)
If rs.NoMatch Then
MsgBox "No such Invoice"
Else
Me.Bookmark = rs.Bookmark
End If

--
Steve Schapel, Microsoft Access MVP

I have an unbound combo box which allows a user to select an invoice number
from the drop down list so that the record for that invoice will be displayed
in the form. The invoice number stored in the Invoice table is an AUTONUMBER
field. The AfterUpdate property of the combox box look like this:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[lngInvoiceNo] = " & Str(Nz(Me![cboInvSelect], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

However, I discovered that when a user enters a number that is greater than
the invoice number available, the record for the first record will be
displayed. In other words, let's say the last invoice number is 100. If a
user enters 120 or 500 and hit enter, the information for the first record
will be displayed.

How do I check the number entered against the last available invoice number
so that I can prompt the user if the number exceeds the number in the table?
Thanks.

ck