2 Combo boxes cacel out the filter. 1

[mutley1]

I am trying to get a 2 drop down combo boxes in a form header to filter the detail in the form, but every time I select the value from the first it is fine, but when I select the 2nd it wipes out the filter from the first. Example data:

Table being used for detail is called "Clm837PQualifier", fields are:

Q_Loop
Q_Segment
Q_Qualifier
Q_Description

1 Loop can have several segments
Segments can appear in more than 1 loop, e.g.

Loop Segment
A Name
A Date
B Name
B Address
B Color
B Date

Each Loop segment may have several qualifiers, but that isn't the problem - the filtering is. 2 combo drop down boxes are LoopDD and SegmentDD.

I have values in LoopDD (distinct Loop from qualifier) - Fine
I have values in Segment DD (distinct segment from qualifier) - Fine.
I have a query in the "on click" of LoopDD so that once the loop is chosen (requery SegmentDD in the macro),
the SegmentDD box offers only those segments relevant to the loop - Fine.

Problem is:
1. I click on LoopDD and select A - this filters the detail to Loop A and relevant Segments (Name and Date) in the detail, and the only 2 options in the SegmentDD box are now Name and Date (as they are the 2 linked to Loop A).
2. I click on Date in the SegmentDD box and it clears the first filter from the macro on the LoopDD box and filters only on Date, so the detail now shows Loop A, Date and any qualifiers relevant, AND Loop B, Date segments and qualifiers.

How do I get it to filter by Loop, OR segment, OR preferably by both without wiping out the previous filter.

Loop Macro is:
Conditions Action Argument
Requery SegmentDD
[Forms]![Clm837PQualifier]![LoopDD] Is Null RunCommand RemoveFilterSort
… SetCommand cmdShowAll, Enabled, 0
… StopAllMacros
SetTempVar strSearch, Replace([Forms]![Clm837PQualifier]![LoopDD],"""","""""")

SetTempVar strFilter, "([Q_Loop] Like ""*" & [TempVars]![strSearch] & "*"")"
ApplyFilter , =[TempVars]![strFilter],
RemoveTempVar strFilter
RemoveTempVar strSearch


Segment Macro is:

Conditions Action Argument
[Forms]![Clm837PQualifier]![SegmentDD] Is Null RunCommand RemoveFilterSort
… SetCommand cmdShowAll, Enabled, 0
… StopAllMacros
SetTempVar strSearch, Replace([Forms]![Clm837PQualifier]![SegmentDD],"""","""""")

SetTempVar strFilter, "([Q_Segment] Like ""*" & [TempVars]![strSearch] & "*"")"
ApplyFilter , =[TempVars]![strFilter],
RemoveTempVar strFilter
RemoveTempVar strSearch


I have tried using a strSearch2, strFilter2 in the one macro so that there are kinda 2, 1 strSearch / filter on the Loop and 1 for the segment, but it doesn't want to play.

Please - any help appreciated for an Ex-SQL guy who is decidedly rubbish with Access.

 

[kjv1611]

Without browsing your entire post, it initially sounds to me like you're updating the same record source on each attempt.

So in other words, when you update ComboBox1, it's creating a new recordsource off the forms' recorsource, and then when you update ComboBox2, it is looking at the original record source, rather than the newly generated/edited record source.

So, I'd suggest what you need to do is to have the Combo Boxes modify a record source that can be changed/modified on the fly... I suppose there are different ways of doing this, but I believe that's your issue.

--

"If to err is human, then I must be some kind of human!" -Me

 

[mutley1]

Thanks kjv,

You are right. Combo box 1 filters the recordset by the selection made AND updates the oprions in the list in combo box 2 to the relevant subset. You are spot on saying that if i then select from box 2 it filters the report on that and not combo box 1 AND 2.

What I am trying to do is just that, filter on both, so box1 brings ther records for that loop and sets box2 to the relevant sub data, then if I select subdata, KEEP box1 filter in place and filter the resultant set by using box2. Question is how, as I am totally lost and tried a few googled suggestions but can't get anywhere.

TIA for any bone you can throw.

M.

 

[kjv1611]

Well, there are a couple different ways, I would think. One way I personally think would be the best would be this:

But the method I'm thinking would be best will be done in VBA. Are you comfortable going that route? If so, I'll help you through that route. If not, we can look at another method or two.

The VBA method I'm talking about would actually much simpler than you might think. Here's what I'm talking about...

First, in your Form VBA module, you'll define a Public Variable for a SQL String. Then in the AfterUpdate Event procedure for each combo box, you'd reset that SQL variable according to what you have.

For instance, with the flexibility of doing this in VBA, you could have this sort of setup... you could allow these options:
1. combo1, then combo2 filter....
2. combo2, then combo1 filter....
3. combo1 only
4. combo2 only
5. For some reason, the user needs to cancel one or the other - you could easily handle that as well, by adding in an extra 1,2, or 3 buttons, however you wanted to go. You'd be using basically the same code with mild variation sin each procedure.

Of course, there may be an easier method, and maybe a better method, I can't say. However, generally speaking, when you can program the actions in VBA, you get a better performance, and you get much better control over the situation - what does/does not happen.

--

"If to err is human, then I must be some kind of human!" -Me

 

[mutley1]

Anything would be great thanks - if you have some sample code or can point me in the direction of an exampe so I can try to build a code set that would be a great help.

Thanks,

M

 

[kjv1611]

Well, I'll just type up basically what I'd do.. I'll just do an example, and you can go from there..

Example:

You've got one table, tblyMTable
Therein, you've got some fields:
WidgetID, Number (AutoNumber)
Widget, Text
Quantity, Number
Color, Text
Height, Number
Width, Number
Length, Number
Weight, Number
Texture, Text
Manufacturer, Text

Then you've got your form, frmMyForm
Therein, You've got 2 Control Boxes, and 3 buttons:
cboColorSelect
cboTextureSelect

cmdCancelColor
cmdCancelTexture
cmdCancelAll
The buttons would be used to undo your selections in the items... so let's say you first selected Blue for the color, but decided you didn't want to specify a color, even after you selected a texture... just click the cancel color button...

cboColorSelect gets it's RowSource from tblColors
cboTextureSelect gets its RowSource from tblTextures

In your VBA Module, you've got a Public Variable, we'll just call it strSqlSource. That variable needs to be public, so that it can be used by the different controls.

So, with all of that, your code could look something like this:

Public strSqlSource As String

Private Sub cboColorSelect_AfterUpdate()
	If strSqlSource = vbNullString Then
		strSqlSource = "SELECT w.WidgetID ,w.Widget ,w.Quantity ,w.Color ,w.Texture " & Chr(13) & _
			"FROM tblWidgets w " & Chr(13) & _
			"WHERE (w.Color = '" & cboColorSelect & "')"
	Else
		strSqlSource = strSqlSource & " AND " & Chr(13) & Chr(9) & _
			"(w.Color = '" & cboColorSelect & "')"
	End If
	Form.RecordSource = strSqlSource
	Form.Requery
End Sub
				   
Private Sub cboTextureSelect_AfterUpdate()
	If strSqlSource = vbNullString Then
		strSqlSource = "SELECT w.WidgetID ,w.Widget ,w.Quantity ,w.Color ,w.Texture " & Chr(13) & _
			"FROM tblWidgets w " & Chr(13) & _
			"WHERE (w.Texture = '" & cboTextureSelect & "')"
	Else
		strSqlSource = strSqlSource & " AND " & Chr(13) & Chr(9) & _
			"(w.Texture = '" & cboTextureSelect & "')"
	End If
	Form.RecordSource = strSqlSource
	Form.Requery
End Sub				   
			   
Private Sub cmdCancelColor_Click()
	If strSqlSource = vbNullString Then
	Else
		If InStr(strSqlSource, "WHERE (w.Texture") Then
			strSqlSource = "SELECT w.WidgetID ,w.Widget ,w.Quantity ,w.Color ,w.Texture " & Chr(13) & _
				"FROM tblWidgets w " & Chr(13) & _
				"WHERE (w.Texture = '" & cboTextureSelect & "')"
			Form.RecordSource = strSqlSource
		Else
			strSqlSource = vbNullString
			Form.RecordSource = "SELECT w.WidgetID ,w.Widget ,w.Quantity ,w.Color ,w.Texture " & Chr(13) & _
			"FROM tblWidgets w "
	End If
	Form.Requery
End Sub

Private Sub cmdCancelTexture_Click()
	If strSqlSource = vbNullString Then
	Else
		If InStr(strSqlSource, "WHERE (w.Color") Then
			strSqlSource = "SELECT w.WidgetID ,w.Widget ,w.Quantity ,w.Color ,w.Texture " & Chr(13) & _
				"FROM tblWidgets w " & Chr(13) & _
				"WHERE (w.Texture = '" & cboTextureSelect & "')"
			Form.RecordSource = strSqlSource
		Else
			strSqlSource = vbNullString
			Form.RecordSource = "SELECT w.WidgetID ,w.Widget ,w.Quantity ,w.Texture ,w.Texture " & Chr(13) & _
			"FROM tblWidgets w "
	End If
	Form.Requery
End Sub				   
				   
Private Sub cmdCancelAll()
	strSqlSource = vbNullString
	Form.RecordSource = "SELECT w.WidgetID ,w.Widget ,w.Quantity ,w.Texture ,w.Texture " & Chr(13) & _
		"FROM tblWidgets w "
	Form.Requery
End Sub

Well, that code is untested, and it's just an idea. There probably are better ways out there, and of course, you con't have to include the Chr(13) and Chr(9) instances I used - I just did that so that if you need to do any debugging with Debug.Print, then you could read the final SQL a little better... Also, I could have a typo or two... maybe wrong property chosen... If you want, take that, edit it to fit your specifics, take out anything you don't want, and report back with what did/did not work.

--

"If to err is human, then I must be some kind of human!" -Me

 

[mutley1]

Thanks!