Cascading combobox requery problem

Question

Hi guys...

I have a combobox called cboProvinces - based on data from qryProvinces that has a column ProvinceAbbreviation. The combobox displays the ProvinceAbbreviation field in the query:

SELECT Provinces.ProvinceAbreviation

FROM Provinces;

I have a combobox called cboFirstNational that lists the corporate name of each client from the FirstNational table:

SELECT FirstNational.FirstNationalID, FirstNational.CorporateName, FirstNational.Province

FROM FirstNational

WHERE (((FirstNational.Province)=[forms]![AssessmentSessionHeaderNEW]![cboProvinces]))

ORDER BY FirstNational.CorporateName;

I am trying to figure out the requery code needed to update the data in cboCorporateName when cboProvince changes.

This is the code I have tried but it is not working - I am getting a compile error Expected function or variable.  Where am I going wrong here?

Private Sub cboProvinces_AfterUpdate()

Me.cboProvinces = Null

    Me.cboProvinces.Requery

    Me.Requery

Answer 1

Hi,

try with

Private Sub cboProvinces_AfterUpdate()

    cboFirstNational.requery

End Sub

Ciao Mimmo

Answer 2

To protect the integrity of the data, you should also set the cboFirstNationalNational control to Null.  Otherwise, if the user edits an existing record by selecting a different province, the value of the cboFirstNationalNational control will remain unchanged.  Its value will therefore be inconsistent with the selected province.  If the control's BoundColumn is a hidden numeric key, as will usually be the case in these circumstances, the combo box will appear to be empty, disguising the inconsistency.

You correctly did this with your original code, albeit with the wrong combo box.

Answer 3

Thanks Mimmo - it's working, still unsure of why the error was appearing but thanks to you I discovered I was requerying the wrong control.

Thanks again!