#NAME? Error on Form

[Rob B.]

I want some form fields to automatically populate with data once another
field on the form is filled in. Specifically, I want a city and state to
fill in automatically once a zip code is entered.

My table tblZipCodes has 3 fields: fldZip, fldCity, and fldState

My query qryZipCodes has an expression, expCitySt, which combines fldCity
and fldState, which looks like, expCitySt: [fldCity] & ", " & [fldState].
The expression produces the desired result in the query

On the form when I enter a valid Zip Code from tblZipCodes, the field that
points to qryZipCodes.expCitySt, just returns #NAME?.

Ideas?


Rob B.

 

[jahoobob via AccessMonster.com]

From Access Help:

Make sure that the field specified in the control's ControlSource property
hasn't been removed from the underlying table or record source (record source:
The underlying source of data for a form, report, or data access page. In an
Access database, it could be a table, query, or SQL statement. In an Access
project, it could be a table, view, SQL statement, or stored procedure.).
Check the spelling of the field name in the control's ControlSource property.

If you specified an expression (expression: Any combination of mathematical
or logical operators, constants, functions, and names of fields, controls,
and properties that evaluates to a single value. Expressions can perform
calculations, manipulate characters, or test data.) in the control's
ControlSource property, make sure that there is an equal sign preceding the
expression.

I want some form fields to automatically populate with data once another
field on the form is filled in. Specifically, I want a city and state to
fill in automatically once a zip code is entered.

My table tblZipCodes has 3 fields: fldZip, fldCity, and fldState

My query qryZipCodes has an expression, expCitySt, which combines fldCity
and fldState, which looks like, expCitySt: [fldCity] & ", " & [fldState].
The expression produces the desired result in the query

On the form when I enter a valid Zip Code from tblZipCodes, the field that
points to qryZipCodes.expCitySt, just returns #NAME?.

Ideas?

Rob B.

 

[Arvin Meyer [MVP]]

The easiest way is to use a combo box to enter the Zip code. Add the other 2
columns to the query or select statement which is the row source for the
combo, then use the AfterUpdate event of the combo to "push" the data into
the 2 other text boxes. The field index for a combo is a 0 based array so:

Sub cboMyCombo_AfterUpdate()
Me.fldCity = Me.cboMyCombo.Column(1)
Me.fldState = Me.cboMyCombo.Column(2)
End Sub