It would be helpful to see your dB.
I see a few issues you should resolve.
One is you have a table named "POD_Form" and a form named "POD_Form". Not a good idea having multiple objects with the same name. Better would be "tblPOD" and "frmPOD".
Another is that it seems that every table has a PK field named "ID". That gets confusing....
Also:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
There is no such thing as a "combo-button". There is a combo box.
The combo box should not have any code in the after update event. The code in the combo box after update event and the code for the search button are fighting each other. (BTW, "Search" is a reserved word)
The line " Dim strCriteria, task As String" isn't doing what you think it is doing (nor what you want it to do)
"strCriteria" is a Variant and "Task" is a string. You MUST explicitly declare the variable type. You should have
Code:
Dim strCriteria As String, task As String
or
Code:
Dim strCriteria As String
Dim task As String
To search a form, you build the criteria string, then set the form filter property. Much easier that what you are trying to do.
Code:
Option Compare Database '<< should be at the top of EVERY module
Option Explicit '<< should be at the top of EVERY module
Sub btnSearch_Click() '<<-- note that this is the click event of the button Search
Dim strCriteria
Dim task As String
If IsNull(Me.RatesValidFrom) Or IsNull(Me.RatesValidTo) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.RatesValidFrom.SetFocus
Else
strCriteria = "([Rates Validity] Between #" & Me.RatesValidFrom & "# And #" & Me.RatesValidTo & "# And "
'check to see if a POL has been selected
If Len(Trim(Me.cbopol & "")) > 0 Then
strCriteria = strCriteria & "POL = '" & Me.cbopol & "' And " '<<-- if cboPOL bound column is Text
' strCriteria = strCriteria & "POL = " & Me.cbopol & " And " '<<-- if cboPOL bound column is a number
End If
'remove the last 5 characters (spaceANDspace)
strCriteria = Left(strCriteria, Len(strCriteria) - 5)
'now filter the form
Me.Filter = strCriteria
Me.FilterOn = True
End If
End Sub
Private Sub Command103_Click() ' <<-- need a better name maybe "btnClear"
' I think this is the clear button
Me.RatesValidFrom = Null
Me.RatesValidTo = Null
Me.cbo = Null
Me.Filter = vbNullString
Me.FilterOn = False
End Sub