multi-filter ...combo box in one form

[SSDA]

Hi dears
there is 8 combo box must be make filter on "RqryAnimalsRep oert"

if i choose one items from one combo box like "DamID"...a nd leave other "null"
the report must be make filter as "DamID" only and leave the other filter blank

...

please, see the attached file
regards

[Seth Schrock]

You will need to use VBA to test for the combo boxes to be null to build your filter string. Sorry, I can't open the attachment at work.

[SSDA]

i did it ...by this code

Private Sub btnOpenreport_Click()
 ' cboDamID
 If IsNull(Me.cboDamID) Then
 Me.Filter = "DamID=''"
 Me.FilterOn = True
 Else
 Me.Filter = "DamID='[cboDamID]'"
 Me.FilterOn = True
 End If
 
 'cboAnimalStatus
  If IsNull(Me.cboAnimalStatus) Then
 Me.Filter = "AnimalStatus=''"
 Me.FilterOn = True
 Else
 Me.Filter = "AnimalStatus='[cboAnimalStatus]'"
 Me.FilterOn = True
 
 End If
'cboAnimalType
  If IsNull(Me.cboAnimalType) Then
 Me.Filter = "AnimalType=''"
 Me.FilterOn = True
 Else
 Me.Filter = "AnimalType='[cboAnimalType]'"
 Me.FilterOn = True
 End If
 
DoCmd.OpenQuery "RqryAnimalsRepoert"

End Sub

[Seth Schrock]

So you got it to work using this code, or this is the code that you tried and it isn't working?

[SSDA]

i tried this code ...but its not working

[Seth Schrock]

Your current code will only filter on one field because you are replacing the value in the filter each time you set it for the next combo box. Also, the references to your combo boxes were inside the double quotes so literal text cboAnimalStatus (etc.) was getting pass to the filter, not the value selected in the combo box. Try the following:

Private Sub btnOpenreport_Click()
Dim strFilter As String

 ' cboDamID
 If Not IsNull(Me.cboDamID) Then
	strFilter = "DamID='" & [cboDamID] & "'"
 End If
 
 'cboAnimalStatus
  If Not IsNull(Me.cboAnimalStatus) Then
	If strFilter = "" Then
		strFilter = "AnimalStatus='" & [cboAnimalStatus] & "'" 
	Else
		strFilter = strFilter & " And AnimalStatus ='" & cboAnimalStatus & "'"
	End If
 End If
 
'cboAnimalType
  If Not IsNull(Me.cboAnimalType) Then
	If strFilter = "" Then
		strFilter = "AnimalType='" & [cboAnimalType] & "'"
	Else
		strFilter = strFilter & " And AnimalType ='" & cboAnimalType & "'"
	End If
 End If
 
 Debug.Print strFilter

 Me.Filter = strFilter
 Me.FilterOn = (strFilter <> "")
 
DoCmd.OpenQuery "RqryAnimalsRepoert"
 
End Sub

[SSDA]

Sorry… what is the strcomputer
… i defined strfilter as string
…and no action

[Seth Schrock]

Sorry about that. I another project going on in my text editor and I didn't notice the the autocomplete had filled in the wrong thing. I also fixed some other stuff. Try it now.

[SSDA]

Until now there is no action , the query still importing all the data… and i made some modification on the code… also no responce…

You are tired with me, brother

[Seth Schrock]

Add the following line at line 29:

Debug.Print strFilter

This will output what the filter will be into the Immediate window. If you don't see the Immediate window, press Ctrl+G in the code editor and it will appear below your code.

[SSDA]

DamID='f-000000' and AnimalStatus ='alive' and AnimalType ='cheep'

this is the filter ... it is working as the first time
but if i left any combo box ... the filter will be empty and dont importing any data from the query


regards

[Seth Schrock]

Try leaving cboDamID blank and post back the result from the Immediate window.

Also, are you wanting the RqryAnimalsRepo ert query to get this filter as well?

[SSDA]

if i leaving cboDamID blank ... the Immediate window didnt print any thing

...

[Seth Schrock]

Please post your code. I just tested my code and it works no matter what combination of combo boxes I use.

[SSDA]

Option Compare Database


Private Sub btnOpenreport_Click()
Dim strfilter As String

 ' cboDamID
 If Not IsNull(Me.cboDamID) Then
 strfilter = "DamID='" & [cboDamID] & "'"
  If strfilter = "" Then
   strfilter = "AnimalStatus='" & [cboAnimalStatus] & "'"
  End If
 End If
  
 'cboAnimalStatus
 If Not IsNull(Me.cboAnimalStatus) Then
 If strfilter = "" Then
 strfilter = "AnimalStatus='" & [cboAnimalStatus] & "'"
 Else
  strfilter = strfilter & " and AnimalStatus ='" & cboAnimalStatus & "'"
 
 End If
 End If
 
 'cboAnimalType

  If Not IsNull(Me.cboAnimalType) Then
 If strfilter = "" Then
 strfilter = "AnimalType='" & [cboAnimalType] & "'"
 Else
  strfilter = strfilter & " and AnimalType ='" & cboAnimalType & "'"
 
 End If
 End If
 
 Me.Filter = strfilter
 Me.FilterOn = (strfilter <> "")
 Debug.Print strfilter
 DoCmd.OpenQuery "RqryAnimalsRepoert"

End Sub