Subforms, dropdown box and synchronisation (1 Viewer)

[majhl]

Hello All,

I have a form-subform situation. The subform, which is in datasheet view, contains two dropdown boxes, Country and District (1-n). I want the district dropdown to only display records based on what's selected in the country dropdown. There will only ever be any content in District if one country is selected (let's say country 'A').

I've used the code below (one of many attempts!) to try to achieve this, but it doesn't seem to have the effect I want. It seems that if I select country 'A' from the country dropdown, the District rowsource is as expected for that particular record.

However, the rowsource for every other District in the list of records in the subform also now has the same rowsource even where the country <> A.

Similarly, if I select <>A from Country, the rowsource fro every district is now empty, even if the country selected in another row is A.

I'm sure that this is problem that has been accounted before. Is there a workaround?

Thanks for any help.

[VBA]

Dim strSQL As String

strSQL = "SELECT DistrictID, District, DistrictOrder "
strSQL = strSQL & "FROM tblDistrict "
strSQL = strSQL & "WHERE CountryID = " & Me.cmbCountryID & " "
strSQL = strSQL & "ORDER BY DistrictOrder"

Me.cmbDisctrictID.RowSource = strSQL

[/VBA]

 

[CJ_London]

I presume you have that code in your cmbCountryID afterupdate event?

Try putting it in your cmbDisctrictID got focus event instead

and in your cmbDisctrictID lost focus event set the rowsource without the filter and sort i.e.

strSQL = "SELECT DistrictID, District, DistrictOrder "
strSQL = strSQL & "FROM tblDistrict"
Me.cmbDisctrictID.RowSource = strSQL

you may also want to call the lost focus event from the form open event to ensure it is set up correctly for the initial display of the form

 

[majhl]

I presume you have that code in your cmbCountryID afterupdate event?

Try putting it in your cmbDisctrictID got focus event instead

and in your cmbDisctrictID lost focus event set the rowsource without the filter and sort i.e.

strSQL = "SELECT DistrictID, District, DistrictOrder "
strSQL = strSQL & "FROM tblDistrict"
Me.cmbDisctrictID.RowSource = strSQL

you may also want to call the lost focus event from the form open event to ensure it is set up correctly for the initial display of the form

Hello CJ_London,

That appears to work beautifully!

Just one minor question. When you say to call the code from the open event of the form, do you mean the subform and not the main form?

Thanks again.

 

[CJ_London]

You need to put it in the subform open event

 

[majhl]

You need to put it in the subform open event

Yep - thanks.