Why can't I change data programmatically?

[Ömer Ayzan]

Dear friends,
I have two combo boxes on a bound form, and set the rowsource of the second
one thru vb code using the value of the first one in the where condition.
Recordset type is updatable snapshot. First combo is bound to the countryID,
and the second one to the CityID. Countries & cities are on different
tables. Whenever a country is changed second combo shows only the cities
that belong to the country selected. If however user clears the country
combo then I'd like to clear the city combo as well. Even though the code
below clears the city combo and combo is bound to the CityID field on the
table on the table CityID remains unchanged. Any idea why?

Private Sub cboCountryCode_AfterUpdate()
CityUpdate
End Sub

Private Sub CityUpdate()
Dim lngCountryID As Long
Dim strSQL As String

lngCountryID = Nz(cboCountryCode, 0)
If lngCountryID = 0 Then
cboCityCode = Null
End If

' Update city
strSQL = "SELECT CityID, CityCode, CityName FROM AYZ_TB_CITY WHERE
CountryID = " & lngCountryID & _
" ORDER BY CityCode"
With cboCityCode
.Enabled = True
.RowSource = strSQL
.Requery
End With
End Sub

This code works ok but if

 

[Sylvain Lafontaine]

Changing the RowSource has no effect on the value of the ControlSource. The
only thing that may happens is that the combobox will display a blank space
if the value of the ControlSource is not part of the list of values of the
RowSource.

You must put code to also change the value of the ControlSource in the
AfterUpdate event of the control or in the BeforeUpdate event of the form.

Also, changing the RowSource of a control automatically performs a Requery,
so you don't have to make a Requery right after that.