looking up zipcode in table and inserting it in a field

[Guest]

I have what I think is a simple problem, that seems to be turning into a
complex programming endeavor.

I have a two column table named Zip: zipcode and city
NOTE TO WISENHEIMERS-- yes, I do know that cities have multiple zipcodes,
but not the cities I'm using

Anyway, on my form, I have a drop down combo box with city names (derived
from the Zip tables). I have another field on my form called zipcodes (bound
to the zipcode field in another table, my main data table).

When the user selects the city, I would like Access to find the zipcode in
the Zip table that corresponds to that city , and then insert that zipcode
into the zipcode filed in main data table. I assume once that is done, the
zipcode will also appear in the zipcodes field on my form (as that field is
bound to the underlying table).

Any help would be appreciated. I was able to construct a query that
returned the correct zipcode, but have no idea how place that single piece of
date generated by the query into the correct field and record of the table.

Thanks in advance
Steve

 

[pietlinden]

I have what I think is a simple problem, that seems to be turning into a
complex programming endeavor.

I have a two column table named Zip: zipcode and city
NOTE TO WISENHEIMERS-- yes, I do know that cities have multiple zipcodes,
but not the cities I'm using

Anyway, on my form, I have a drop down combo box with city names (derived
from the Zip tables). I have another field on my form called zipcodes (bound
to the zipcode field in another table, my main data table).

When the user selects the city, I would like Access to find the zipcode in
the Zip table that corresponds to that city , and then insert that zipcode
into the zipcode filed in main data table. I assume once that is done, the
zipcode will also appear in the zipcodes field on my form (as that field is
bound to the underlying table).

Any help would be appreciated. I was able to construct a query that
returned the correct zipcode, but have no idea how place that single piece of
date generated by the query into the correct field and record of the table.

Thanks in advance
Steve

make the combobox have two columns, one with City, one with Zip Code.
then set the rowsource for ZipCode to
=Me.Controls("cboCity").Column(1)

no need for lookups at all. You might want to populate the comobox
only after the user has entered a few characters... unless your list
is very small already.

 

[Guest]

That doesn't seem to work at all....
i have one combo box that displays the names of the cities.

are you telling me that the zipcode field control has to be another combobox
that just displays the zipcode column? How would selecting a city from one
combo box automatically make the corresponding zipcode appear in the other
control? Also, if I do get it to appear, how do I then insert it in the
underlying main data table.. that zipcode combo box can't be attached to both
the zipcode table and the main data table at the same time, can it?

also, when I use your recordsource parameters I get an error that the record
source specified on the form does not exist.

 

[John W. Vinson]

I have a two column table named Zip: zipcode and city
NOTE TO WISENHEIMERS-- yes, I do know that cities have multiple zipcodes,
but not the cities I'm using

And never will, over the entire use of your database? Good luck...

Anyway, on my form, I have a drop down combo box with city names (derived
from the Zip tables). I have another field on my form called zipcodes (bound
to the zipcode field in another table, my main data table).

If each city in your Zip table has one and only one zipcode, simply include
the Zip in the Combo's row source; for instance use

SELECT DISTINCT City, Zip
FROM zipcodes
ORDER BY City;

Set the combo's Column Count to 2 and ColumnWidth to

1.5;0

to conceal the zip code.

In its AfterUpdate event put

Private Sub comboboxname_AfterUpdate()
Me!txtZipcode = Me.comboboxname.Column(1)
End Sub

to push the second column (it's zero based) from the combo into the textbox.

John W. Vinson [MVP]

 

[Guest]

Thanks, but after the update, I get the error: "The expression After Update
you entered as the event property setting produced the following
error:User-defined type not defined"

I have no idea what that means. The names of the combo box and the text box
(for the zipcode) in the AfterUpdate event subrouting match the names of the
controls on the form. By the way, do I bind the textbox control that should
contain the zipcode to a field in my main data table so it is stored there?

 

[John W. Vinson]

Thanks, but after the update, I get the error: "The expression After Update
you entered as the event property setting produced the following
error:User-defined type not defined"

Sounds like you copied the code directly onto the AfterEvent line. That wasn't
my intention (sorry if I was confusing) - delete the code from there, and
click the ... icon next to the After Update event line. Choose Code Builder.
Access will give you a Sub and End Sub line; edit it to match my code, using
your controlnames of course.

I have no idea what that means. The names of the combo box and the text box
(for the zipcode) in the AfterUpdate event subrouting match the names of the
controls on the form. By the way, do I bind the textbox control that should
contain the zipcode to a field in my main data table so it is stored there?

Yes.

John W. Vinson [MVP]

 

[Guest]

I did enter it as code previously and I still keep getting the error that the
User-defined type not defined.
I tried your example on a new, blank form with just the two controls and it
works fine, but when I try to replicate it on my existing form I get this
error.

 

[John W. Vinson]

I did enter it as code previously and I still keep getting the error that the
User-defined type not defined.
I tried your example on a new, blank form with just the two controls and it
works fine, but when I try to replicate it on my existing form I get this
error.

Please copy and paste the actual code you're using, along with the name of the
form and of the relevant controls.

John W. Vinson [MVP]

 

[Guest]

Please copy and paste the actual code you're using, along with the name of the
form and of the relevant controls.

John W. Vinson [MVP]

 

[Guest]

Name of Form: Main
Name of Combobox with cities:Combo192
Name of unbound textbox I want to insert zipcodes in: zipcode1

Code placed in "AfterUpdate" event of Combo192 (Event Procedure)

--------------------------
Private Sub Combo192_AfterUpdate()
Me!zipcode1 = Me.Combo192.Column(1)
End Sub
--------------------------
Note, when I first click on the button with ..., an error window titled
"SmartVBA" opens advising me "Missing MS Scripting Runtime in system
directory"..Then another tell me "Can not read file e:\SmartVBA\license.dat",
then another saying "Wrong license file. Please reinstall the program".
Finally the Visual Basic Code window opens.
I reinstalled Access 2007, but that didn't solve this problem. I don't
know what other program I might have to reinstall.

 

[John W. Vinson]

Name of Form: Main
Name of Combobox with cities:Combo192
Name of unbound textbox I want to insert zipcodes in: zipcode1

Code placed in "AfterUpdate" event of Combo192 (Event Procedure)

--------------------------
Private Sub Combo192_AfterUpdate()
Me!zipcode1 = Me.Combo192.Column(1)
End Sub
--------------------------
Note, when I first click on the button with ..., an error window titled
"SmartVBA" opens advising me "Missing MS Scripting Runtime in system
directory"..Then another tell me "Can not read file e:\SmartVBA\license.dat",
then another saying "Wrong license file. Please reinstall the program".
Finally the Visual Basic Code window opens.
I reinstalled Access 2007, but that didn't solve this problem. I don't
know what other program I might have to reinstall.

I've never heard of "SmartVBA" - might it be some sort of add-in, not part of
Access? I don't have 2007 installed so I can't debug. Try opening VBA and
select Tools... References; if SmartVBA (or something resembling it) is
checked, try unchecking it, going back to the VBA window, and compiling the
database.

As for your code, bear in mind that the Column() property is zero based, so
Column(1) means the *second* column in the combo's row source. If the combo
only has one column you'll get this error. Check its ColumnCount property (2
or more) and its RowSource (the second field in the query should be the
zipcode).

John W. Vinson [MVP]

 

[Guest]

Ah, both problems solved.
First, I found smartvba and uninstalled it.
Then your magic 3 words helped: "Compiling the database" which I have never
done before. So I did that, and everything works now. Thanks a million