automatic field population

  • Thread starter Thread starter Lauren B
  • Start date Start date
L

Lauren B

I have my formed devised so that when a zip code is entered, the city,
state, and county are automatically populated. This is based on a query I
derived from a large table containing all US zip codes. My 'after update'
code for the Zip Code field reads as follows:

Private Sub ZipCode_AfterUpdate()
P_City = Me.ZipCode.Column(1)
P_State = Me.ZipCode.Column(2)
P_County = Me.ZipCode.Column(3)
End Sub

If I go into an existing record, this function works fine; however, if I
enter a new record, it does not. After entering a zip code in a new record,
the other fields remain blank. How can I get this to work on new form
entries?

Any assistance is appreciated.

LB
 
Hi, Lauren.

If the user is selecting a ZipCode from a combo box list, the behavior
should be the same for an existing and a new record. Is it possible that you
have the Limit to List property set to No, and that the user has entered a
Zip Code not in your list? If so, you should set the Limit to List property
to Yes, and devise an event procedure to allow your users to add a new record
to your ZipCode table if necessary. A Google search on Limit to List should
get you code that you can modify for this purpose.


If you're using an AfterUpdate event to assign values, I can only assume
that you are assigning them to underlying fields in your table. This is a
redundant and error-prone approach. You do not need these fields in your
table, you only need the foreign key to table that contains them. You can
then produce any field you need from this table by linking your table to the
ZipCode table by the foreign key.

If you need merely to display these values in unbound textboxes on your
form, simply set the Control Source of each to the appropriate Column
property expression, e.g.:

= Me.ZipCode.Column(1)

Hope that helps.
Sprinks
 
Back
Top