Questions on the "classic" filtering comboboxes topic

[Guest]

Working off this page:
http://www.mvps.org/access/forms/frm0028.htm

I thought I had it right, but the second combobox is simply blank no matter
what selection I make in the first.

So, I'm a bit unclear, from that webpage, what code is supposed to be
present in the first combobox's after_update event, and the second combobox's
Control Source, Record Source Type, and Record Source properties. I'm also
curious, is Dev Ashish suggesting two possible ways of doing this on that
page?


Here are my elements:

tblItems
tblItemMats
frmItems (main)
Combo24 (first, linked to tblItems)
Combo36 (second, linked to tblItemMats)

Thanks

 

[Lynn Trapp]

Your first combobox needs to have a recordsource from the tblItems. Your
second Combobox needs to have a recordsource from tblItemMats with a
criteria that filters it based on the first combobox. The After_Update event
of the first combobox needs to requery the second combobox.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Dirk Goldgar]

Working off this page:
http://www.mvps.org/access/forms/frm0028.htm

[...] I'm also curious, is Dev Ashish suggesting two possible
ways of doing this on that page?

Yes. That page shows two ways of doing it: one involving modifying the
second combo box's rowsource at run time, and another involving setting
the row source at design time with a criterion referring to the first
combo box, so all you have to do at run time is requery the second combo
box.

 

[Guest]

Ok, thanks you two, that's a little bit clearer. Here's the code I put in
the second combobox's Record Source field:

SELECT [tblItemMats].[ID], [tblItemMats].Description FROM tblItemMats WHERE
[tblItemMats].[Material]=Combo24;

And the requery in the first combobox After_Update event:

Me!Combo36.Requery

However, now when I select an item in the first combobox, I'm getting a
popup:
"Enter Parameter Value: tblItemMats.Description"

Working off this page:
http://www.mvps.org/access/forms/frm0028.htm

[...] I'm also curious, is Dev Ashish suggesting two possible
ways of doing this on that page?

Yes. That page shows two ways of doing it: one involving modifying the
second combo box's rowsource at run time, and another involving setting
the row source at design time with a criterion referring to the first
combo box, so all you have to do at run time is requery the second combo
box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Ok, never mind the popup concern and sorry, I had a wrong field in the SQL.
Now I have it right, but when I tab over to the second combobox after
selecting a value in the first, I just getting a blank box. Maybe something
to do with column heads, column widths, bound column, etc.?

Ok, thanks you two, that's a little bit clearer. Here's the code I put in
the second combobox's Record Source field:

SELECT [tblItemMats].[ID], [tblItemMats].Description FROM tblItemMats WHERE
[tblItemMats].[Material]=Combo24;

And the requery in the first combobox After_Update event:

Me!Combo36.Requery

However, now when I select an item in the first combobox, I'm getting a
popup:
"Enter Parameter Value: tblItemMats.Description"

Working off this page:
http://www.mvps.org/access/forms/frm0028.htm

[...] I'm also curious, is Dev Ashish suggesting two possible
ways of doing this on that page?

Yes. That page shows two ways of doing it: one involving modifying the
second combo box's rowsource at run time, and another involving setting
the row source at design time with a criterion referring to the first
combo box, so all you have to do at run time is requery the second combo
box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Hi, Ricter.

I just getting a blank box. Maybe something
to do with column heads, column widths, bound column, etc.?

The second combo box is blank because of at least one of the following:

1. [Event Procedure] is not set in Combo24's OnAfterUpdate Property; or
2. Combo24_AfterUpdate( ) does not exist in the form's module or, if it
does, then it doesn't have the code you've posted in your other thread typed
into it; or
3. The query is returning an empty Recordset; or
4. The combo box's column properties are set incorrectly.

Ensure that [Event Procedure] is set properly, that the Combo24_AfterUpdate(
) subroutine exists (and has the correct code) by selecting the elipses (...)
on the right side of the combo box's OnAfterUpdate Property to jump to the
correct VBA procedure in the form's module, that the query returns at least
one record, that the ColumnWidths Properties are not set to zero, and that
the ColumnCount Property is not too low for the number of columns needed to
display the values. Combo24's ColumnCount Property should be 2 and the
ColumnWidths Property should be something like 0";1.0" so that at least
something should be displayed from the second column of the query and you can
adjust the width properly later when you see how wide it needs to be.

If everything else is in order, then check the query. To determine whether
or not your query is actually returning at least one record, create a new
query and go to the SQL View pane and paste the following into the window:

SELECT ID, Material
FROM tblItemMats
WHERE ([Type] = 'XXXX')

Replace XXXX with the value of the bound column for the row selected in
Combo24. If you don't know what that value is, then temporarily place the
following line of code in Combo24's After_Update( ) event:

MsgBox Me!Combo24

Save the code and compile the module. Then open the form in Form View and
select a row in the Combo24 combo box. The message box will show you the
text string to use to replace XXXX in the SQL query above. Type that value
in and run the query. Are there any records displayed? If not, then your
combo boxes are working properly, but there's nothing for the second combo
box to display.

I'd highly recommend that you rename these combo boxes with meaningful names
for future programming maintenance, and that you avoid using Reserved words,
such as Type, as names for identifiers in your database objects and code in
order to avoid wasting time chasing bugs.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Ok, never mind the popup concern and sorry, I had a wrong field in the SQL.
Now I have it right, but when I tab over to the second combobox after
selecting a value in the first, I just getting a blank box. Maybe something
to do with column heads, column widths, bound column, etc.?

Ok, thanks you two, that's a little bit clearer. Here's the code I put in
the second combobox's Record Source field:

SELECT [tblItemMats].[ID], [tblItemMats].Description FROM tblItemMats WHERE
[tblItemMats].[Material]=Combo24;

And the requery in the first combobox After_Update event:

Me!Combo36.Requery

However, now when I select an item in the first combobox, I'm getting a
popup:
"Enter Parameter Value: tblItemMats.Description"

Working off this page:
http://www.mvps.org/access/forms/frm0028.htm

[...] I'm also curious, is Dev Ashish suggesting two possible
ways of doing this on that page?

Yes. That page shows two ways of doing it: one involving modifying the
second combo box's rowsource at run time, and another involving setting
the row source at design time with a criterion referring to the first
combo box, so all you have to do at run time is requery the second combo
box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)