Search Form

[cjwilkinson]

Hi guys,

I am trying to build a search form based on a form (frmProductSearch) and a subform (frmSubProductSearch).

frmProductSearch: has about 5 unbound combo boxes all cascaded to one another and should control the subform (e.g.: cmbProductBrand, cmbProductFamily, cmbProductGroup, cmbProductType and cmbProductCode)

frmSubProductSearch: is a datasheet controlled by the 5 comboboxes in the main form.

Can someone send me an example of this setup? I have no idea how to:
- Have all the combo boxes filter each other automatically depending on what you select
- Have the subform controlled by multiple combo boxes.

Can some explain this to me or send me an example of this. Loads of databases probably work with something similar to this.

Many thanks for your feedback!

 

[DevastatioN]

Hello,

I am not sure I completely 100% understand but here is my best shot. If you want comboboxes to filter each other, you may wish to look up "Cascading Comboboxes".

To make these comboboxes filter the datasheet subform however, you can use the following code attached to a "search button" or in the AfterUpdate event of each combobox.

Private Sub btnSearch_Click()
'Button filters out records depending on the criteria entered in the search fields txtSerial and txtKeyword
Dim strWhere As String
Dim lngLen As Long

'Stores the search criteria for the Product Brand from the cmbProductBrand and enters it into the Where Condition Statement
If Not IsNull(Me.cmbProductBrand) Then
strWhere = "( [ProductBrand] Like ""*" & Me.cmbProductBrand & "*"") AND "
End If

'Stores the search criteria for the Product Family from the cmbProductFamily and enters it into the Where Condition Statement
If Not IsNull(Me.cmbProductFamily) Then
strWhere = "( [ProductFamily] Like ""*" & Me.cmbProductFamily & "*"") AND " & strWhere
End If

'Stores the search criteria for the Product Group from the cmbProductGroup and enters it into the Where Condition Statement
If Not IsNull(Me.cmbProductGroup) Then
strWhere = "( [ProductGroup] Like ""*" & Me.cmbProductGroup & "*"") AND " & strWhere
End If

'Stores the search criteria for the Product Type from the cmbProductType and enters it into the Where Condition Statement
If Not IsNull(Me.cmbProductType) Then
strWhere = "( [ProductType] Like ""*" & Me.cmbProductType & "*"") AND " & strWhere
End If

'Stores the search criteria for the Product Code from the cmbProductCode and enters it into the Where Condition Statement
If Not IsNull(Me.cmbProductCode) Then
strWhere = "( [ProductCode] Like ""*" & Me.cmbProductCode & "*"") AND " & strWhere
End If

'This is used to cut off the " AND " at the end of the Where Condition
lngLen = Len(strWhere) - 5

'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria in the fields provided before using the search feature.", , "Search Criteria Error"
Else
strWhere = Left$(strWhere, lngLen)
frmSubProductSearch.Form.Filter = strWhere
frmSubProductSearch.Form.FilterOn = True
End If
End Sub

I hope this is helpful, note that the field names such as [ProductFamily] etc. are the names of the fields located on the subform itself.