Setting Controls from a Table in Access 97

[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.

[xzzy]

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.

[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.

[xzzy]

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.

[xzzy]

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.

[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.