Help with Popup form filter

[Brook]

Good Day All,

I have a form that I use to filter a report with 5 options the user has to
select from drop down cboboxes. As it is set up and working now, if the user
selects option1 (Size), and option2(Colour) the code only files those files
with the size and colour chosen. I would like for the filter to include all
colours for the size chosen. Can anyone help?

Below is my code for my cmd buttons:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptcheckingaccount].Filter = strSQL
Reports![rptcheckingaccount].FilterOn = True
Else
Reports![rptcheckingaccount].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Thanks,

Brook

 

[Arvin Meyer [MVP]]

Change the rowsource of the color cobo to read the form criteria:

Select * From tblSizeColor Where ColorID = Forms!MyFormName!cboSize

Then what you need to do is write some code in the after update event of the
size combo box which will requery the color combo.

Sub cboSize_AfterUpdate()
Me.cboColor.Requery
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

I almost forgot that there's a simple demo at the AccessMVP.com website:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Brook]

Thanks for the reponse,

I'm not sure if this will do what I need, but its close. B/c I guess
what I am actually wanting is to my formfilter set up so that I can have
Option1, Option2, Option3, Option4, and Option5.

The user can select to only filter by option1, or I would like to be able
for the user to select option2 and the filter will include all items With
option1 and option2 criteria.

Thanks,

Brook

I almost forgot that there's a simple demo at the AccessMVP.com website:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Good Day All,

I have a form that I use to filter a report with 5 options the user has to
select from drop down cboboxes. As it is set up and working now, if the user
selects option1 (Size), and option2(Colour) the code only files those files
with the size and colour chosen. I would like for the filter to include all
colours for the size chosen. Can anyone help?

Below is my code for my cmd buttons:

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptcheckingaccount].Filter = strSQL
Reports![rptcheckingaccount].FilterOn = True
Else
Reports![rptcheckingaccount].FilterOn = False
End If

End Sub


Private Sub Command29_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Thanks,

Brook

 

[Arvin Meyer [MVP]]

Thanks for the reponse,

I'm not sure if this will do what I need, but its close. B/c I guess
what I am actually wanting is to my formfilter set up so that I can have
Option1, Option2, Option3, Option4, and Option5.

The user can select to only filter by option1, or I would like to be able
for the user to select option2 and the filter will include all items With
option1 and option2 criteria.

Only if you build the option 2 filter to include option1 data. To do
otherwise, you'd need to use an IN Clause to include each of the others, and
I'm not sure if you can include more that 1 Select statement in an IN
Clause. To see an example of a Select statement in an IN Clause, run the
Find Unmathched Query Wizard.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access