Compare Data in a Recordset from User Input

[hammer187]

I am trying to compare data from a user input to a recordset. If the data is found in the recordset, open a form, if it isnt found, open a different form. If the user inputs no data and presses enter, display error saying invalid ID.

Private Sub Command12_Click()

Dim rs As DAO.Recordset
Dim txtID As Variant
txtID = Forms![LoginForm2]![txtEmployeeID]
 
Set rs = CurrentProject.Connection.Execute("SELECT * FROM DataTable WHERE EmployeeID IS NULL")
If Len(txtID) > 0 Then
While Not rs.EOF
If rs = txtID Then
DoCmd.OpenForm "frmMain", acNormal
Else
DoCmd.OpenForm "HourlyForm", acNormal
End If
Else
MsgBox "Please enter a valid Associate ID"
rs.MoveNext
Wend
Set rs = Nothing
End If
Exit Sub
End Sub

[pbaldy]

Well, this

If rs = txtID Then

would have to be:

If rs!FieldName = txtID Then

Where FieldName is the field you want to compare the input to. That said, looping the table is very inefficient. I'd add a criteria to the SQL and then you simply test for EOF (did the recordset return anything).

[hammer187]

Could you show me what you mean by adding criteria to the SQL? Im not fluent in VB. Here is my code right now.

Private Sub Command12_Click()

Dim rs As DAO.Recordset
Dim txtID As Variant
txtID = Forms![LoginForm2]![txtEmployeeID]
 
Set rs = CurrentProject.Connection.Execute("SELECT * FROM DataTable WHERE EmployeeID IS Text1.Text")
If Len(txtID) > 0 Then
While Not rs.EOF
If rs!EmployeeID = txtID Then
DoCmd.OpenForm "frmMain", acNormal
Else
DoCmd.OpenForm "HourlyForm", acNormal
End If
Else
MsgBox "Please enter a valid Associate ID"
rs.MoveNext
Wend
Set rs = Nothing
End If
Exit Sub
End Sub

[pbaldy]

For starters, you're mixing ADO and DAO there. You appear to be looking for a specific employee, so along the lines of

Set rs = CurrentDb.OpenRecordset("SELECT * FROM DataTable WHERE EmployeeID = " & txtID , dbOpenDynaset)

Which assumes the EmployeeID field is numeric (data type). Then you just test to see if the recordset returned anything:

If rs.EOF Then 'no such person  
  DoCmd.OpenForm "HourlyForm", acNormal
Else
  DoCmd.OpenForm "frmMain", acNormal
End If

[hammer187]

I am getting an debug error on the set rs = line of the code

Private Sub Command12_Click()

Dim rs As DAO.Recordset
Dim txtID As Variant
txtID = Forms![LoginForm2]![txtEmployeeID]
Set rs = CurrentDb.OpenRecordset("SELECT * FROM DataTable WHERE EmployeeID = " & txtID, dbOpenDynaset)
If Len(txtID) > 0 Then
If rs.EOF Then 'no such person
  DoCmd.OpenForm "HourlyForm", acNormal
Else
  DoCmd.OpenForm "frmMain", acNormal
End If
Else
MsgBox "Please enter a valid Associate ID"
End If
Exit Sub
End Sub

[hammer187]

I am getting this error:

Syntax error (missing operator) in query expression 'EmployeeID = '

[hammer187]

If i dont enter a number, it displays the error above. If I do enter a number, it displays the error data type mismatch in criteria expression

[pbaldy]

The first error implies the variable has no value. Make sure something has been entered in the textbox. The second that the field is text, not numeric; if so:


Set rs = CurrentDb.OpenRecordset("SELECT * FROM DataTable WHERE EmployeeID = '" & txtID & "'", dbOpenDynaset)

[hammer187]

Wow, stupid syntax. That works. Thanks. I will mark this as solved. I am also having trouble with something else concerning this database, but I'm going to work on it for a couple of days and then if I can't get it, I will post on here.

[pbaldy]

Happy to help.