Search and compare data in a table

ednis · #1 (**permalink**) 08-10-12, 08:36

Could someone help me with the project Im doing. Basically I got a form, and that form has textbox. When I enter my ID or someone enters their ID then click on the command button. It will check each data in a certain field in a table.

here is what I currently have:

Private Sub LookUp_Click()

Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.Recordsets("Login")

rs.FindFirst ("[vzid]")

Do While Not (rs.EOF)

If rs![vzid] = Me.InputT.Value Then

TimeIn.Visible = True
TimeOut.Visible = True

Else

rs.MoveNext

End If

Loop

End Sub

Sinndho · #2 (**permalink**) 08-10-12, 10:31

This is not the proper way of using the FindFirst method. Try:

Code:

Private Sub LookUp_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strCriteria As String
    
    strCriteria = "vzid = " & Me.InputT.Value
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Login", dbOpenSnapshot)
    rs.FindFirst strCriteria
    If rs.NoMatch = False Then
        TimeIn.Visible = True
        TimeOut.Visible = True
    End If
    rs.Close
    Set rs = Nothing

End Sub

Ideally, you should test for a Null in InputT before performing the search.

ednis · #3 (**permalink**) 08-10-12, 13:10

Thanks. I tried the code but im getting a yellow line at

rs.FindFirst strCriteria

vzid for example is x1234

I can't see why this is giving me an error.

Sinndho · #4 (**permalink**) 08-10-12, 15:49

Quote:

Originally Posted by ednis

Thanks. I tried the code but im getting a yellow line at

rs.FindFirst strCriteria

vzid for example is x1234

I can't see why this is giving me an error.

Because if vzid is defined as Text, you must use:

Code:

strCriteria = "vzid = '" & Me.InputT.Value & "'"

Internet Services

Web Development

Digital Marketing

Consumer Tech