Cascading Combo --- Just Not Happening!!

[CJ]

Hi groupies

I just do not know what the deal is here. I have tried everything I could
find regarding cascading combo boxes and still mine are not working
properly.

My combos are on a continuous subform.

The first combo selects a category of products. The second combo should find
the correct products based on the category shown in the first. My problem is
that when I select a different category in the next record, the product in
the previous record disappears.

Here is the sql and code for the first combo:

SELECT tblExpCategories.lngCatID, tblExpCategories.strExpCategory
FROM tblExpCategories
ORDER BY tblExpCategories.strExpCategory;

Bound column is 1

Private Sub lngCategoryID_AfterUpdate()
strExpense = Null
strExpense.Requery
End Sub

The second combo sql is:

SELECT tblPurchaseExpenses.lngPurchaseExpID, tblPurchaseExpenses.strExpense,
tblPurchaseExpenses.lngCatID
FROM tblPurchaseExpenses
WHERE
(((tblPurchaseExpenses.lngCatID)=[Forms]![frmAppointmentPurchases]![lngCategoryID]))
ORDER BY tblPurchaseExpenses.strExpense;

Bound column is 1

I have tried everything that I can think of.....why is this such a
chore?!?!?

 

[AccessVandal via AccessMonster.com]

Try to search is forum. try something like,

Cascading combo boxes and updating second box

Pat Hartman’s -
There is a download from a MVP site, Ken Snell or
somebody else

I had posted something on this somewhere in june.

And is the combo bound to the record source?

 

[CJ]

I have tried every download and code that I could find, that is why I posted
my own code because WHO KNOWS perhaps there is something I am still missing.

The combo boxes are bound to the fields in the record source and everything
looks great in the table.

sigh
CJ

 

[ruralguy via AccessMonster.com]

Maybe the way Martin Green explains it will help.
http://www.fontstuff.com/access/acctut10.htm

I have tried every download and code that I could find, that is why I posted
my own code because WHO KNOWS perhaps there is something I am still missing.

The combo boxes are bound to the fields in the record source and everything
looks great in the table.

sigh
CJ

Try to search is forum. try something like,

[quoted text clipped - 12 lines]

 

[AccessVandal via AccessMonster.com]

Well, here is the cut&paste from my previous reply to,
'-----------------------------------------------------------------------------
----------
Sierras,

I propose a Pat Hartman’s cascading combo boxes on a continuous form.
(if I can remember)

On the current event of the sub form,

Me.MyFirstCombo.Requery
Me.MySecondCombo.Requery
Me.MyThirdCombo.Requery

This will requery the combos when you are at the current record of your
subform.

Next, create or unbound your 3 existing combo boxes with the existing query.
You can hide the unbound combos, so that you’ll only see the down arrow
button.

Next, create 3 new Textboxes that will be bound by Query/Table of your
subform. Align the Textboxes to the unbound combos, so that they look like
the original Combo Boxes. Note – You can bound the “MyFirstCombo†and use
only to 2 TextBoxes if you want to.

Use your combo box query or VB to autofill the textboxes.
Or
Use your AfterUpdate event of the your combo box to requery the bound
TextBoxes.

Use the afterupdate event in the combos,

Private Sub MyFirstCombo_AfterUpdate()
Me.MySecondComb.Requery
Me.MyTextBox1.Requery ‘if using Textbox
End Sub

Private Sub MySecondCombo_AfterUpdate()
Me.MyThirdCombo.Requery
Me.MyTextBox2.Requery
End Sub

Private Sub MyThirdCombo_AfterUpdate()
Me.MyTextBox3.Requery
End Sub

If you use the query in the second combo, you need to refer to a control like
“Forms!SubFormName!MyFirstCombo†in your second combo box in the Query Grid
Criteria and same for the Third Combo.
'------------------------------end--------------------------------------------
---------------------

 

[Ken Snell \(MVP\)]

Try to search is forum. try something like,

Cascading combo boxes and updating second box

Pat Hartman's -
There is a download from a MVP site, Ken Snell or
somebody else

http://www.cadellsoftware.org/SampleDBs.htm#ContCascadeCbo