RE: Form help

Tech-Archive recommends: Fix windows errors by optimizing your registry

For purposes of discussion I'll use these table names and column names:

tbl_City
city_id - long
city_name - text

tbl_ZIP
zip_code text
link_city_id long

cbo_City
at design-time define the combo's row source as
row source = "SELECT city_id, city_name, FROM tbl_City ORDER BY city_name

cbl_ZIP
at design-time define the combo's row source as
row source = "SELECT * from tbl_ZIP WHERE 1 = 2"

Note: This makes the source syntactically correct, but empty.
This will keep Access happy, but there won't be any zip codes
in the combo box until the user selects a city.



Set the bound column to your bound data as needed. I'd only do that after
you make sure that the interconnection of the combo boxes works the way that
you want


Private Sub cbo_City_Click()
Dim long_City_ID as Long
Dim SQL As String

If Not IsNull(Me.cbo_City) Then
' reload the cbo_ZIP with just zips for this city
long_City_ID = Me.cbo_City.Column(0)
SQL = "SELECT * FROM tbl_ZIP " & _
"WHERE ( [link_city_id] = " & str(long_City_ID) & ") " & _
"ORDER BY zip_code"
Me.cbo_ZIP.rowsource = SQL
Me.cbo_ZIP.dropdown
Else
' empty the combo box
Me.cbo_ZIP = Null
SQL = "SELECT * FROM tbl_ZIP WHERE 1 = 2"
Me.cbo_ZIP.rowsource = SQL
End If
End If


"Tom" wrote:

I am using Access 2003. I am trying to make a form where the zipcode combobox
is automatically populated with the zipcodes available in the city chosen
from the city combo box on the form.


I have a table called city with the cityid field and a field called cityname.
I have a table called zip with a field called cityid, a field called
cityname, and a field called zipid. The cityid is the city of origin and the
zipname is the actual zipcode such as 90120.

I created a form called my form and i placed a combo box on it pointing back
to the city table . I indexed on city name and when I was finished I right
clicked the field and named it cityname

I created a second combobox based on the zip table and I moved all three
fields over and I dragged the edge over to the left so the cityid field
wasn't visible. I right clicked it and named it zipname.

In the row source I clicked the ellipses and at the query grid I had 3
columns: zipid, cityid and zipname

under cityid I typed in the following criteria; [Forms]![my form]![cityname]

The form works perfectly. Any city I choose in the city box, the zipcodes
for that city are displayed. All is well until I try to import those fields
into a working form.

I created another table called main and I used the fields,
firstname,lastname,address,cityname,state,zipname

I made a form based on this table and then deleted the cityname and zipname
text boxes. I replaced them with comboboxes based on the zip and city tables.
I adjusted the fields as described above and changed the references to
reflect the form name as main . I told the combo boxes to store their data in
the zipname field and cityname fields and i changed the combo box names
accordingly on the form.


I reset the tab order and was ready to use my new form. My first problem was
that it passed the cityid number back to the table and the zipid number
back to the table instead of the actual name of the city or the actual
zipcode. I changed the bound column and rectified that problem. I also had
difficulty getting it to display the zipcodes in the drop down box instead of
the zipid. I rectified that by changing column widths. However now the
zipcode box contents are no now longer controlled by the city chosen above in
the cityname combobox. instead I get a list of all zipcodes in the table
which defeats the purpose.

can anyone offer any solutions. I want a form where the city I choose will
determine the zipcodes I have to choose from and have that information passed
back to the main table. It should be possible and I am close but this is so
frustrating. Please help if you can. email me sccaafries@xxxxxxxxxxx
.

Relevant Pages

  • Re: Replacing values based on a set list
    ... CityID over the CityName. ... >I have an Excel workbook with two worksheets: ... >same city. ...
    (microsoft.public.excel.misc)
  • Can a field in a table get its value by referencing another table?
    ... I have one table that has all the zipcodes for colorado and their associated ... contractors work and their associated city. ... (I.E. one cities official name is Littleton but half of littleton is referred ...
    (microsoft.public.access.queries)
  • Form help
    ... from the city combo box on the form. ... I have a table called city with the cityid field and a field called cityname. ... I created a second combobox based on the zip table and I moved all three ... Any city I choose in the city box, the zipcodes ...
    (microsoft.public.access.forms)
  • RE: Form help
    ... a 'foreign key' rther than a primary key, then it must be a long to match. ... if city 101 is New ... I have a table called city with the cityid field and a field called cityname. ... Any city I choose in the city box, the zipcodes ...
    (microsoft.public.access.forms)
  • RE: Form help
    ... 'reload the cbo_zip with just zips for this city ... I have a table called city with the cityid field and a field called cityname. ... Any city I choose in the city box, the zipcodes ...
    (microsoft.public.access.forms)