Subform Filter by Selection using multiple combo boxes (1 Viewer)

[Johnrg]

Hi All,

I am a compleate new user so please be as kind as possible especially with the technical talk!!

I work at a sawmilling company and am in the process of making a basic database to enter in and then to look over our current stocks.

I have a form (Stock Filter) that has eight unbound combo boxes that lookup the values in eight seperate product description tables.

These combo boxes and there related product description tables are called:

Grade
Treatment
Location
Drying
Finish
Nominal Width
Nominal Thickness
Actual Width
Actual Thickness

The subform (Stock Filter Subform) is a Query that shows each pack of timber we have in stock, the product description information from the above tables, length information plus some general summing information.

Basically I want a "Filter by Selection" on each combo box. When the user chooses the value from each combo box the subform query is filtered to only show the records that corresond to that choice. He or she might filter by "GRADE" and then by "ACTUAL WIDTH" etc and so on.

I presume I also need a command button that removes all the filters at once so the user can start again and would also like a command button that sends the filtered information to a report that we can print, fax or email to customers

I have read and re-read the manual, looked at help and searched the forum but just can't get it to work. Everything I have created so far has been through the use of wizards and I draw a blank on macros and codes.

Can any one give me a step by step on how to set this up?

If I can get one combo box to work, In can follow copy the information for the other boxes.

Really appreciate your help.

JohnG From New Zealand.

 

[CyberLynx]

In my opinion...the best way to go here since this is basically a typical search Form is to first ensure that the SubForm controls' Link Child Fields and Link Master Fields properties are empty. We want to modify the Record Source property of the SubForm itself by applying the necessary query required to display the desired results.

You need to either make a decision as to whether or not you would like to achieve a AND or an OR search for the selected Combo Box item(s). Most often an AND search is desired.

The idea here to achieve our goal is to dynamically create a SELECT Query string completely dependent upon the selections made from the nine different unbound Combo Boxes. You have provided a list of Combo Box Names and their related tables but there is no mention of the name of the table which is holding the Wood Stock therefore we will call it tblStock.

The stock search needs to be live, meaning, for every selection of any of the nine Combo Boxes the SubForm must fill with data related to any one of those selections at any given time. Because of this, we should place our code into a Function procedure, let's call this Function StockSearch. To call this function every time a Combo Box selection is made we need to place the call to this StockSearch Function directly into the OnClick event property window for each Combo Box, like this:

                 ___________________
On Click ...... |[U]=StockSearch()     [/U]|

The StockSearch Function will always check all nine Combo Boxes to see if they contain anything within their respective text windows. If they do then those items will be applied to the SELECT query the code will dynamically create and apply to the Record Source property of the SubForm. Here is the StockSearch Function which must reside within the Search Forms' Code Module:

Private Function StockSearch()
   On Error GoTo Error_StockSearch
   
   Dim StrgSQL As String
   Dim WhereClause As String
   
   StrgSQL = "SELECT * FROM tblStock"
   
   '1st Combo - Wood Grade - Assuming the Table Field 'Grade' is of a Text DataType
   If Len(Me.Grade.Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Grade]='" & Me.Grade.Value & "'"
   End If
   
   '2nd Combo - Wood Treatment - Assuming the Table Field 'Treatment' is of a Text DataType
   If Len(Me.Treatment.Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Treatment]='" & Me.Treatment.Value & "'"
   End If
   
   '3rd Combo - Stock Location - Assuming the Table Field 'Location' is of a Text DataType
   If Len(Me.Location.Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Location]='" & Me.Location.Value & "'"
   End If
   
   '4th Combo - Wood Drying (kilned) - Assuming the Table Field 'Drying' is of a Text DataType
   If Len(Me.Drying.Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Drying]='" & Me.Drying.Value & "'"
   End If
   
   '5th Combo - Wood Finish (S4S or whatever) - Assuming the Table Field 'Finish' is of a Text DataType
   If Len(Me.Finish.Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Finish]='" & Me.Finish.Value & "'"
   End If
   
   '6th Combo - Nominal Width - Assuming the Table Field 'Nominal Width' is of a Text DataType
   If Len(Me.[Nominal Width].Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Nominal Width]='" & Me.[Nominal Width].Value & "'"
   End If
   
   '7th Combo - Nominal Thickness - Assuming the Table Field 'Nominal Thickness' is of a Text DataType
   If Len(Me.[Nominal Thickness].Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Nominal Thickness]='" & Me.[Nominal Thickness].Value & "'"
   End If
   
   '8th Combo - Actual Width - Assuming the Table Field 'Actual Width' is of a Text DataType
   If Len(Me.[Actual Width].Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Actual Width]='" & Me.[Actual Width].Value & "'"
   End If
   
   '9th Combo - Actual Thickness - Assuming the Table Field 'Actual Thickness' is of a Text DataType
   If Len(Me.[Actual Thickness].Value & "") > 0 Then
      If WhereClause = "" Then WhereClause = " WHERE " Else WhereClause = "AND "
      WhereClause = WhereClause & "[Actual Thickness]='" & Me.[Actual Thickness].Value & "'"
   End If
   
   'And the WHERE clause string to our SELECT query String
   If WhereClause <> "" Then StrgSQL = StrgSQL & WhereClause
   
   StrgSQL = StrgSQL & ";"
   
   'Place our created SELECT Query into the Record Source property of SubForm.
   Forms("Stock Filter")("Stock Filter Subform").Form.RecordSource = StrgSQL
   
Exit_StockSearch:
   Exit Function
   
Error_StockSearch:
   MsgBox "StockSearch Function Error" & vbCr & vbCr & _
          Err.Number & " - " & Err.Description, vbExclamation, _
          "Stock Search Error"
   Resume Exit_StockSearch
End Function

Yes...I think you should also have a Command Button that will automatically clear the values displayed in all Combo Boxes and populate the SubForm will ALL Stock records.

Merely place this code into the OnClick event for the Command Button:

Dim Ctrl As Control

For Each Ctrl In Me.Controls
   If Ctrl.ControlType = acComboBox then Ctrl=Null
Next Ctrl

Call StockSearch

The dynamically created Query string used to populate the SubForm can also be applied to the Record Source property of a Report.

Now...Do keep in mind that the StockSearch Function will most likely not work as planned and will probably contain errors. This code was simply written off the top of my head and can not be tested against your Database since I don't have it. Control Names may be different, Table and or Table Field Names may be different, and if you are using apostrophes and or quote marks within the dimensions of your data, this can cause problems since the code does not deal with them. However, you should be able to get a fairly good idea of what was done here though.

If you post a Sample Database with tables and some data to work with, I will be more than happy to implement this within that Sample DB.

Hope this helps.

.

 

[Johnrg]

Mr Cyberlynx,
Thank you, you are a legend.
I really appreciate your time in replying.
To be honest alot of what you posted is totally over my head but I will try and work through this on Monday to get some result.
The database I am working on is 1mb compacted and repaired, is that to large to post here or email to you?
Thanks again for your help.
JohnG

 

[Johnrg]

Hi All,
Heres the database I have been working on.
As discussed the "STOCKFILTER" form is my headache.
I can't get the filters working, the clear filters command working or the "preview in report" command button working.
Any help would be greatly appreciated.
I tried the above code but to be honest (I know this probally sounds bad) but I am not sure where to put it!!.
Most of this database has been done with wizards.
Thanks and Regards
JohnG

 

[Johnrg]

Second time lucky, I could not upload database file so here it is zipped.
Thanks again
JohnG

 

[Johnrg]

Anyone get a chance to help me out with this one?

 

[CyberLynx]

Just downloaded your sample DB. Will take a quick look at it now.

.

 

[Johnrg]

Thanks for that, really appreciate your help.
JohnG

 

[CyberLynx]

Well John, here is your Sample DB back to (from one Sawmiller to another ) you and I believe it does the task you were looking for.

Due to the complexity of the Query used to pull the data from, a Dynamic Filter was the definite way to go in my opinion, not the dynamic query string into the RecordSource property as I had mentioned in the earlier post.

The Stock Filter Form now supports a direction of either AND or OR. Where as with AND, each item selected is added to the Filter and each record displayed must meet the requirements of each item selected. For example:

Grade: Merch AND Location: RANGIORA.

Each record displayed must match bot items.

Where as with OR, each item selected is added to the Filter and each record displayed May or May Not meet the requirements of each item selected. For Example:

Grade: Merch OR Location: RANGIORA.

Each record displayed must contain either the Merch Grade OR the Location RANGIORA.

The Form is Live meaning as soon as a item is selected or removed the SubForm is automatically updated right away with the records found. I also created a simple report so that you can run the filter against the Report via the Preview Report Button.

Hope this helps and good luck with your project.

.

 

[Johnrg]

I don't know what to say - that is fantastic!!
You have made my month!
Would you be interested in doing some other simple programming in this database for me on a commercial basis. Makes sense seeing you are in the industry and understand the talk etc.
Let me know if interested and I will forward my contact details and we can catch up off line on the details.
Thanks so much once again. That is brilliant!
JohnG

 

[CyberLynx]

Glad it worked out for you John.

The forum as a whole is here to assist you with whatever issues you may have with the development of your project. There are many many professionals here that can and will assist you.

If you prefer a more personal approach to your project (and many do) then you can send me a Private Message by clicking on my Name on any of the Posts I have issued.

.

 

[fabio]

CyberLynux, I am trying to use the Dynamic Filter, initially it works very well, however when a combo is cleared through the button next to the combo box, the subform is not updated. could you please help me?

 

[fabio]

I have added an If to check the "Filterclause" and now it works very well.
I am not able to insert code on this answer, so please check the indent for all llines.

----------------------------------------

Private Function StockSearch()
On Error GoTo Error_StockSearch

Dim FilterClause As String, D As Long

'Hold whether we Use AND or OR in our Filter Criteria
D = Me.DirectionGrp.Value

'1st Combo - Wood Grade - Assuming the Table Field 'Grade' is of a Text DataType
If Nz(Me.Grade.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Grade]='" & Me.Grade.Value & "'"
End If

'2nd Combo - Wood Treatment - Assuming the Table Field 'Treatment' is of a Text DataType
If Nz(Me.Treatment.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Treatment]='" & Me.Treatment.Value & "'"
End If

'3rd Combo - Stock Location - Assuming the Table Field 'Location' is of a Text DataType
If Nz(Me.Location.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Location]='" & Me.Location.Value & "'"
End If

'4th Combo - Wood Drying (kilned) - Assuming the Table Field 'Drying' is of a Text DataType
If Len(Me.Drying.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Drying]='" & Me.Drying.Value & "'"
End If

'5th Combo - Wood Finish (S4S or whatever) - Assuming the Table Field 'Finish' is of a Text DataType
If Len(Me.Finish.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Finish]='" & Me.Finish.Value & "'"
End If

'6th Combo - Nominal Width - Assuming the Table Field 'Nominal Width' is of a Text DataType
If Len(Me.[NominalWidth].Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Width Nom]=" & Me.[NominalWidth].Value
End If

'7th Combo - Nominal Thickness - Assuming the Table Field 'Nominal Thickness' is of a Text DataType
If Len(Me.[NominalThickness].Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Thick Nom]=" & Me.[NominalThickness].Value
End If

'8th Combo - Actual Width - Assuming the Table Field 'Actual Width' is of a Text DataType
If Len(Me.[ActualWidth].Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Actual Width]=" & Me.[ActualWidth].Value
End If

'9th Combo - Actual Thickness - Assuming the Table Field 'Actual Thickness' is of a Text DataType
If Len(Me.[ActualThickness].Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[Actual Thick]=" & Me.[ActualThickness].Value
End If

If FilterClause = "" Then
Forms("Stock Filter")("Packet Volumes Query Form Search subform").Form.FilterOn = False
Else
'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("Stock Filter")("Packet Volumes Query Form Search subform").Form.Filter = CurrentFilter
'Turn on the Filter
Forms("Stock Filter")("Packet Volumes Query Form Search subform").Form.FilterOn = True
End If

Exit_StockSearch:
Exit Function

Error_StockSearch:
MsgBox "StockSearch Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"Stock Search Error"
Resume Exit_StockSearch
End Function

 

[nslemmons]

Hey all.

I am very much interested in using this for a filter on a subform. I have been working on it but am running into some problems.

Here's the VBA modified to reflect the object names in my database. I'm not sure what the problem is but I'm not getting the expected results.

Any help is apprciated.

Private Function rateSearch()
On Error GoTo Error_rateSearch

Dim FilterClause As String, D As Long

'Hold whether we Use AND or OR in our Filter Criteria
D = Me.filterGrp.value

If Nz(Me.cmbSname_L.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "cmbSname_L='" & Me.cmbSname_L.value & "'"
End If

If Nz(Me.cmbsCity_L.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "cmbsCity_L='" & Me.cmbsCity_L.value & "'"
End If

If Nz(Me.cmbsState_L.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "cmbsState_L='" & Me.cmbsState_L.value & "'"
End If

If Len(Me.cmbcName_L.value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "cmbcName_L='" & Me.cmbcName_L.value & "'"
End If
If Len(Me.cmbcCity_L.value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "cmbcCity_L='" & Me.cmbcCity_L.value & "'"
End If
If Len(Me.cmbcState_L.value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "cmbcState_L='" & Me.cmbcState_L.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("frmLookUpRates")("chdLookUpRate").Form.Filter = CurrentFilter
'Turn on the Filter
Forms("frmLookUpRates")("chdLookUpRate").Form.FilterOn = True

Exit_rateSearch:
Exit Function

Error_rateSearch:
MsgBox "rateSearch Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"Rate Search Error"
Resume Exit_rateSearch
End Function


Thanks,
Nate