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

Setting Controls from a Table in Access 97

P: n/a
Bob
I have a form with controls City and Zip Code, and a Table containing Zip Code
with the corresponding City. I would like the user to just enter Zip Code,
either by typing or with a combo box; then have the city set automatically.
Any advice on how to do this would be appreciated.

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
combobox example:

the columns in the combo would be:

zip
city
state

to get the city and state of the selected zip do this onexit, ondblclick or
whenever you need to set city and state

ctlCITYSTATE = ctlzipcode.column(1) & ", " & ctlzipcode.column(2) ' it's
zero based
code example

dim myset as recordset
set myset = currentdb().openrecordset("select city, state form ZipCodes
where zipcode = '" & ctlzipcode & "'",dbopensnapshot)

if myset.recordcount = 0 then
msgbox ctlzip & " is not on file"
else
ctlcitystate = myset!city & ", " & myset!state"
endif

close, etc.

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"Bob" <Bo*******@att.net> wrote in message
news:kn***********************@bgtnsc04-news.ops.worldnet.att.net...
I have a form with controls City and Zip Code, and a Table containing Zip Code with the corresponding City. I would like the user to just enter Zip Code, either by typing or with a combo box; then have the city set automatically. Any advice on how to do this would be appreciated.

Nov 12 '05 #2

P: n/a
Bob
Thanks for your help. However, when I try your suggestion from the Combo
Box, I get a popup error message:

The value you entered isn't valid for this field.

The City control I'm trying to set is bound to a text field in a table,
which is the same length as the Combo Box City field.. Where did I go
wrong? Thanks for any additional help.
Nov 12 '05 #3

P: n/a
it sounds like you are assigning a string to a number field

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"Bob" <Bo*******@att.net> wrote in message
news:KJ***********************@bgtnsc04-news.ops.worldnet.att.net...
Thanks for your help. However, when I try your suggestion from the Combo
Box, I get a popup error message:

The value you entered isn't valid for this field.

The City control I'm trying to set is bound to a text field in a table,
which is the same length as the Combo Box City field.. Where did I go
wrong? Thanks for any additional help.

Nov 12 '05 #4

P: n/a
oops, didn't finish the reply. or the value being assigned is failing a
validation

Look in:

table: design the table and select the field ( ex CITY ) and then
look at the "Validation Rule" and see if any of the value violate that rule

Form: similar, select the textbox and then in the data tab, look at the
"Validation Rule" and see if any of the values violate that rule, or you may
have code somewhere else that is causing the message such as a validation in
the BeforeUpdate event.
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"xzzy" <mr********@comcast.net> wrote in message
news:3mIEb.402339$Dw6.1250211@attbi_s02...
it sounds like you are assigning a string to a number field

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"Bob" <Bo*******@att.net> wrote in message
news:KJ***********************@bgtnsc04-news.ops.worldnet.att.net...
Thanks for your help. However, when I try your suggestion from the Combo Box, I get a popup error message:

The value you entered isn't valid for this field.

The City control I'm trying to set is bound to a text field in a table,
which is the same length as the Combo Box City field.. Where did I go
wrong? Thanks for any additional help.


Nov 12 '05 #5

P: n/a
Bob
Yes, it does imply that I'm putting text into a number field. I defined the
City control by dragging the City field from the associated Query. The City
field in the table referenced by the query is defined as text and is longer
that the corresponding field in the Combo Box table. Is my problem maybe
associated with having the Query between the table and the Form? The City
field comes from the second table defined in the Query and the fields are
included with the Table* usage. Again, thanks for your help.
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.