Cascading combo

[byulogia@gmail.com]

I have a table with lists of Provinces (22 ) Districts (89) - each province have more than 1 district.

I have 2 combo box (Province first and District second).
I want the District combo to show only the list of districts that are in province selected in first combo. Below is my simple code: but it is not working.

Private Sub cboProvince_AfterUpdate()


Dim strSource As String
strSource = "SELECT Districts " & _
"FROM All " & _
"WHERE Provinces = '" & Me.cboProvince & "' ORDER BY Districts"
End Sub

PLEASE HELP

[Minty]

You are not setting the next combo to the source string you are setting up. Assuming your other combo is called cmbDistricts something like;

Private Sub cboProvince_AfterUpdate()
    Dim strSource As String
    strSource = "SELECT Districts " & _
    "FROM All " & _
    "WHERE Provinces = '" & Me.cboProvince & "' ORDER BY Districts"
    
   me.cmbDistricts.rowsource = strSource

End Sub

[mike60smart]

Hi

From your screenshot it looks like you have both Provinces and Districts in the same table?

I would suggest you need 1 Table for the Provinces and then a 2nd table to list all District associations.

You can then use Cascading Combobox's as they are designed to be used.

[ssanfu]

It is OK to have the Province and District in the same table.

1) The row source for the combo box "cboProvince" would be

SELECT DISTINCT Province FROM All ORDER BY Province

2) The row source for the combo box "cboDistrict" would be

SELECT Districts FROM All WHERE Provinces = '" & Forms!YourFormName.cboProvince & "' ORDER BY Districts

(Change YourFormName"" to your form name)


3) Then you would need to add code in the after update event of "cboProvince"

Private Sub cboProvince_AfterUpdate()
    Me.cboDistrict.Requery
End Sub

I prefer this method rather than always changing the combo box row source.

[byulogia@gmail.com]

When I try the form, it says synthax error from FROM clause.

I dont know, please tip mr

[byulogia@gmail.com]

Hi sir
I tried in but it says, synthax error from FROM clause

[ssanfu]

I don't know your table names nor the field names or control names.

It would be helpful if you would post your dB...

[byulogia@gmail.com]

I don't know your table names nor the field names or control names.

It would be helpful if you would post your dB...

Table Name ALL
Field Names (Province , Districts)

[ssanfu]

Oops, I had a brain freeze. The reference to the combo box "cboProvince"was wrong


The row source for the combo box "cboDistrict" would be

SELECT Districts FROM All WHERE Provinces = '" & Forms!YourFormName.cboProvince & "' ORDER BY Districts

Change "YourFormName" to the name of your form.


If your form name is "Form2", you would use "Forms!Form2.cboProvince"
If your form name is "frmProvinceDistrict", you would use "Forms!frmProvinceDistrict.cboProvince"




I edited my previous post to correct my eerror...


(The attached dB is my example of cascading combo boxes)

[mike60smart]

Hi Steve

Can you tell me which unique ID you would be saving using this method?

[ssanfu]

Can you tell me which unique ID you would be saving using this method?

Not saving an unique ID. Saving the text selection in the combo box to a text field.

In this case, looking at the code in the OP's first post, it appears that the Province and District combo boxes are saving the respective selected text names.
Not sure what the table design looks like. So I answered the OP's question based on my perception of the table design.



Would I save the actual text? No, I design my tables to have an autonumber PK field and a numeric linking FK fields. And thus would need two table as indicated in your posts.