-
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.
-
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
-
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?
-
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
-
Thanks for the clarification.
-
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;
-
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
-
Forum Rules
|
|