By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

automatically assign a value based on another value

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.