Opening a from based on more than 1 criteria

[Howlsta]

I've built a form where for instance a member of staff enters their name and ID to logon. The problem is i built the form using help from wizard to open the next form. I wanted to link criteria for ID and Name. I linked the open operation by ID and tried to add the code to check if the name corresponds to the ID. The code below only seems to check if the ID is valid, and even so it seems to allow the user to enter values which don't exist in the table!

Can anyone help improve this code to make the system check that ID and name are valid, please??
Private Sub cmdGo_Click()
On Error GoTo Err_cmdGo_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkName As String

stDocName = "frmstaffswitchboard"

stLinkCriteria = "[StaffID]=" & Me![txtStaffID]
stLinkName = "[FirstName]=" & "'" & Me![txtName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, , , stLinkName
Exit_cmdGo_Click:
Exit Sub

Err_cmdGo_Click:
If IsNull(Me.txtStaffID) Or IsNull(Me.txtName) Then
MsgBox "Name or ID is Blank"
Else
MsgBox Err.Description
End If
Resume Exit_cmdGo_Click

 

[Rich]

Shouldn't the If IsNull be at the beginning
Private Sub cmdGo_Click()
On Error GoTo Err_cmdGo_Click

If IsNull(Me.txtStaffID) Or IsNull(Me.txtName) Then
MsgBox "Name or ID is Blank",vbOK Only
Exit Sub
Else
Dim stDocName As String
HTH

 

[Howlsta]

Well that helped, now the name box has to be filled. But the new code, below allows me to enter any old garbage and will still open the form. I need it to only allow corresponding values from columns firstname and StaffID in a table called tblStaff. Any more help anyone?? Thanx

Private Sub cmdGo_Click()
On Error GoTo Err_cmdGo_Click

If IsNull(Me.txtStaffID) Or IsNull(Me.txtName) Then
MsgBox "Name or ID is Blank"
Exit Sub
Else

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkName As String

stDocName = "frmstaffswitchboard"

stLinkCriteria = "[StaffID]=" & Me![txtStaffID]
stLinkName = "[FirstName]=" & "'" & Me![txtName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, , , stLinkName
Exit_cmdGo_Click:
Exit Sub

Err_cmdGo_Click:

MsgBox Err.Description
Resume Exit_cmdGo_Click

End If
End Sub

 

[Rich]

Okay you have two choices either open the recordset and check if the StaffID exists or Use DLookUp to see if it exists if it doesn't then then use the MsgBox, ExitSub again,Else DoCmd ETC
HTH

 

[Howlsta]

How do i open the recordset will this code check if staffID exists? or is it totally wrong? I've got really used recordsets yet i'm trying to read about them now!
I've not heard of dlookup either!

Sub findStaffID(lngValue As Long)
Dim rst As ADODB.Recordset
Dim strSQL As String

Set rst = New ADODB.Recordset
rst.ActiveConnection = currentStaffID.Connection
rst.CursorType = adOpenStatic
rst.Open "Select * from tblStaff"
strSQL = "[StaffID]" = " & lngValue"

rst.Find strSQL

If rst.EOF Then
MsgBox lngValue & " not found"

Else
MsgBox lngValue & "found"
End If

rst.Close
Set rst = Nothing
End Sub