How do I make MsgBox work?

[Noreene Patrick]

I have a form that pulls from a table that holds Invoice #'s. I used the wizard to make a combobox to type in the info I want and it pulls up the correct invoice number. That works fine. But, when I enter an invoice number that is not in the table, it does nothing...I want it to pop up a MsgBox that says "Please enter a valid invoice number."
Below is the code that the wizard wrote for me..but exactly how do I add the correct code for the msgbox to work?

The last line of code is what I put in but it always pops up even if the invoice number is correct.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[tblInvoice#] = " & Str(Nz(Me![Combo72], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
MsgBox "Please enter a valid invoice number."

End Sub

Thanks, Noreene

 

[dcx693]

Try replacing the last two lines with:

If Not rs.EOF Then
     Me.Bookmark = rs.Bookmark
Else
     MsgBox "Please enter a valid invoice number."
     Me.Combo72=Null
     Me.Combo72.SetFocus
     Me.Combo72.DropDown
End If

 

[Noreene Patrick]

I still get the same reaction when I inserted this code. It will go to a valid invoice, but do nothing if the invoice is invalid except go to the very first record.

I replaced the last two lines of my code with this code...any other suggestion?

Thanks very much, Noreene

 

[Mile-O]

Dim rs As Object

Just want to say that you can afford to be more explicit with this statement:

i.e.

Dim rs As Recordset


and even moreso:

Dim rs As DAO.Recordset

or

Dim rs As ADODB.Recordset

 

[dcx693]

Sorry, I think I'm having a Friday morning brain malfunction. Is your combo box allowing people to type in numbers that don't already exist? If so, you might just want to consider settings the "Limit to list" property to Yes, and then placing that single line of MsgBox code into the On Not In List event.

 

[Noreene Patrick]

Thanks Mile-O-Phile for taking the time to answer me...It would be wonderful for me if I knew enough about code to understand that...However, I am very, very dimwitted about code and I just don't understand what to do.

I tried to replace Object with Recordset, but it gave me a compile error and highlighted FindFirst.

Could you make it more simple for me? I would love to have your knowledge..but unfortunately for me, I dont.

Thanks, Noreene

 

[Noreene Patrick]

dcx693

That worked just great and it was simple enough for me to do.

Thanks to both of you for your help!!!!

Noreene