-
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
|
|