Field lookup function (simple probably)

[Nitesh9999]

I have a table with the following 4 fields (these are the ones i'm having the issue with but there are others).

ID, Country, City, Date

I enter data into this table via a form

The ID is an auto number. The date is simply enetered.

The Country Field is a lookup (in the table itself) with the following lookup properties

Display Control: Combo Box

Row Source Type: Value List

Row Source: "England";"Spain";"France" etc


Now the problem...

I also want a lookup in the City combobox (on the form) which changes to reflect what was selected in the Country field.

E.G

If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.

I was told to use the following code in the afterupadate of "parent combobox":

Private Sub ComboCountry_AfterUpdate()
Select Case Me.ComboCity
Case "Test"
Me.ComboCity.RowSource = "A;B;C"
End Select
End Sub

Ive tried it but as always i get an error when i use the country drop down...

A pop up box with:

Compile error:
Method or data member not found

and visual basic opens with the top line of the code "Private Sub Country_AfterUpdate()" highlighted in yellow, and the ".RowSource =" is highlighted in blue.

These are the properties of my form

My Country Combobox is called - "Country"
My City Combobox is called - "City"

I have put the above code in the after update of the "Country" combobox as below:

Private Sub Country_AfterUpdate()
Select Case Me.City
Case "England"
Me.City.RowSource = "London;Manchester;Leeds"
End Select
End Sub


Anyone know where the error in the code is? Obviously i have reduced the code to include only one case.

Attached is the DB

 

[mohsinhq]

i couldnt have a look at your example as i'm using ACC97 but:

from your question - would it not be easier to use cascading combo boxes? (Search the forum)

then on afetr update of your country combo box:

Private Sub cboCountry_AfterUpdate()

' check whether the Country box has a value
If IsNull(Me.cboCountry) Then
' clear the City Box current value
Me.cboCity = Null
' lock and disable the City Selection Box
Me.cboCity.Enabled = False
Me.cboCity.Locked = True
Else
' unlock and enable the City Selection box
Me.cboCity.Enabled = True
Me.cboCity.Locked = False
' requery cboCity based on the selection in cboType
Me.cboCity.Requery
End If

End Sub

-------------------------------

Searching Cacading Combo Boxes should give you more info on how to link the two together.

Hope this helps

 

[Nitesh9999]

Sorry I must not have been clear. I am using cacading combo's (I think, one boxes values depend on another, in a parent-child format)

 

[Nitesh9999]

Its fine i have it working. If you were wondering what it looks like its attached. Sorry for any confusion.

p.s you can probably see how easy the thing i was trying to explain is!!