Multiple combobox to filter subform

[huongdl1987]

Hi, I'm trying to filter a subform based on 4 comboboxes in the main form, which are:
City
State
Zip Code
Area

Basically, i want to search mother's based on these 4 comboboxes, so that the subform would show the filtered mom.

However, the code that I've been using keeps giving me error. Can you help me take a look at these? I've working on these for like 1 week and it's getting me no where!

Here is the code that I've been using:
[/code]

Private Function MomSearch()
On Error GoTo Error_MomSearch
Dim CurrentFilter As String
Dim FilterClause As String, D As Long

'Hold whether we use AND or OR in our filter criteria
D = Me.DirectionGrp.Value

'1st Combo- UpdatedMotherCity - The Table Field 'UpdatedMotherCity' is of a text datatype
If Nz(Me.cbxMomCity.Column(0), 0) > 0 Then


If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[cbxMomCity]='" & Me.cbxMomCity.Value & "'"
End If

'2nd Combo- UpdatedMomState - The Table Field 'UpdatedMotherCity' is of a text datatype
If Nz(Me.cbxMomState.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[cbxMomState]='" & Me.cbxMomState.Value & "'"
End If

'3rd Combo- UpdatedMomZip - The Table Field 'UpdatedMotherCity' is of a text datatype
If Nz(Me.cbxMomZip.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[cbxMomZip]='" & Me.cbxMomZip.Value & "'"
End If

'4th Combo- Area - The Table Field 'Area' is of a text datatype
If Len(Me.cbxMomArea.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[cbxMomArea]='" & Me.cbxMomArea.Value & "'"
End If

'Fill this form wide variable so that it can be used for the report
CurrentFilter = FilterClause: FilterClause = ""

'Place our created filter criteria into the filter property of subform.
Forms("MailMerge")("MailMergeQuery").Form.Filter = CurrentFilter
'Turn OFF the filter
Forms("MailMerge")("MailMergeQuery").Form.FilterOn = True

Exit_MomSearch:
Exit Function
Error_MomSearch:
MsgBox "MomSearch function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"Mothers Search Error"
Resume Exit_MomSearch


End Function




[/code]

AccessQuestion.zipI've also attached the file in this post.
Please ignore the 3 errors it gives asking for mother's name and address when you first open the file. I have to delete this information to protect the ID of the mothers.

Thank you,
Meo

[orange]

Please post the error code and message.

Have you tried stepping thru the code?

[huongdl1987]

The error is: MomSearch function error 2465- Access can't find the field 'MailMergeQuery' referred to in your expression.

I don't know how to save as mdb file??

Meo

[orange]

I think there is a button or dialog hat allows you to save in a previous version or earlier format????

Check your form names carefully, I think you have mis-identified the Form involved. Or your syntax is incorrect.

see this for syntax
http://access.mvps.org/access/forms/frm0031.htm

[amrut]

Orange is correct
'Place our created filter criteria into the filter property of subform.
Forms("MailMerge")("MailMergeQuery2").Form.Filter = CurrentFilter --------------Your subform control name is MailMergeQuery2
'Turn OFF the filter
Forms("MailMerge")("MailMergeQuery2").Form.FilterOn = True --------------Your subform control name is MailMergeQuery2