automatically assign a value based on another value

[TRAX]

Here's my problem:
I've got a table cities with the following tables: zipcode, city, province
and table provinces: a table with: province_id, provincename
cities.province and province.province_id are linked.
Is there anay way to automatically fill in the cities.province value based
on the cities.zipcode value?
For example: if the value in zipcode is between 1000 and 2000, then the db
should fill in "1" in province?

Thanks in advance

--�
stijn.verspeet@pandora[delete.this].be

[Salad]

TRAX wrote:

Here's my problem:
I've got a table cities with the following tables: zipcode, city, province
and table provinces: a table with: province_id, provincename
cities.province and province.province_id are linked.
Is there anay way to automatically fill in the cities.province value based
on the cities.zipcode value?
For example: if the value in zipcode is between 1000 and 2000, then the db
should fill in "1" in province?

Thanks in advance

--�
stijn.verspeet@pandora[delete.this].be

If a small list, you could use Select Case
Select case zipcoce
Case 1000 to 2000
Me.Province = 1
Case 2001 to 3000
Me.provice = 2
Case Else
Me.Provice = 3
End Select

I'm not sure how many tables you have. However, if you can somehow link the
proviince id to the zipcode, you can use Dlookup() as one method. Or use a
recordset.

Me.Province = Dlookup("ProvinceID","ProvinceTable","your filter to find
province)

or
DIm strSQL As String
Dim rst As Recordset
strSql = "Select ProviceID from ProvinceTable Where your filter
condition...."
Set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
If rst.RecordCount > 0 then Me.ProviceID = rst!ProviceID
rst.close
set rst = Nothing

You can place any of these in the AfterUpdate event of the zipcode.