# Database Discussions > Microsoft Access >  Multi criteria combo query problem

## compton

Hi.
I hope someone may be able to help with query multi-criteria problem I am so far unable to overcome.


My database is for recording the decorations and contents of apartments and houses.

Each apartment or house has an address. Within each address there are Areas (rooms) and Parts (parts of an Area which are then described)


On my main form there is an address ID field 


I have two combos to search / filter the current address data which returns the search / filter on a data entry sub form.

Both combos return information from the same table (Inventory)


I have one combo where an area can be selected via a query. (Works ok) (cboArea)


The second combo (cboPart) displays Parts (parts of the pre-selected Area) This is the problem query. 

The second combo query (based on the choice in the first combo) successfully returns only applicable parts for the area previously selected in the first combo for the current address.

Problem. It is convenient within the concepts of the database to be able to click the second combo without making a choice from the first combo as in this circumstance all the 'Parts' from the searched table are displayed. This, for example, is so I can work on (have returned) all of the ceilings or walls or floor coverings etc in all of the current address Areas (rooms) 

However the second combo used without making a choice in the first combo returns all 'Parts' for all addresses in the table being searched regardless of the current property address.

My question is:

Is there a criteria for the second combo I can add which will only return all Parts for the current address when nothing is selected from the first combo but still allow the current filtering when a choice is made from the first combo.

Here is the SQL statement in question for the second combo


SELECT DISTINCT Inventory.Part, Inventory.AddressID, [AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null AS Expr1
FROM Inventory
WHERE ((([AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null)=True) AND 

((Inventory.Area)=[Forms]![InventoryReport]![cboArea])) OR ((([Forms]![InventoryReport]![cboArea]) Is Null))
ORDER BY Inventory.Part;



i would like to learn how to express the following for combo two:

If an area choice is selected from combo one (cboArea) display all the Parts in the table for that area filtered by the current address ID
If an area choice is not made from combo one (cboArea) combo two to return all the parts in the table but only for the current address.

Apologies for being long winded.
Hoping someone may be interested to help.

----------


## Allan Murphy

In the GotFocus event of your combo box cobPart you will need to set the recordsource for the combo box based on the cboArea combo box and then requery the cboPart combo box.

This is the "air code"


```
Sub cboPart GotFocus()
dim sqlcode as string
 if isnull(cboArea) then
    ' area is not selected then display all the parts for this address
     'create the sql code
     sqlcode= now enter the sql code to display all the parts at this address
     ' set the record source the cboPart combo box
      me!cboPart.recordsource= sqlcode
  else
      ' area is selected
      create the sql code to show the parts for the selected area at the selected address
      sqlcode= now enter the sql code to display the parts for the selected area at this address
       me!cboPart.recordsource=sqlcode
   end if

   ' requery the combo box so that the correct data will be displayed based on the cboArea selection if any.
  docmd.requery "cboPart"
End Sub
```

If you need assistance post a sample database with dummy data to the forum.

----------


## compton

Thanks Allan.
Have not tested yes but this approach looks like a good solution. Question. Would I need to remove the current Row Source Type, Row Source etc data from the cboPart Data properties and rely only on the GotFocus code?

----------


## Allan Murphy

Yes, you will need to remove the current row source in the Properties. 
The GotFocus will assign the rowsource when the user selects the down arrow or moves into the control to start entering the Part.

----------


## compton

Thanks for the clarification.

----------


## compton

I have constructed working queries for both parts of the code (when an choice is made from cboArea and when cboArea is empty) and copied the SQL for each alternative

I have removed the current row row source in the properties from cboPart

The problem for me know is how to format the copied SQL code in to the GetFocus event so it is recognized by vba. (sqlcode=)

I have tried enclosing each SQL statement in quotes but this does not work. I would welcome any suggestions for translating the SQL to be read successfully by vba. . SQL statements as below:

SQL copied from working query for when an Area choice is made (sqlcode=)

SELECT DISTINCT Inventory.Part, Inventory.AddressID, [AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null AS Expr1
FROM Inventory
WHERE ((([AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null)=True) AND 
((Inventory.Area)=[Forms]![InventoryReport]![cboArea])) OR ((([Forms]![InventoryReport]![cboArea]) Is Null))
ORDER BY Inventory.Part;

-----
SQL copied from working query when Area combo is empty (for sqlcode=)

SELECT DISTINCT Inventory.Part, Inventory.AddressID, [AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null AS Expr1
FROM Inventory
WHERE ((([AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null)=True))
ORDER BY Inventory.Part;

----------


## compton

Problem Solved.
For anyone encountering a similar problem to the one I set out. I have solved the problem thanks to Allen putting me on the right path with his suggested solution and other help regarding expressing SQL in vba from the excellent information at http://www.fontstuff.com/access/acctut15.htm

For anyone interested here is the code that now does exactly as I required.

Note that the SQL view for Row Source property set for the cboArea is:
SELECT DISTINCT Inventory.Area, Inventory.AddressID, [AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null AS Expr1
FROM Inventory
WHERE ((([AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null)=True));

The Row Source property set for the cboPart is left empty as the OnEnter event for cboPart as below handles this:


Private Sub cboPart_Enter()
On Error GoTo Err_cboPart_Enter

Me.Refresh
'MsgBox "txtInvID"

 Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim sqlcode As String
    Dim addrRef As Long
    Dim areaV As String

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryPartComboCrtiteria")
    addrRef = Me.txtInvID
    'MsgBox (txtInvID) 'Check addressID

        Me.cboArea.Requery 'This seems to be essential to avoid Null errror

    If IsNull(Me.cboArea.Value) Or Len(Me.cboArea.Value & vbNullString) = 0 Then
    	'MsgBox "Area is empty" 'Debugging check
   	 'areaV = "-0-" 'Debugging check
   	 'MsgBox (areaV) 'Debugging check to confirm no string has been selected from the Area combo

    ' area is not selected then display all the parts for this address
     'create the sql code. Note: SELECT DISTINCT suppresses duplicates
     sqlcode = "SELECT DISTINCT Inventory.Part " & _
             "FROM Inventory " & _
             "WHERE Inventory.AddressID = " & addrRef & _
             " ORDER BY Inventory.Part;"
        ' Pass the SQL string to the query
            qdf.SQL = sqlcode
            'DoCmd.OpenQuery "qryPartComboCrtiteria" 'Debugging check
         ' set the record source the cboPart combo box
            Me.cboPart.RowSource = sqlcode

  Else
    'MsgBox "Area has data" 'Debugging check
    'Area has data so only return parts matching the selected area
    sqlcode = "SELECT DISTINCT Inventory.Part " & _
             "FROM Inventory " & _
             "WHERE Inventory.AddressID = " & addrRef & _
             "AND Inventory.Area='" & Me.cboArea.Value & "' " & _
             "ORDER BY Inventory.Part;"
        ' Pass the SQL string to the query
            qdf.SQL = sqlcode
           	 'DoCmd.OpenQuery "qryPartComboCrtiteria" Debugging check
         ' set the record source the cboPart combo box
            Me.cboPart.RowSource = sqlcode    
    End If

   ' Clear the object variables
        Set qdf = Nothing
        Set db = Nothing

Exit_cboPart_Enter:
    Exit Sub

Err_cboPart_Enter:
    MsgBox err.Description & vbCrLf & err.Number
    Resume Exit_cboPart_Enter

End Sub

----------

