I have an MS-Access form that will be used for data entry, as well as to view data if the user chooses to do so.
I have two combo boxes at the very top of the form.
ComboBox1 provides a list of “EntryTypes” that the user can choose. Once the EntryType is chosen, ComboBox2 should only display a list of IDs for previous records of that specific type. Then the user should be able to select a specific entry ID and view the record within the form.
I first added ComboBox2, in the pop-up-wizard I selected “Find a record based on…” and set it to look for a specific record based on the ID. This worked perfectly fine on its own.
Then I added ComboBox1, and added the following VBA:
Private Sub ComboBox1_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [TABLE].[ID]," & _
"[TABLE].[FIELD1]," & _
"[TABLE].[TYPEFIELD]," & _
"[TABLE].[IDFIELD]" & _
"FROM [TABLE] " & _
"WHERE [TYPEFIELD] = " & ComboBox1.Column(3).Text
ComboBox2.RowSource = strSQL
ComboBox2.Requery
End Sub
The reason I have “.Column(3).Text” :
Because ComboBox1 displays two fields of data from its source table (Source table has [ID], [FieldA], [FieldB]) but the other table where the records are entered, only has values of FieldB. So I figured with the ID column, I should specify this to match column 3 of the ComboBox1 selection with the entries in the table of records.
My hope is that ComboBox2 can retain its functionality of displaying records based on ID selection; I just want the filter of entrytypes to be applied first so users can look things up easier. But when I test this out, ComboBox2 does not update after something is selected in ComboBox1.
Is there anything wrong with my code? Is it even possible to apply this two-combo-box method when one of them already has a built-in function to find and display a record?
7