Coding Problem in Combo Box

[G deady via AccessMonster.com]

I have 2 cascading combo boxes. Thr first calls all 50 states in the US.(I am
using States as the parent combo box instead of Zipcodes as many do because I
need to break down my zipcodes into maneable numbers. There are 47,000 of
them) The Second is "Supposed" to called the call all the cites and zipcodes
for the state selected. I have a problem with my WHERE clause. Without the
WHERE Clause Combo2 works fine. It shows every record in the table. When I
add the WHERE Clause It won't run. I am asked for a parameters for each field
in the second combobox.
Table1 has 2 fields-StAbb(PK) and State. Table2 has 5 fields-ZipID,ZipCode,
City, & StAbb(FK).
I have a query for each table that the combo boxes run off.

The rowsource for combo1 is
"SELECT tbl_states.StAbb, tbl_states.State FROM tbl_states ORDER BY
tbl_states.StAbb;"

The rowsource for Combo2 is:
"SELECT qry_zipcodes.ZipID, qry_zipcodes.City, qry_zipcodes.ZipCode,
qry_zipcodes.StID FROM qry_zipcodes WHERE qry_zipcodes.StAbb=Forms!
frm_clirntinformation!combo24 ORDER BY [City], [ZipCode];

It doesn't matter whether I run them off the querys or directly off the
underlying tables. The same thing happens. Can anyone tell me what I am doing
wrong?

 

[G deady via AccessMonster.com]

I corrected the two typos in the code. (cliRntinformation fixed to
cliEntinformation) and (qry_zipcodes.StAbb fixed to qry_zipcodes.StID FROM
qry_zipcodes WHERE qry_zipcodes.StID) That got rid of the parameter request
but now I get no records when I run the second combo

 

[Sylvain Lafontaine]

Try with adding a Parameters clause at the beginning of your second query:

PARAMETERS Forms!frm_clirntinformation!combo24 Long;
Select ...

Don't forget the « ; » at the end of the Parameters clauses. Another
solution would be to dynamically build the rowsource each time.

 

[Guest]

I corrected the two typos in the code. (cliRntinformation fixed to
cliEntinformation) and (qry_zipcodes.StAbb fixed to qry_zipcodes.StID FROM
qry_zipcodes WHERE qry_zipcodes.StID) That got rid of the parameter request
but now I get no records when I run the second combo

???????
It really helps if you use the actual names of the objects....

In your first post, you said there are two tables

Table1 (tblStates??) has 2 fields-StAbb(PK) and State.

Table2 (tblZipCodes??) has 5 fields-ZipID,ZipCode,City, & StAbb(FK).
What is the 5th field?

Where did the field "StID" come from? It's not in either field list.

The form name is "frm_cliEntinformation"?

What are the two combo box names?

What is the code (if any) in the first combo box (for the States)
AfterUpdate event?

 

[G deady via AccessMonster.com]

Steve, sorry about that.The first table is tbl_states and has 2 fields :
StAbb(PK) and States. The second table is tbl_zip-city. It has: ZipID
(autonumberPK), StID(FK to StAbb in tbl_states), City and Zip.(My mistake. I
said 5. There are 4.)
The form name is frm_clientinformation. the first combobox is named
"combo24" and the second is "combo26"
The code in the afterupdate event of combo24 is "Me.combo26.Requery"
I think that answers all your questions.

 

[Guest]

Steve, sorry about that.The first table is tbl_states and has 2 fields :
StAbb(PK) and States. The second table is tbl_zip-city. It has: ZipID
(autonumberPK), StID(FK to StAbb in tbl_states), City and Zip.(My mistake. I
said 5. There are 4.)
The form name is frm_clientinformation. the first combobox is named
"combo24" and the second is "combo26"
The code in the afterupdate event of combo24 is "Me.combo26.Requery"
I think that answers all your questions.

OK, here is what I used:

-----------
Query: qry_zipcodes
--
SELECT [tbl_zip-city].ZipID, [tbl_zip-city].StID, [tbl_zip-city].City,
[tbl_zip-city].ZipCode
FROM [tbl_zip-city]
ORDER BY [tbl_zip-city].StID, [tbl_zip-city].City, [tbl_zip-city].ZipCode;
-----------


Combo24
---
Row Source ...........
SELECT tbl_states.StAbb, tbl_states.State FROM tbl_states ORDER BY
tbl_states.StAbb;

Column count .........2
Column widths.......0";1"
Bound column ......1

After Update event:

Private Sub Combo24_AfterUpdate()
Me.Combo26.Requery
End Sub
-----------

Combo26
---
Row Source ...........
SELECT qry_zipcodes.ZipID, qry_zipcodes.City, qry_zipcodes.ZipCode,
qry_zipcodes.StID, [City] & " - " & [ZipCode] AS CityZip FROM qry_zipcodes
WHERE (((qry_zipcodes.StID)=[Forms]![frm_clientinformation].[combo24]))
ORDER BY qry_zipcodes.City, qry_zipcodes.ZipCode;

Column count .........5
Column widths.......0";0";0";0";2"
Bound column ......1
-----------

Paste the SQL for the Combo boxes and set the column count and widths (you
can paste them in also). Should work - it did in my mdb.


Note that I added a calculated field in the row source for Combo26. It will
show both the City and the zip; otherwise you would see only the city or the
zipcode, but not both at the same time.

HTH

 

[G deady via AccessMonster.com]

Steve, I still don't have it but I think that is because I'm not sure where
to put the code

Query: qry_zipcodes
--
SELECT [tbl_zip-city].ZipID, [tbl_zip-city].StID, [tbl_zip-city].City,
[tbl_zip-city].ZipCode
FROM [tbl_zip-city]
ORDER BY [tbl_zip-city].StID, [tbl_zip-city].City, [tbl_zip-city].ZipCode;

You gave me both row sources so I assume this is a sql statement in the query
window but I am not really familiar with them. I'm sorry to be suth a bother.
I think if you can tell me where this code goes I'll have it.

 

[Guest]

Steve, I still don't have it but I think that is because I'm not sure where
to put the code

Query: qry_zipcodes
--
SELECT [tbl_zip-city].ZipID, [tbl_zip-city].StID, [tbl_zip-city].City,
[tbl_zip-city].ZipCode
FROM [tbl_zip-city]
ORDER BY [tbl_zip-city].StID, [tbl_zip-city].City, [tbl_zip-city].ZipCode;

You gave me both row sources so I assume this is a sql statement in the query
window but I am not really familiar with them. I'm sorry to be suth a bother.
I think if you can tell me where this code goes I'll have it.

In your first post, you wrote:

"It doesn't matter whether I run them off the querys or directly off the
underlying tables."

so I posted the query I used that the row source for combo26 was based on
(trying to follow what you were doing).

You can use the table instead of the query for the Combo26 row source. Just
replace "qry_zipcodes" with "tbl_zip-city" (no quotes) for the Combo26 row
source.


Or, create a new query (if you don't have one named qry_zipcodes), close the
dialog that appears and switch to SQL view. Paste in the above SQL and save
the query as "qry_zipcodes".

Then try the combo boxes...