Please bear with me I am new to Access VBA.
This is my table below:
This is the combobox named cmbTask and has a rowsource property of:
SELECT DISTINCT Task FROM DDValues;
Field Task Values from the table show up in 1st combobox named cmbTask. I added an on change event to 1st combobox where if 'Building' or 'Mapping' is chosen from the combobox list, its corresponding Status values will show in the 2nd combobox dropdown named cmbStatus. cmbStatus has no rowsource property. Only coded its rowsource in Code Builder below.
Private Sub cmbTask_Change()
cmbStatus.RowSource = "SELECT Status from [DDValues] WHERE [Task] ='" & cmbTask & "' ORDER BY [ID] ASC"
cmbStatus.Requery
End Sub
But here is the result:
Why is this not showing the correct Status values based on Task value chosen?
Appreciate your help.
From image of the second combobox, it appears its RowSourceType property is set to "Value List" instead of "Table/Query". Correct that and issue should be resolved.
Other observations:
First combobox RowSource is more complicated than needed, although I am sure it works. SELECT DISTINCT Task FROM DDValues;
should be adequate. You use <> ""
in criteria - I expect Task field would never be empty so this filter is likely unnecessary. Also, don't confuse empty string with Null - two very different things. I NEVER allow empty strings in fields.
VBA code could be reduced by setting second combobox RowSource property in design to:
SELECT Status from [DDValues] WHERE [Task] = [cmbTask] ORDER BY [ID] ASC;
Then just Me.cmbStatus.Requery
in VBA.
If you want to save ID instead of repeating text in data records (field must be long integer number type), include ID in second combobox RowSource.
ColumnCount 2
ColumnWidths 0";1"
BoundColumn 1
If you save the ID, there really is no need to save Task. If Status values were saved and unique, would be no need to save Task. Since there is apparent duplicate usage, advise to save ID.
I would use AfterUpdate instead of OnChange event.