Combo Box - Unique Situation

N

NEWER USER

I have five combo boxes . After Update, the next box gets focus, drops down
and displays the values based on prior selection. My fifth combo box in most
cases has one value only. I need help adding code before the fifth box drops
down (lstEngine).

Private Sub lstEngine_GotFocus()
On Error Resume Next
Me.Controls("lstEngine").RowSource = "SELECT qryCatalogData.Engine FROM
qryCatalogData GROUP BY qryCatalogData.Engine, qryCatalogData.SGroup,
qryCatalogData.Model, qryCatalogData.Make, qryCatalogData.Year,
qryCatalogData.Description HAVING
(((qryCatalogData.Make)=[Forms]![frmCatalog]![lstMake])) And
(((qryCatalogData.Year)=[Forms]![frmCatalog]![lstYear])) And
(((qryCatalogData.Model)=[Forms]![frmCatalog]![lstModel])) And
(((qryCatalogData.Description)=[Forms]![frmCatalog]![lstGroup]))ORDER BY
qryCatalogData.Engine;"
End Sub

Private Sub lstEngine_AfterUpdate()
Dim strSQL As String
Dim strRecordsource As String
On Error Resume Next

strRecordsource = "qryCatalog"

' build sql string for form's RecordSource
strSQL = "SELECT * FROM " & strRecordsource

Me.RecordSource = strSQL

Call SetVisibility(True)

End Sub

If there is only one value in the list, accept that value (so I don't have
to) and display records in the query, else display multiple values and I will
select one and then the records are displayed. ANY help appreciated.
 
M

Mr. B

Hi, Newer User",

Try this code:

Dim varCount
Dim varVal
'check the number of items in the list
varCount = Me.NameOfComboBox.ListCount
If varCount = 1 Then
'get the only value in the list
varVal = Me.NameOfComboBox.ItemData(0)
'assign the value to the combo box
Me.NameOfComboBox.Value = varVal
End If
 
N

NEWER USER

Your suggestions work very well. Thanks again for your help.

Mr. B said:
Hi, Newer User",

Try this code:

Dim varCount
Dim varVal
'check the number of items in the list
varCount = Me.NameOfComboBox.ListCount
If varCount = 1 Then
'get the only value in the list
varVal = Me.NameOfComboBox.ItemData(0)
'assign the value to the combo box
Me.NameOfComboBox.Value = varVal
End If

-----
HTH
Mr. B
askdoctoraccess dot com


NEWER USER said:
I have five combo boxes . After Update, the next box gets focus, drops down
and displays the values based on prior selection. My fifth combo box in most
cases has one value only. I need help adding code before the fifth box drops
down (lstEngine).

Private Sub lstEngine_GotFocus()
On Error Resume Next
Me.Controls("lstEngine").RowSource = "SELECT qryCatalogData.Engine FROM
qryCatalogData GROUP BY qryCatalogData.Engine, qryCatalogData.SGroup,
qryCatalogData.Model, qryCatalogData.Make, qryCatalogData.Year,
qryCatalogData.Description HAVING
(((qryCatalogData.Make)=[Forms]![frmCatalog]![lstMake])) And
(((qryCatalogData.Year)=[Forms]![frmCatalog]![lstYear])) And
(((qryCatalogData.Model)=[Forms]![frmCatalog]![lstModel])) And
(((qryCatalogData.Description)=[Forms]![frmCatalog]![lstGroup]))ORDER BY
qryCatalogData.Engine;"
End Sub

Private Sub lstEngine_AfterUpdate()
Dim strSQL As String
Dim strRecordsource As String
On Error Resume Next

strRecordsource = "qryCatalog"

' build sql string for form's RecordSource
strSQL = "SELECT * FROM " & strRecordsource

Me.RecordSource = strSQL

Call SetVisibility(True)

End Sub

If there is only one value in the list, accept that value (so I don't have
to) and display records in the query, else display multiple values and I will
select one and then the records are displayed. ANY help appreciated.
Click to expand...
Click to expand...
 
Top