Combobox syncing

[NeoFax]

I have 5 comboboxes on my form that allow technicians to get to the
exact data they are looking for quickly. However, the syncing only
works for the first three comboboxes. Also, I cannot select say only
a value from the third box and it delimits previous boxes. Currently
I am using the way that is depicted in the microsoft website unbound
sample database by using [forms]![formname]![cmbName] and is null to
filter/sync the comboboxes. What I would like to do is use code to
accomplish this as it is more flexible than figuring out queries. ;^)
Here is the SQL for the fourth combobox:

SELECT qryMECAPPDumpPDPStation.[JOB CARD NUMBER]
FROM qryMECAPPDumpPDPStation
WHERE (((qryMECAPPDumpPDPStation.[HELO#])=[Forms]![frmIssues2]!
[cmbHelo]) AND ((qryMECAPPDumpPDPStation.Station)=[Forms]![frmIssues2]!
[cmbStation]) AND ((qryMECAPPDumpPDPStation.Area)=[Forms]![frmIssues2]!
[cmbArea])) OR (((qryMECAPPDumpPDPStation.[HELO#])=[Forms]!
[frmIssues2]![cmbHelo]) AND (([Forms]![frmIssues2]![cmbArea]) Is Null)
AND (([Forms]![frmIssues2]![cmbStation]) Is Null)) OR
(((qryMECAPPDumpPDPStation.Station)=[Forms]![frmIssues2]![cmbStation])
AND (([Forms]![frmIssues2]![cmbArea]) Is Null) AND (([Forms]!
[frmIssues2]![cmbHelo]) Is Null)) OR (((qryMECAPPDumpPDPStation.Area)=
[Forms]![frmIssues2]![cmbArea]) AND (([Forms]![frmIssues2]!
[cmbStation]) Is Null) AND (([Forms]![frmIssues2]![cmbHelo]) Is Null))
OR ((([Forms]![frmIssues2]![cmbArea]) Is Null) AND (([Forms]!
[frmIssues2]![cmbStation]) Is Null) AND (([Forms]![frmIssues2]!
[cmbHelo]) Is Null))
GROUP BY qryMECAPPDumpPDPStation.[JOB CARD NUMBER];


As you can see, I am missing multiple "if cmbbox x and y have values
but z is null then" type SQL info in this particular query. Any help
would be appreciated. Thanks!

 

[Arvin Meyer MVP]

It looks like your data is all within the same table. In fact, using a
drill-down method only the foreign keys should be in the table with the
combo box values for those keys coming from other tables. If you are using
Lookup fields, the keys aren't directly available, and that may be the root
of your problem. Have a look at the drill down (albeit only 2 levels) in the
following sample:

http://www.accessmvp.com/Arvin/Combo.zip

 

[NeoFax]

It looks like your data is all within the same table. In fact, using a
drill-down method only the foreign keys should be in the table with the
combo box values for those keys coming from other tables. If you are using
Lookup fields, the keys aren't directly available, and that may be the root
of your problem. Have a look at the drill down (albeit only 2 levels) in the
following sample:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I have 5 comboboxes on my form that allow technicians to get to the
exact data they are looking for quickly.  However, the syncing only
works for the first three comboboxes.  Also, I cannot select say only
a value from the third box and it delimits previous boxes.  Currently
I am using the way that is depicted in the microsoft website unbound
sample database by using [forms]![formname]![cmbName] and is null to
filter/sync the comboboxes.  What I would like to do is use code to
accomplish this as it is more flexible than figuring out queries. ;^)
Here is the SQL for the fourth combobox:

SELECT qryMECAPPDumpPDPStation.[JOB CARD NUMBER]
FROM qryMECAPPDumpPDPStation
WHERE (((qryMECAPPDumpPDPStation.[HELO#])=[Forms]![frmIssues2]!
[cmbHelo]) AND ((qryMECAPPDumpPDPStation.Station)=[Forms]![frmIssues2]!
[cmbStation]) AND ((qryMECAPPDumpPDPStation.Area)=[Forms]![frmIssues2]!
[cmbArea])) OR (((qryMECAPPDumpPDPStation.[HELO#])=[Forms]!
[frmIssues2]![cmbHelo]) AND (([Forms]![frmIssues2]![cmbArea]) Is Null)
AND (([Forms]![frmIssues2]![cmbStation]) Is Null)) OR
(((qryMECAPPDumpPDPStation.Station)=[Forms]![frmIssues2]![cmbStation])
AND (([Forms]![frmIssues2]![cmbArea]) Is Null) AND (([Forms]!
[frmIssues2]![cmbHelo]) Is Null)) OR (((qryMECAPPDumpPDPStation.Area)=
[Forms]![frmIssues2]![cmbArea]) AND (([Forms]![frmIssues2]!
[cmbStation]) Is Null) AND (([Forms]![frmIssues2]![cmbHelo]) Is Null))
OR ((([Forms]![frmIssues2]![cmbArea]) Is Null) AND (([Forms]!
[frmIssues2]![cmbStation]) Is Null) AND (([Forms]![frmIssues2]!
[cmbHelo]) Is Null))
GROUP BY qryMECAPPDumpPDPStation.[JOB CARD NUMBER];

As you can see, I am missing multiple "if cmbbox x and y have values
but z is null then" type SQL info in this particular query.  Any help
would be appreciated.  Thanks!- Hide quoted text -

- Show quoted text -

I solved my problem by building the SQL syntax programaticaly
(spelling?) and setting this SQL string as the rowsource of each
combobox. It works great! Thanks for whomever posted this in another
thread for something else. I borrowed and manipulated it for my
problem. Here is the code for future reference:

On Error GoTo Err_cmbStation_GotFocus

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
Dim strSQL As String
Dim strHelo As String
Dim strStation As String
Dim strArea As String
Dim strJobCard As String
Dim strResp As String

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cmbHelo) Then
strHelo = "((qryMECAPPDumpPDPStation.[HELO#]) = " & Me.cmbHelo &
") AND "
End If

'Text field example. Use quotes around the value in the string.
'If Not IsNull(Me.cmbStation) Then
' strStation = "((qryMECAPPDumpPDPStation.STATION) = """ &
Me.cmbStation & """) AND "
'End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbArea) Then
strArea = "((qryMECAPPDumpPDPStation.Area) = """ & Me.cmbArea &
""") AND "
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbJobCard) Then
strJobCard = "((qryMECAPPDumpPDPStation.[JOB CARD NUMBER]) = """ &
Me.cmbJobCard & """) AND "
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbLead) Then
strResp = strSQL & "((qryMECAPPDumpPDPStation.RESPONSIBILITY) =
""" & Me.cmbLead & """) AND "
End If

strWhere = strHelo & strStation & strArea & strJobCard & strResp

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5

If strWhere = "" Then
strSQL = "SELECT qryMECAPPDumpPDPStation.[HELO#]" & _
" FROM qryMECAPPDumpPDPStation" & _
" GROUP BY qryMECAPPDumpPDPStation.[HELO#];"
Me.cmbStation.RowSource = strSQL
GoTo Exit_cmbStation_GotFocus
End If

strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
Debug.Print strWhere

'strSQL = "SELECT qryMECAPPDumpPDPStation.[HELO#]" & _
'" FROM qryMECAPPDumpPDPStation" & _
'" WHERE (((qryMECAPPDumpPDPStation.STATION) = """4fl"""") And
((qryMECAPPDumpPDPStation.Area) = """4fl"""")" & _
'" And ((qryMECAPPDumpPDPStation.[JOB CARD NUMBER]) = """4fl"""") And
((qryMECAPPDumpPDPStation.RESPONSIBILITY) = """4fl""""))" & _
'" GROUP BY qryMECAPPDumpPDPStation.[HELO#];"

strSQL = "SELECT qryMECAPPDumpPDPStation.[STATION]" & _
" FROM qryMECAPPDumpPDPStation" & _
" WHERE (" & strWhere & ")" & _
" GROUP BY qryMECAPPDumpPDPStation.[STATION];"

If Not strWhere = "" Then
'Finally, apply the string as the form's RowSource.
Me.cmbStation.RowSource = strSQL
End If

Exit_cmbStation_GotFocus:
Exit Sub

Err_cmbStation_GotFocus:
MsgBox Err.DESCRIPTION
Resume Exit_cmbStation_GotFocus

I put this in the got focus event of each combobox.

 

[Barry]

I have a similar issue...My combobox is based on a query of a table with a
single field, and a datasheet in my subform has a foreign key based on a
query of a lookup field in a table. I can select values in the combobox if
the same value already exists in the datasheet, but if the value does not
exist causes the value in the combobox to be unselectable. Could this be due
to the lookup table field which I have linked to the combobox?