asked on

finding item

When I enter in an item to search the PCID field with I am running into problems. If I enter in the first item in the record I get "good find" but if I enter in anything else I get "bad find". I get this even when I enter in good search item. Below is my code.

Option Explicit
'declare database variables
Public db As Database
Public rs As Recordset
Private Sub Cancel_Click()

'quits login and closes program
Unload LogInForm

End Sub
Private Sub Form_Load()

'open database
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\tenneco.mdb")
Set rs = db.OpenRecordset("select PCID from parts", dbOpenDynaset)
rs.MoveLast

'centers login in center of screen
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2

'starts user in login box
LogInForm.Show
OperatorID.SetFocus

End Sub
Private Sub LogIn_Click()

If rs.RecordCount > 0 Then rs.MoveLast
rs.MoveFirst

If rs.Fields("PCID").Value = LogInForm.OperatorID.Text Then
MsgBox "good find"
Else
MsgBox "bad find"
End If

End Sub

TIA
Craig

deighton

Private Sub LogIn_Click()

rs.FindFirst "PCID = " & LogInForm.OperatorID.Text

If not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If

End Sub

csindorf

ASKER

Adjusted points from 50 to 60

csindorf

ASKER

I get "Data type mismatch in criteria expression"

Here is the code as you suggested:
Option Explicit
'declare database variables
Public db As Database
Public rs As Recordset
Private Sub Cancel_Click()

'quits login and closes program
Unload LogInForm

End Sub
Private Sub Form_Load()

'open database
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\tenneco.mdb")
Set rs = db.OpenRecordset("select PCID from parts")
rs.MoveLast

'centers login in center of screen
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2

'starts user in login box
LogInForm.Show
OperatorID.SetFocus

End Sub
Private Sub LogIn_Click()

rs.FindFirst "PCID = " & LogInForm.OperatorID.Text

If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If

End Sub

PaulHews

Deighton's answer is correct, but if the PCID field is a string, you will need:
rs.FindFirst "PCID = '" & LogInForm.OperatorID.Text & "'"

String values in query criteria have to have single or double quotes around them.

csindorf

ASKER

But if I enter in PCID which is the column name it come back ok, but if I enter in a value in that column no good.

TIA

Craig

deighton

Private Sub LogIn_Click()

rs.FindFirst "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.OperatorID.Text)) & "'"

If Not rs.NoMatch Then
MsgBox "good find"
Else
MsgBox "bad find"
End If

End Sub

csindorf

ASKER

That was it deighton! Great. could you please explain the line: rs.FindFirst "ucase(trim(PCID)) = '" & ucase(trim(LogInForm.OperatorID.Text)) & "'"

that way I can understand better what the code is doing.

Craig

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial