multi-filter ...combo box in one form

[SSDA]

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

if i choose one items from one combo box like "DamID"...and 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 RqryAnimalsRepoert 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
 
 

[Seth Schrock]

Your If-Then for the cboDamID is messed up. Try re-copying my code into yours.

[SSDA]

sir .. still importing all data from the query ...
and the filter print :
AnimalStatus='alive' And AnimalType ='sheep'

note: i left the cboDamID BLANK
regards

[Seth Schrock]

Okay, so we have the filter working. Now, we need a way to get that filter to the query. Please post the SQL code for your query. I'm guessing that it might be simpler to just build the query string in code and then run it that way, but I can't say for sure until I see your query.

[SSDA]

please, see below

SELECT qAnimals.AnimalID, AnimalsStatus.AnimalStatus, AnimalsTypes.AnimalType, qAnimals.Age, qAnimals.locationID, qAnimals.fixedassets, qAnimals.AnimalSex, qAnimals.SireID, qAnimals.DamID, qAnimals.Ready, qAnimals.ReadyDate
FROM AnimalsStatus INNER JOIN (AnimalsTypes INNER JOIN qAnimals ON AnimalsTypes.AnimalTypeID = qAnimals.AnimalTypeID) ON AnimalsStatus.AnimalStatusID = qAnimals.AnimalStatusID;

[Seth Schrock]

Please use CODE tags when post code, VBA or SQL.

Add the following between lines 38 and 39:

Dim strSQL As String
Dim db As DAO.Database
Dim qry As DAO.QueryDef
 
strSQL = "SELECT qAnimals.AnimalID, AnimalsStatus.AnimalStatus, " & _
         "AnimalsTypes.AnimalType, qAnimals.Age, qAnimals.locationID, " & _
         "qAnimals.fixedassets, qAnimals.AnimalSex, qAnimals.SireID, qAnimals.DamID, " & _
         "qAnimals.Ready, qAnimals.ReadyDate " & _
         "FROM AnimalsStatus INNER JOIN (AnimalsTypes INNER JOIN qAnimals ON " & _
         "AnimalsTypes.AnimalTypeID = qAnimals.AnimalTypeID) ON " & _
         "AnimalsStatus.AnimalStatusID = qAnimals.AnimalStatusID " & _
         "WHERE " & strFilter
 
Set db = CurrentDb
Set qry = db.QueryDefs("RqryAnimalsRepoert") 'Check spelling on this query name
 
qry.SQL = strSQL
qry.Close
 
Set db = Nothing
Set qry = Nothing

Note the spelling check on line 15. I copied what you had in post #15, but just double check it.

[SSDA]

My Bro..

thanks ..now it is working in my demo Database , i will apply this code in the main project and inform u the final result ...

many many thanks for your support

regards

[Seth Schrock]

Glad to help. Good luck on your project.