Cascading Combobox based on FieldList (1 Viewer)

[mumbles10]

Ok... I have two comboboxes, cmb1 and cmb2... I have cmb1 rowsourcetype = FieldNames and rowsource = tblInfo. What I would like it to do is populate cmb2 with the list of values based on the users selection of cmb1.

From what I have found and read... This needs to be done in VBA based on the changing rowsource... however, I tried this and I can't seem to get it to work. I keep getting an error that: Procedure Declaration does not match description of event or procedure having the same name...

Here is the VBA:

Private Sub FindValue()
    'Build a SQL statement to pull unique values
    'from whatever field name is selected in the form
    'if cmb1 is empty, do nothing
        Dim MySQL As String
        MySQL = "SELECT DISTINCT" + cmb1.Value
        MySQL = MySQL + " FROM " + tblInfo
        MySQL = MySQL + " WHERE " + cmb1.Value Is Not Null
        MySQL = MySQL + " ORDER BY " + cmb1.Value
        
    'Make SQL the rowsource
    Me!cmb2.RowSourceType = "Table/Query"
    Me!cmb2.RowSource = MySQL
    'Get SearchValue to show something from the drop down list
    Me!cmb2.Requery
    Me!cmb2.Value = Me!cmb2.ItemData(0)
    
  End If
  
    
End Sub

The Form_Load and cmb1_AfterUpdate are set to call the procedure...

Any thoughts?

 

[prley]

using the rowsource on a combo box works best like you have it with sql statements. But the sql statement usually is based on a table of data already known. If the data isn't know your better off creating a string of items seperated by ";" then making that string the new row source for cmb2. Use the click event of the cmb1 combo box each time the user clicks cmb1 to append values to the string. Then replacing the cmb2 rowsource with that string of items.

Oh and if you do this don't forget to change the row source type to values.

 

[mumbles10]

using the rowsource on a combo box works best like you have it with sql statements. But the sql statement usually is based on a table of data already known. If the data isn't know your better off creating a string of items seperated by ";" then making that string the new row source for cmb2. Use the click event of the cmb1 combo box each time the user clicks cmb1 to append values to the string. Then replacing the cmb2 rowsource with that string of items.

Oh and if you do this don't forget to change the row source type to values.

Thanks for the response... not exactly sure what you mean by the last part, in regards to the appending the values to the string?

 

[mumbles10]

I figured it out... I created a new form and re did the operation and now it works... weird.

 

[prley]

glad to hear you got it to work. to clarify

mystring = "item1"
user clicks the comobox and selects item2 then
mystring = mystring & ";" & comobox

syntax might not be 100% but that's the idea.

mystring is now equal to "item1;item2"