If Statement Doesn't Do Else Part

[hammer187]

My if statement doesn't do the else part. There is something wrong with my rs.eof part of my if statement.

Private Sub Command19_Click()

Set rs = CurrentDb.OpenRecordset("SELECT * FROM CoachTable WHERE EmployeeID = '" & txtID & "'", dbOpenDynaset)
txtID = Forms![LoginForm2]![txtEmployeeID]
txtName = Forms![LoginForm2]![Text13]
 If rs.EOF Then
 txtName = DLookup("CoachName", "HourlyTable", "EmployeeID='" & txtID & "'")
 Forms![LoginForm2]![Text13].SetFocus
    Forms![LoginForm2]![Text13] = txtName
Else
txtName = DLookup("CoachName", "CoachTable", "EmployeeID='" & txtID & "'")
Forms![LoginForm2]![Text13].SetFocus
    Forms![LoginForm2]![Text13] = txtName
End If
End Sub

[Bob Fitz]

Is the field EmployeeID a numeric field? If so

"EmployeeID = '" & txtID & "'"

should be

"EmployeeID = " & txtID

[hammer187]

Should this be in all of the spots that has "EmployeeID = '" & txtID & "'" ?

[hammer187]

Well, I replaced that with what you said and it gave me an error.

[hammer187]

The reason that I know something is wrong with me rs.eof part of my if statement is that I replaced that with if x=2 and i declared x as a dim integer and it did the else part with no problem.

[hammer187]

I also put x = 1 above my if statement.

[Bob Fitz]

Well I could be wrong, but I think the problem is with the recordset rs
Have you also tried:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM CoachTable WHERE EmployeeID = " & txtID , dbOpenDynaset)

[hammer187]

That didn't work. It gave me an error with the Set rs ... line

[Bob Fitz]

I can't see a declaration for rs

Try this:

Private Sub Command19_Click()
Dim rs as DAO.RecordSet
Set rs = CurrentDb.OpenRecordset("SELECT * FROM CoachTable WHERE EmployeeID = " & txtID, dbOpenDynaset)
txtID = Forms![LoginForm2]![txtEmployeeID]
txtName = Forms![LoginForm2]![Text13]
If rs.EOF Then
    txtName = DLookup("CoachName", "HourlyTable", "EmployeeID= " & txtID)
    Forms![LoginForm2]![Text13].SetFocus
    Forms![LoginForm2]![Text13] = txtName
Else
    txtName = DLookup("CoachName", "CoachTable", "EmployeeID= " & txtID)
    Forms![LoginForm2]![Text13].SetFocus
    Forms![LoginForm2]![Text13] = txtName
End If
End Sub

[hammer187]

That must have gotten lost in all of my cutting and pasting. It used to be there. I added it and it didnt fix the problem

[hammer187]

I also pasted your code into it and it gave me an error.

[Bob Fitz]

Is EmployeeID numeric?
What was the error and on which line?

[Bob Fitz]

I've just realised that txtID and txtName are not declared

[hammer187]

I declared txtId and txtName and it still didnt work. When I pasted your code into it, it gave me run-time error 3075 syntax error missing operator in query expression 'EmployeeID ='

[hammer187]

in the Set rs ... line