Creating an autofill capability

[pavescott13]

Looking for assistance with an Access 2003 database. I am currently trying
to assist in the creation of a database for a non-profit organization that my
wife is volunteering with and I am concerned with errors in entering data.
For the tblvolunteers, I have following standard fills VolunteerNumber
(autoid), strFirstName, strLastName, strAddress, strCity, strState, and
strPostalCode. I have downloaded a table that has the zipcode married up
with the city, state, and areacode. I would like for the user to select
their zipcode on frmVolunteer via a combo-box. Once they do that, I would
like to have the City and State autopopulate (based on the information in
tblZipcode with the actual Zipcode as the primary key)...so that I don't have
a screw up on entry...which their Excel sheets crrently reflect. I have set-
up a relationship between strPostalCode(which I am thinking needs to be an
integer) and the Zipcode table (note that the one I have has the Zipcode as
text). I have other areas that would also benefit from a similar function,
but I am at a loss on how to do it. There was a day when I felt competent at
working with Access...and I have been rereading all of my texts to try to get
up to speed...but I am in a time constraint with this and I am not to proud
to ask for assistance.

 

[Albert D. Kallal]

Assuming you have a combo box based on the folwling sql:

select Zipcode, City, State from tblZipcode

Now, use a wizard to build a combo box on the above 3 columns, and of course
bind that combo box to the zip code field in your main table/form

Then, in the after update event of the combo box, simply go:

me.city = me.comboZip.Column(1)
me.State = me.comboZip.Column(2)

that is it....

The column function is zero based, so 0 = 1st column of the combo box and
that would be the zip code...

 

[Gordon Lincoln]

Albert explained how to acquire the data you desire using a combo box -

One statement you made concerns me = about make the postalcode column an
integer. You definitely want the postalcode or zipcode to be a text/string
value. One of the several problems you would face using an integer is that
the NE USA zips would get truncated to 4 characters due to the leading
zero.

The old school rule on determining when a value that contains numerals is
numeric or text - is that, if you add or subtract from the number - does the
answer make any sense? Would you ordinarily wish to sum this number with
another? Stated another way - if the number represents a 'name' or a 'label'
value, then it's text, if the number represents a quantity then it's
numeric.

So a zipcode, a social security number, a telephone number, etc. are text
values because they function as names, not quantity values.

A bank balance or commission percentage, the population of New York City, or
even calendar dates represent a measurement, a quantity that you may wish to
add to or subtract from to reflect the results of an event when the quantity
changes.

One exception to this general rule is the primary key field where an
auto-generated number in the form of a long integer is often used. The
primary key field is a label/identifier - but the efficiency of using a long
integer in this situation outweighed the shortcomings in the assessment of
the developers.

Hope this helps,
Gordon

 

[pavescott13 via AccessMonster.com]

Thanks...I will give this a try tonight

Assuming you have a combo box based on the folwling sql:

select Zipcode, City, State from tblZipcode

Now, use a wizard to build a combo box on the above 3 columns, and of course
bind that combo box to the zip code field in your main table/form

Then, in the after update event of the combo box, simply go:

me.city = me.comboZip.Column(1)
me.State = me.comboZip.Column(2)

that is it....

The column function is zero based, so 0 = 1st column of the combo box and
that would be the zip code...