Creating a filter for continuous forms

[NJMike64]

Hi all,



need help on this one as I think I am getting screwed up with all the string identifier. I have a continuous forms where one field (unique) is called Part and another field is called MFG (It's a parts catalog if you will)

I want to create 2 combo boxes in the form header, where you can only use one or the other. The first would list all the part numbers and when you select a part number it will only show that part below. The other is for MFG where it will show the unique list of MFG and when you select, all the parts for that mfg will be shown in the form.

can someone help out on this???

Thanks
Mike

[ssanfu]

You want two unbound combo boxes in the header. They would be exclusive of the other.

You didn't say what the record source for the form is, so lets call it a table named "tblCatalog". And both "Part" and "MFG" are text type fields.

One combo box named "cboPart".
The row source is "SELECT Part FROM tblCatalog"
In the afterupdate event, the code would be:

Private Sub Part_AfterUpdate()
    Me.Filter = "Part = '" & Me.cboPart & "'"
    Me.FilterOn = True
End Sub

The second combo box name "cboMFG".
The row source is "SELECT DISTINCT MFG FROM tblCatalog"
In the afterupdate event, the code would be:

Private Sub MFG_AfterUpdate()
    Me.Filter = "MFG = '" & Me.cboMFG & "'"
    Me.FilterOn = True
End Sub

Might also have a button to remove the filter.

Me.Filter = ""
Me.FilterOn = False

[NJMike64]

Thanks Steve. That helped. I also found another solution which is actually perfect that you put unbound fields about the continuous form area and write code to invoke the filter such as;

The srchpart is the new unbound field and the part is the field from the table shown. You repeat the if statement for each unbound field related to a bound one and you execute after update the code below.

This works perfectly. Unfortunately, the author did not put his name on the sample db and I can't remember which site I pulled it from, but the name of the DB is FilterDemo. Hats off to that programmer

Private Function UpdateFilter()

Dim a As String

' Build a filter string based on user input

If Not IsNull(Me.srchPart) Then
a = a & " AND part like '*" & Me.srchPart & "*'"
End If

If a = "" Then
' No filter required
Me.Filter = ""
Me.FilterOn = False
Else
' Add filter to form
Me.Filter = Right(a, Len(a) - 4)
Me.FilterOn = True
End If

End Function