RE: Form help
- From: Tom <Tom@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 9 Jun 2008 03:29:00 -0700
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
"NKTower" wrote:
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
- References:
- Form help
- From: Tom
- RE: Form help
- From: NKTower
- Form help
- Prev by Date: nested forms and converting form 2003 to 2000
- Next by Date: RE: Form help
- Previous by thread: RE: Form help
- Next by thread: RE: Form help
- Index(es):
Relevant Pages
|
