automatically filling a field with referenced info from another table

[AndyTilia]

Hello,

I'm working on a form--a portion of which has two fields:

City:
State:

I'd like to write some VB code that would automatically fill the state field when the user enters a common city in the city field. I have a separate table with the same two fields and records like this:
1 Boston MA
2 Springfield MA
3 New York NY

etc. I'd like to use that table as the reference (the brains) driving the auto state completion task.

I've been using BeforeUpdate and If statements, but I'd like the flexibility of being able to add cities and states regularly in the table rather than going to the code each time.

Thanks for any help!!!

Andy

 

[Craig0201]

Andy,

Code goes something like this:

Private Sub City_BeforeUpdate(Cancel As Integer)

Dim myDB As Database
Dim CityStateRS As Recordset
Dim strCity As String
Dim strState As String

Set myDB = CurrentDb
Set CityStateRS = myDB.OpenRecordset("Select * from tblCity where city =[forms]![form1]![city]&quot
strCity = Forms!form1!City

With CityStateRS
Do
If !City = strCity Then
strState = !State
Forms!form1!State = strState
End If
.MoveNext
Loop While strState = "" And Not .EOF
End With
End Sub

Replace form1 with the name of your form, tblCity with the name of the table holding the cities & states.

Not bad for my first ever DAO code!!!

Craig

 

[Craig0201]

Oops! When inputting the SQL statement, miss out the WHERE clause.

Craig