form default from a second field

[Arch]

My data base contains a data table and a drop down table. The drop down
table contains 3 fields: ID number, Town-name, and ZIP. To populate the
Town field in the data table, the user selects from the drop down list via a
combo box on a form, and the value goes into Town in the table. I want the
ZIP associated with this town to appear as a default in the ZIP-value box on
the same form. Suggestions? Should the ZIP-value box be text or combo?

 

[John Vinson]

My data base contains a data table and a drop down table. The drop down
table contains 3 fields: ID number, Town-name, and ZIP. To populate the
Town field in the data table, the user selects from the drop down list via a
combo box on a form, and the value goes into Town in the table. I want the
ZIP associated with this town to appear as a default in the ZIP-value box on
the same form. Suggestions? Should the ZIP-value box be text or combo?

In the AfterUpdate event of the combo box ("drop down table" is not a
meaningful term, I presume you have a combo on your Form based on the
Town - Zip table), put code like

Private Sub comboboxname_AfterUpdate()
Me!txtZip = comboboxname.Column(2)
End Sub

The Column property is zero based so this will "push" the third field
in the combo box's Row Source - the Zip - into the textbox named
txtZip. The user can overwrite it if they wish.

One question: most towns over a few thousand population have multiple
Zip codes; how does your "drop down table" deal with these?

John W. Vinson[MVP]

 

[Arch]

John, thanks for you help. I may be almost there. In the form, Town is
entered via a combo box (driven by a "list" table), Zip by a text box. I put
the code you recommended into the AfterUpdate event of the combo, including
".Column(2)". I also changed the Control Source for the Zip text box to the
Town field name. When I select a Town name, what appears in the Zip text box
is the correct line number of the "list" table, not the Zip code itself.

 

[Arch]

I almost forgot about your multiple Zip question. By showing both Town and
Zip in the drop down list for Town, the user can choose the right Zip within
Town. In our little corner of Connecticut, there is happily only one Zip in
most Towns.

 

[John Vinson]

John, thanks for you help. I may be almost there. In the form, Town is
entered via a combo box (driven by a "list" table), Zip by a text box. I put
the code you recommended into the AfterUpdate event of the combo, including
".Column(2)". I also changed the Control Source for the Zip text box to the
Town field name. When I select a Town name, what appears in the Zip text box
is the correct line number of the "list" table, not the Zip code itself.

Well, it's not Column(2) then. The Column property is zero based; (2)
means "retrieve the third field from the combo box's Row Source
query".

Take a look at the Row Source query in datasheet view, and see which
column contains the zip. Subtract one and use that number as the
Column() property.

John W. Vinson[MVP]

 

[Arch]

John, thanks. Your original code works fine, now that I ironed out a few
problems like redefining the Zip field as text and not combo, and formatting
input to include leading zeroes. In general, I am having trouble getting a
grip on how to address cells in Access in a manner analogous to arrays in
Basic or Fortran, to use them for defaults. Slowly it coming, however,
thanks in part to your help.

 

[John Vinson]

In general, I am having trouble getting a
grip on how to address cells in Access in a manner analogous to arrays in
Basic or Fortran, to use them for defaults.

Well, since Access doesn't use "cells", I can see why you might be
having trouble using them... <g>

Tables *are not arrays*, and thinking of them as if they were will
just cause confusion.


John W. Vinson[MVP]