I keep getting "Too few parameters. Expected 1." message each time a record is clicked and an error pops up and goes to VBA code line CurrentDb.OpenRecordset strSQL for debugging.
Code:
Private Sub btnFindRecord_Click()
'On Error Resume Next
Dim Permission As String
Dim Staff As String
Dim CaseGroup As String
Dim ManagerGroup As String
Dim DepartmentGroup As String
Dim tClient As String
Dim aCase As String
Dim strSQL As String
Permission = [Forms]![frmLogin]![txtPermissions]
Staff = [Forms]![frmLogin]![txtStaffNumber]
CaseGroup = [Forms]![frmLogin]![txtCaseGroup]
ManagerGroup = [Forms]![frmLogin]![txtManagerGroup]
DepartmentGroup = [Forms]![frmLogin]![txtDepartmentGroup]
tClient = [Forms]![frmViewMain]![txtClientNum]
aCase = "SELECT tblCase.[CaseNum] FROM tblCase WHERE tblCase.[ClientNum] Like '" & tClient & "' "
strSQL = "SELECT ID, CaseNum, ClientNum, DateCreated, CaseStatus, AssignedCaseGroup, AssignedStaff " _
& "FROM tblCase " _
& "WHERE CaseNum LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR ClientNum LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR CreatedBy LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR DateCreated LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR StartDate LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR EndDate LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR Urgency LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR CaseStatus LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR Service LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR CaseSubmitted LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR DateSubmitted LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR SubmissionMethod LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR Outcome LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR ClientFileRefNum LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR HourlyRate LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR DailyRate LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR FlatRate LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR Budget LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR HoursEstimate LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR HoursActual LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR AssignedCaseGroup LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR AssignedStaff LIKE '*" & Me!txtRecordSearchBox & "*' " _
& "OR Notes LIKE '*" & Me!txtRecordSearchBox & "*' "
If Permission = "Admin" Then
CurrentDb.OpenRecordset strSQL
ElseIf Permission = "Manager" Then
If Not IsNull(tClient) Then
CurrentDb.OpenRecordset strSQL
DoCmd.SetFilter , "[CaseNum] Like '" & aCase & "' AND [AssignedDepartmentGroup] Like '" & DepartmentGroup & "' AND [AssignedManagerGroup] Like '" & ManagerGroup & "' AND [AssignedCaseGroup] LIKE '" & CaseGroup & "' OR [AssignedStaff] LIKE '" & Staff & "'"
Else
Beep
MsgBox ("Selected CLIENT not associated with any CASE."), vbOKOnly, "Warning"
End If
ElseIf Permission = "HR" Then
If Not IsNull(tClient) Then
CurrentDb.OpenRecordset strSQL
DoCmd.SetFilter , "[CaseNum] Like '" & aCase & "' AND [AssignedDepartmentGroup] Like '" & DepartmentGroup & "' AND [AssignedManagerGroup] Like '" & ManagerGroup & "' AND [AssignedCaseGroup] LIKE '" & CaseGroup & "' OR [AssignedStaff] LIKE '" & Staff & "'"
Else
Beep
MsgBox ("Selected CLIENT not associated with any CASE."), vbOKOnly, "Warning"
End If
ElseIf Permission = "User" Then
If Not IsNull(tClient) Then
CurrentDb.OpenRecordset strSQL
DoCmd.SetFilter , "[CaseNum] Like '" & aCase & "' AND [AssignedDepartmentGroup] Like '" & DepartmentGroup & "' AND [AssignedManagerGroup] Like '" & ManagerGroup & "' AND [AssignedCaseGroup] LIKE '" & CaseGroup & "' OR [AssignedStaff] LIKE '" & Staff & "'"
Else
Beep
MsgBox ("Staff member not associated with any CASE."), vbOKOnly, "Warning"
End If
ElseIf Nz(Permission, "") = "" Or Nz(Permission, "") = 0 Then
Beep
modEventLog.Tracker "Unauthorized Case Access Attempt - Permissions Null or 0"
DoCmd.Quit
ElseIf Permission <> "Admin" Or Permission <> "Manager" Or Permission <> "User" Or Permission <> "HR" Then
Beep
modEventLog.Tracker "Unauthorized Case Access Attempt - Permissions Not Recognized"
DoCmd.Quit
End If
Me!
[listSearch].RowSource = strSQL
Me!
[listSearch].Requery
End Sub