Message box problem [Resolved]

[hbin]

Private Sub Command1_Click()
    Dim Search As String
    Dim sql As String
    
     Search = InputBox("Enter Client ID", "Search Record")
    
    data1.Recordset.FindFirst "[Client ID]= '" & Search & "'"
    sql = "SELECT * FROM Table1 WHERE [Client ID] = '" & Search & "'"
        data1.RecordSource = sql
        data1.Refresh
        
        If data1.Recordset.NoMatch Then
          
            MsgBox "Record Not Found!", vbExclamation, "Search Record"
        Else
            MsgBox "Record Found!", vbInformation, "Search Record"
        End If             
End Sub

I wrote the above code to retrieve more than one records of a same client, and these records will be showed in a data grid. When record found/match, there will be a mesage box which displays "Record Found!", but the problem is when there is no record match, the message box still displays "Record Found!". I can't see anything wrong with the code/statement, any suggestion to make the message box display "Record Not Found!" when there is no record match?

Thanks in advance!

[Merri]

Insert this after the Refresh line:

Debug.Print data1.Recordset.NoMatch

My guess is that NoMatch's value is always zero/false. If it would be something else, you'd constantly get Not Found messagebox.

[dglienna]

you prolly need to compare it to -1, 0, or 1. Use an = and an else.
it prolly returns -1 if not found or something like this that causes the false.

[hbin]

Debug.Print data1.Recordset.NoMatch

I inseted it after the Refresh line but it still can't works. BTW, what is the meaning of Debug.Print?

[Merri]

There is a window called Immediate when your program is in runtime. When you use Debug.Print, the variable you give to it appears in the Immediate window. You can use this to find out the contents of your variables on runtime so you can debug your program and find out what might be wrong.

[hbin]

I only see the word "False" in the Immediate window, what does that means?

[dglienna]

that it did not find a match. it would have been true if it did.

[dglienna]

try changing

If data1.Recordset.NoMatch Then

to

If data1.Recordset.NoMatch = True Then

is the Search the same data type as ID is?

[hbin]

Originally posted by dglienna
try changing

If data1.Recordset.NoMatch Then

to

If data1.Recordset.NoMatch = True Then

is the Search the same data type as ID is?

I tried your code, but it is still the same.

Yeah, the Search is the same data type as ID, which is String.

[MartinLiss]

Can you put together a small project (including data) that shows the same problem and attach it?

[dglienna]

use debug.print to check each value. or else step through.

[ponjaps]

Try the ffg. codes:

Dim Search As String
Dim sql As String

Search = InputBox("Enter Client ID", "Search Record")

sql = "SELECT * FROM Table1 WHERE [Client ID] = '" & Search & "'"
data1.RecordSource = sql
data1.Refresh

if data1.recordset.bof and data1.recordset.eof then
MsgBox "Record Not Found!", vbExclamation, "Search Record"
else
MsgBox "Record Found!", vbInformation, "Search Record"
End If

[hbin]

Thanks ponjaps, your code work!