Results 1 to 7 of 7

Thread: Multi criteria combo query problem

  1. #1
    Join Date
    Aug 2014
    Posts
    6

    Multi criteria combo query problem

    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.

  2. #2
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    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"
    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.
    Allan

  3. #3
    Join Date
    Aug 2014
    Posts
    6
    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?

  4. #4
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    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.
    Allan

  5. #5
    Join Date
    Aug 2014
    Posts
    6
    Thanks for the clarification.

  6. #6
    Join Date
    Aug 2014
    Posts
    6
    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;

  7. #7
    Join Date
    Aug 2014
    Posts
    6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •