Form help

[Tom]

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 (e-mail address removed)

 

[NKTower]

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

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 (e-mail address removed)

 

[Tom]

Thank you so much for the help. I have just a few more questions. First of
all in your reply you said create the table called tbl_city which I did. You
said city_id-long . Is the field name city_id and you want the field type to
be number set to long integer? or is the field to be named city_id_long.

Same question in tbl_zip
link_city_id long is that link_city_id as the table name and number as the
type with long integer?

I assume the code in the private sub(cbo_city_click() is to be in the on
click event in the city combo box correct?
I am having some trouble with the code especially in this section:
SQL= "Select * from tbl_zip " &_
"Where ([linkl_city_id]=" & str(long_city_id0 & ") "&_
"Order by zip_code"

I keep getting red underline errors when I come to the end of the line and
try to use the &_ is that an underscore character?

Here is my whole code maybe you can see where I went wrong

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 ([linkl_city_id]=" & str(long_city_id0 & ") "&_
"Order by zip_code"
Me.cbo_zip_Label.rowsouyrce = sql
Me.cbo_City.zip.Dropdown
Else
'empty the combo box'
Me.cbo_zip_Label = Null
sql = "Select * from tbl_zip where 1=2"
Me.cbo_zip_Label.RowSource = sql
End If
End If
End Sub


I want the info stored in a table called tbl_main so I used the wizard when
creating the cbo boxes and told it to store the info in the appropriate
fields in the main table and then created the cbo boxes on the main form.

I apologize for asking these questions but i really need to get this
technique down and your help is greatly appreciated


Tom

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

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 (e-mail address removed)

 

[Tom]

also are there any primary key fields in the city table or zip table? If it
isn't too much to ask maybe you could send a sample database. I could see how
your code worked and where it was placed. E-mail:[email protected]

Thanks again. You obviously know what you are doing!
Tom

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

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 (e-mail address removed)

 

[NKTower]

Hi Tom -

a) Not knowing what kind of data item you were using, I just guessed that
the code would be a number of type long. If a record's primary key is
AutoNumber, i.e. where Access just starts with 1 and bumps by 1, that is
really a special case of a long. If another table is going to reference it
as in a table lookup, then the value in the other table (where it is known as
a 'foreign key' rther than a primary key, then it must be a long to match.
Foreign keys may have duplicate values. For example, if city 101 is New
York, there may be 300 or so ZIP codes records that have the foreign key of
101 as the pointer to the New York City zip code.
b) You have a very simple fix needed for your VBA code. Here are some rules:
1) to concatenate two strings (i.e. create a single string from two
strings, you connect them with a space, the ampersand character, and another
space. Unfortunately the forum doesn't use fixed font, so the spaces didn't
show. So you need to add spaces to either side of the ampersand character
(&) in those lines.
You may also use " + " to concatenate strings.
2) To continue a line of code from one to the other, you need a space and
the underline character. (The space may be the same one that follows the
ampersand.)

c) If you are using a field in a table as a foreign key it is good practice
to make the field in the record that it goes to for a lookup to have the link
a primary key in the lookup table. So in my example, it would be best to
have city_id be a primary key. Things get a little strange in the real
world of ZIP codes. There are some zip codes that serve more than one
politically defined community, for example not too far from me is a single
ZIP code that supports two differently-named communities. So if you were to
do a lookup on that ZIP code if that were your primary key to the ZIP code
table you might have a problem. This is rare and you might be able to ignore
it.

also are there any primary key fields in the city table or zip table? If it
isn't too much to ask maybe you could send a sample database. I could see how
your code worked and where it was placed. E-mail:[email protected]

Thanks again. You obviously know what you are doing!
Tom

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

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 (e-mail address removed)