Combing 2 Buttons Into 1 Button

[hammer187]

How can I combine these 2 buttons into 1 button?

Private Sub Command12_Click()

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

If IsNull(DLookup("EmployeeID", "CoachTable", "EmployeeID = '" & txtId & "' AND CoachName='" & txtName & "'")) Then
MsgBox "This is not a valid login"
Else
    DoCmd.OpenForm "frmMain", acNormal
End If
Exit Sub
End Sub

Private Sub Command16_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)
If IsNull(DLookup("EmployeeID", "HourlyTable", "EmployeeID = '" & txtId & "' AND CoachName='" & txtName & "'")) Then
MsgBox "This is not a valid login"
Else
    DoCmd.OpenForm "HourlyForm", acNormal
End If
End Sub

[Bob Fitz]

Does clicking either of the buttons give the expected result?
Are these buttons on a form called LoginForm2?
Where is txtName declared?
What is the purpose of the rs recordset?

[hammer187]

the buttons do work and they do bring the expected results. the rs recordset apparently is not needed. I will declare txtName if that makes it work. These buttons are in LoginForm2

[hammer187]

This is what I have so far but it prompts the MsgBox twice. For example, if a coach signs in, it says Invalid ID and then goes to the appropriate Form. If an Hourly signs in, it says Invalid ID and goes to the appropriate form.

Private Sub Command25_Click()
Dim txtName As Variant
Dim txtId As Variant
txtId = Forms![LoginForm2]![txtEmployeeID]
txtName = Forms![LoginForm2]![Text13]
If IsNull(DLookup("EmployeeID", "HourlyTable", "EmployeeID = '" & txtId & "' AND CoachName='" & txtName & "'")) Then
MsgBox "This is not a valid login"
Else
    DoCmd.OpenForm "HourlyForm", acNormal
End If
If IsNull(DLookup("EmployeeID", "CoachTable", "EmployeeID = '" & txtId & "' AND CoachName='" & txtName & "'")) Then
MsgBox "This is not a valid login"
Else
    DoCmd.OpenForm "frmMain", acNormal
End If
End Sub

[Bob Fitz]

Try this:

Private Sub Command25_Click()

If Not IsNull(DLookup("EmployeeID", "HourlyTable", "EmployeeID = " & Me.txtEmployeeID & " AND CoachName='" & Me.Text13 & "'")) Then
  DoCmd.OpenForm "HourlyForm", acNormal
ElseIf Not IsNull(DLookup("EmployeeID", "CoachTable", "EmployeeID = '" & Me.txtEmployeeID & "' AND CoachName='" & Me.Text13 & "'")) Then
  DoCmd.OpenForm "frmMain", acNormal    
Else
  MsgBox "This is not a valid login"
End If

End Sub

[Bob Fitz]

BTY
At the top of each code module you have the line:
Option Compare Database
IMHO it would be wise to place the following line:
Option Explicit
directly below it

[hammer187]

Ok, I got it to work. I had to tweak your code. the Me. stuff, will not work. Here is what ended up working

Private Sub Command25_Click()
Dim txtName As Variant
Dim txtId As Variant
txtId = Forms![LoginForm2]![txtEmployeeID]
txtName = Forms![LoginForm2]![Text13]
If Not IsNull(DLookup("EmployeeID", "HourlyTable", "EmployeeID = '" & txtId & "' AND CoachName='" & txtName & "'")) Then
DoCmd.OpenForm "HourlyForm", acNormal

ElseIf Not IsNull(DLookup("EmployeeID", "CoachTable", "EmployeeID = '" & txtId & "' AND CoachName='" & txtName & "'")) Then
DoCmd.OpenForm "frmMain", acNormal
Else
    
MsgBox "Invalid Login"
End If
End Sub

[Bob Fitz]

Good! I'm glad you got it to work

Did you add the Option Explicit as I suggested?
See: http://www.fmsinc.com/free/newtips/v...ion/index.html
for an explanation of why it should be used.

FYI Although the EmployeeID field is storing a number it must actually be a text field in the table, or your code would fail.

[hammer187]

I have everything as a text field because it is easier for me to write code for all text because it is basically a string. Now if I can just get my other button to do the whole if then else statement that you commented on earlier today.