Autopopulate Field according to Textbox in same Form from 2 Tables

[hammer187]

Here is my situation. I have a login form that inputs the user for ID and Name. I have 2 tables that has the user credentials. One table is coaches and one is for hourly. I want to autopopulate the Name text box in my form from the ID text box. User A is a coach and User B is an hourly. I want User A to input their ID in the ID text box and press a button to check the ID, then I want it to autopopulate the Name of User A from the CoachTable into the Name text box on my form. User B is an hourly, I want it to do the same thing as User A, but look into the HourlyTable. Here is my code and I know it doesn't work just my thoughts on how to do it.

Private Sub Command19_Click()
Dim rs As DAO.Recordset
Dim txtID As Variant
txtID = Forms![LoginForm2]![txtEmployeeID]
txtName = Forms![LoginForm2]![Text13]
Set rs = CurrentDb.OpenRecordset("SELECT * FROM HourlyTable WHERE EmployeeID = '" & txtID & "'", dbOpenDynaset)
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM CoachTable WHERE EmployeeID = '" & txtID & "'", dbOpenDynaset)

Forms![LoginForm2]![Text13].SetFocus
 txtName = DLookup("EmployeeID", "HourlyTable", "EmployeeID = '" & txtID & "' AND CoachName='" & txtName & "'")
 txtName = DLookup("EmployeeID", "CoachTable", "EmployeeID = '" & txtID & "' AND CoachName='" & txtName & "'")
End Sub

[June7]

What isn't working? Is there error message, wrong results, nothing happens?

Why do you open recordsets?

[hammer187]

Like I said in my initial post, I know this code doesn't work. I'm not sure if I even need recordsets or not. If I do, I didn't declare rs1. There isn't code to get the Name from the table and place it in the name field on my form, it just checks it. There isn't code to distingush which table to look at for which user. So to answer your question, yes there are errors and probably wrong results even if I do get past the errors. I just wrote this down to get my mind thinking on how to get it done. But I got stopped on how to determine which table to look into for which user.

[hammer187]

This is what I have got so far. It only does the first table though, I can't get it to do the next table.

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

If I input an hourly ID, it displays the name in the msgbox, if I input a coach ID, it does a Null error for txtName

[hammer187]

I have inserted this

Forms![LoginForm2]![Text13].SetFocus
    Forms![LoginForm2]![Text13] = txtName

below my if statement and below my else statement, that puts the Name into my Name box.

[hammer187]

I figured out that I can do it if I use 2 buttons one per table. But I dont want to use 2 buttons. I need to find a way to join the buttons.

[hammer187]

Here is one of my button codes. It doesn't do the else statement. Something is wrong with the 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

[June7]

Try populating txtID before opening recordset. EmployeeID is a text datatype field?

I really don't understand what you are trying to do. You want to verify if CoachName is in CoachTable and if not then look for it in HourlyTable? What happens if it is not in either?