Been working on this for a while.
Found some code that is partially working. I have 4 combo boxes in the main form that i want to use to filter records in a sub-form.
It works for the first two combo boxes and i thinks it's because they are both text data. The other two consist of a number and the other a date. When i try to include one or both of these fields, the error i get is "Run-Time Error 3709" / "The search key is not found in any record".
I currently have the code designating the variables (strJobNo and strTrainDate) as String types. When i make them a Byte and the other a Date type i get a data mismatch error when i try to filter.
Appreciate any help to resolve this.
Here is the code that i currently have written...
================================================== ================================================== =========================
Private Sub cmdApplyFilter_Click()
Dim strCourseTitle As String
Dim strRosterNumber As String
Dim strJobNo As String
Dim strTrainDate As String
If IsNull(Me.cboTraining.Value) Then
strCourseTitle = "Like '*'"
Else
strCourseTitle = "='" & Me.cboTraining.Value & "'"
End If
If IsNull(Me.cboRosterNum.Value) Then
strRosterNumber = "Like '*'"
Else
strRosterNumber = "='" & Me.cboRosterNum.Value & "'"
End If
If IsNull(Me.cboJobNumber.Value) Then
strJobNo = "Like '*'"
Else
strJobNo = "='" & Me.cboJobNumber.Value & "'"
End If
If IsNull(Me.cboTrainingDate.Value) Then
strTrainDate = "Like '*'"
Else
strTrainDate = "='" & Me.cboTrainingDate.Value & "'"
End If
Me.fsbTrainingRosters_New.Form.Filter = " [strTR_CourseTitle]" & strCourseTitle & " AND [strTR_RosterNumber]" & strRosterNumber & _
" AND [bytJobNumber]" & strJobNo & " AND [dtmTR_TrainingDate]" & strTrainDate
Me.fsbTrainingRosters_New.Form.FilterOn = True
End Sub
================================================== ================================================== =========================