I know this error is well covered ground but all the solutions suggested have not worked and it's driving me to distraction!!!! 
I've created a form that allows a user to select a date from a combo box and a colleague from another combo box then click a button which builds a filter string and applies it to the form. The code for the button is as follows:
Private Sub Command64_Click()
Dim strDate As String
Dim strFilter As String
If IsNull(Me.cmbColleague) Or IsNull(Me.cmbDate) Then
MsgBox "Please select both a date and a colleague before clicking Refresh.", vbExclamation, "Error"
Exit Sub
End If
strDate = Format(Me.cmbDate, "mm/dd/yyyy")
strFilter = "AddedBy = " & Me.cmbColleague & " AND DateAdded = #" & strDate & "#"
Me.FilterOn = True
Me.Filter = strFilter
End Sub
AddedBy is the field containing an employee number and is a text type field. cmbColleague is a combo box using a query to pull all registered employees from the Colleagues table.
DateAdded is a date field containg the date the record was added. cmbDate is a combo box using a query to pull all the dates from the database.
Now this is the part I don't get. If I shorten the filter to just the date or just the colleague, it works fine. It's a soon as I try to filter on both that I get the error; "You cancelled the previous operation" on the last line of the code.
I've tried setting the filter then turning it on and as it is now turning on the filter first then setting it. I've tried setting up a new form but it's not working either.
Like I say the most baffling thing is it'll let me filter on one criteria but not both, and as far as I know i'm creating the multiple criteria filter correctly, or am I?
Any help would be hugely appreciated!!
I've created a form that allows a user to select a date from a combo box and a colleague from another combo box then click a button which builds a filter string and applies it to the form. The code for the button is as follows:
Private Sub Command64_Click()
Dim strDate As String
Dim strFilter As String
If IsNull(Me.cmbColleague) Or IsNull(Me.cmbDate) Then
MsgBox "Please select both a date and a colleague before clicking Refresh.", vbExclamation, "Error"
Exit Sub
End If
strDate = Format(Me.cmbDate, "mm/dd/yyyy")
strFilter = "AddedBy = " & Me.cmbColleague & " AND DateAdded = #" & strDate & "#"
Me.FilterOn = True
Me.Filter = strFilter
End Sub
AddedBy is the field containing an employee number and is a text type field. cmbColleague is a combo box using a query to pull all registered employees from the Colleagues table.
DateAdded is a date field containg the date the record was added. cmbDate is a combo box using a query to pull all the dates from the database.
Now this is the part I don't get. If I shorten the filter to just the date or just the colleague, it works fine. It's a soon as I try to filter on both that I get the error; "You cancelled the previous operation" on the last line of the code.
I've tried setting the filter then turning it on and as it is now turning on the filter first then setting it. I've tried setting up a new form but it's not working either.
Like I say the most baffling thing is it'll let me filter on one criteria but not both, and as far as I know i'm creating the multiple criteria filter correctly, or am I?
Any help would be hugely appreciated!!
I feel a wee bit daft now. That was a really simple solution and did in fact do the trick!
