populate form field

[Brian]

I have a form field that I would like to serve as the criteria for a text box
on the same form. Basically, I store addresses with just the zip code and
have a seperate table with city, state and zips.
I understand both the pros and cons of this arrangement but it works for me.
I would like to display only the city name based on the zip code listed or
entered in the form field.
I have a query that will display a concantonated city and state for each zip
code and have the criteria set as:
[Forms]![tbladdr]![cityStateZip_idZip]
If I put a text box with =[qryCityStateZip]![ExprCityState] I get a #Name
error
What isthe proper way to accomplish this

 

[Jeff Boyce]

Brian

That error typically means Access can't find somthing with that (spelling of
that) name in what it's working on.

In your situation, I suspect it's because your text box's Control Source is
trying to peek inside a query, something that I believe a Control Source
can't do.

Try using the DLookup() function against that query instead...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Arvin Meyer [MVP]]

Perhaps you don't fully understand the ramifications of this. For instance, I
live in a zip code which encompasses 2 different cities. It isn't as unusual
as it may seem.

That said, if your data is in separate fields (as it should be) you merely
need to use a combo box to display the city, and have the state and zip code
in other columns. To fill additional text boxes, refer to the columns in your
combo, using their zero-based index, like (aircode):

Private Sub cboCity_AfterUpdate()
Me.txtState = Me.cboCity.Column(1) ' 2nd column
Me.txtZip = Me.cboCity.Column(2)
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Brian]

Well, my original intent was to display the city listed for the zip code, I
din't plan on storing the city in the table data, however you do have a
point. So, since I am designing a new database, I can make whatever changes
necessary, does this sound more appropriate
make tblCustomer contain address info including city, state and zipcode,
rather than just zipcode. The form used to populate this table, I want to
enter in the zip code and the city(ies) associated with that zip code
populates either a a form field, which can be edited or a combo box to select
the proper city - wait, come to think of it, the zip codes are the PK in the
zip code table, so only one city will always show up anyway, but if I
populate it in an editable field, like a suggestion, on the form and store it
in the table as the city name, it should be accurate when I use the table
data for displaying customer information.
With that in mind, how would I then have the city name appear in a text box

Perhaps you don't fully understand the ramifications of this. For instance, I
live in a zip code which encompasses 2 different cities. It isn't as unusual
as it may seem.

That said, if your data is in separate fields (as it should be) you merely
need to use a combo box to display the city, and have the state and zip code
in other columns. To fill additional text boxes, refer to the columns in your
combo, using their zero-based index, like (aircode):

Private Sub cboCity_AfterUpdate()
Me.txtState = Me.cboCity.Column(1) ' 2nd column
Me.txtZip = Me.cboCity.Column(2)
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I have a form field that I would like to serve as the criteria for a text box
on the same form. Basically, I store addresses with just the zip code and
have a seperate table with city, state and zips.
I understand both the pros and cons of this arrangement but it works for me.
I would like to display only the city name based on the zip code listed or
entered in the form field.
I have a query that will display a concantonated city and state for each zip
code and have the criteria set as:
[Forms]![tbladdr]![cityStateZip_idZip]
If I put a text box with =[qryCityStateZip]![ExprCityState] I get a #Name
error
What isthe proper way to accomplish this