Cascading Combo Boxes in Main Form

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

I have a sample of cascading listboxes, which are functionally identical in
operation to combo boxes, at:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"MGC" wrote in message
...
I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and
child
fields. My concern at present is getting the Combo Boxes to cascade and
the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

MGC,

I have a demo of cascading combo boxes at:
http://www.askdoctoraccess.com/downloadpage.htm

Scroll down toward the bottom of the page and click on the
"DemoCascadeComboBoxes.zip" link to down load the zip file.

As for populating other controls utilizing the value of one or even both of
the combo boxes, use the AfterUpdate event of the combo boxes to accomplish
this.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

Mr. B:

Thank you for your help!!

My next question is: if I want my second box to cascade into the third, do
I use the same code or add more to the first one?

How do I auto-populate the other three fields? Which code do I use?

"Mr. B" wrote:

MGC,

I have a demo of cascading combo boxes at:
http://www.askdoctoraccess.com/downloadpage.htm

Scroll down toward the bottom of the page and click on the
"DemoCascadeComboBoxes.zip" link to down load the zip file.

As for populating other controls utilizing the value of one or even both of
the combo boxes, use the AfterUpdate event of the combo boxes to accomplish
this.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

MGC,

You can modify the existing code to reference the appropriate opjects and
place it in the AfterUpdate event of your second control.

You would also need to add a statement to the original code in the first
combo box that will also requery the third combo box when the first combo box
is changed:

Me.NameOfThirdComboBox.Requery

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Mr. B:

Thank you for your help!!

My next question is: if I want my second box to cascade into the third, do
I use the same code or add more to the first one?

How do I auto-populate the other three fields? Which code do I use?

"Mr. B" wrote:

MGC,

I have a demo of cascading combo boxes at:
http://www.askdoctoraccess.com/downloadpage.htm

Scroll down toward the bottom of the page and click on the
"DemoCascadeComboBoxes.zip" link to down load the zip file.

As for populating other controls utilizing the value of one or even both of
the combo boxes, use the AfterUpdate event of the combo boxes to accomplish
this.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

Thanks for all your help and patience! But I am still struggling with this
whole thing. When I add the following to Combo12 (my first box):

Option Compare Database
Option Explicit

Private Sub Combo12_AfterUpdate()
With Me.ComboAccountingPeriod (my second box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

and then do the same for Combo 14 (my second box):

Option Compare Database
Option Explicit

Private Sub Combo14_AfterUpdate()
With Me.ComboDepartment (my third box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

the first expression in Combo 12 disappears. So.......

I am very confused can you tell?

"Mr. B" wrote:

MGC,

You can modify the existing code to reference the appropriate opjects and
place it in the AfterUpdate event of your second control.

You would also need to add a statement to the original code in the first
combo box that will also requery the third combo box when the first combo box
is changed:

Me.NameOfThirdComboBox.Requery

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Mr. B:

Thank you for your help!!

My next question is: if I want my second box to cascade into the third, do
I use the same code or add more to the first one?

How do I auto-populate the other three fields? Which code do I use?

"Mr. B" wrote:

MGC,

I have a demo of cascading combo boxes at:
http://www.askdoctoraccess.com/downloadpage.htm

Scroll down toward the bottom of the page and click on the
"DemoCascadeComboBoxes.zip" link to down load the zip file.

As for populating other controls utilizing the value of one or even both of
the combo boxes, use the AfterUpdate event of the combo boxes to accomplish
this.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

You need to add a line of code to the Combo12 code to requery each of your
additional combo boxes like this:

Me.ComboAccountingPeriod.requery
me.combo14.requery

In the second combo box you would need to requery the third combo box and so
on. And by the way in one place you say that "ComboAccountingPeriod" is your
second box and in another place you say that Combo 14 is your second box. I
am confused here as well.

I need you to explain further your "the first expression in Combo 12
disappears." statement.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Thanks for all your help and patience! But I am still struggling with this
whole thing. When I add the following to Combo12 (my first box):

Option Compare Database
Option Explicit

Private Sub Combo12_AfterUpdate()
With Me.ComboAccountingPeriod (my second box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

and then do the same for Combo 14 (my second box):

Option Compare Database
Option Explicit

Private Sub Combo14_AfterUpdate()
With Me.ComboDepartment (my third box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

the first expression in Combo 12 disappears. So.......

I am very confused can you tell?

"Mr. B" wrote:

MGC,

You can modify the existing code to reference the appropriate opjects and
place it in the AfterUpdate event of your second control.

You would also need to add a statement to the original code in the first
combo box that will also requery the third combo box when the first combo box
is changed:

Me.NameOfThirdComboBox.Requery

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Mr. B:

Thank you for your help!!

My next question is: if I want my second box to cascade into the third, do
I use the same code or add more to the first one?

How do I auto-populate the other three fields? Which code do I use?

"Mr. B" wrote:

MGC,

I have a demo of cascading combo boxes at:
http://www.askdoctoraccess.com/downloadpage.htm

Scroll down toward the bottom of the page and click on the
"DemoCascadeComboBoxes.zip" link to down load the zip file.

As for populating other controls utilizing the value of one or even both of
the combo boxes, use the AfterUpdate event of the combo boxes to accomplish
this.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

I'm not sure where to begin.

Combo12 (named Entity and the first one) pulls from tbleEntity
Combo14 (named Accounting Period and the second box) is a value list 1 thru 12
Combo16 (named Department) pulls from qryBudgetData

Can you please tell me the codes to use in each 'AfterUpdate' event if I
want Combo 14 to populate based on Combo 12; and Combo 16 to populate based
on Combos 12 and 14?

Sign me lost...

"Mr. B" wrote:

You need to add a line of code to the Combo12 code to requery each of your
additional combo boxes like this:

Me.ComboAccountingPeriod.requery
me.combo14.requery

In the second combo box you would need to requery the third combo box and so
on. And by the way in one place you say that "ComboAccountingPeriod" is your
second box and in another place you say that Combo 14 is your second box. I
am confused here as well.

I need you to explain further your "the first expression in Combo 12
disappears." statement.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Thanks for all your help and patience! But I am still struggling with this
whole thing. When I add the following to Combo12 (my first box):

Option Compare Database
Option Explicit

Private Sub Combo12_AfterUpdate()
With Me.ComboAccountingPeriod (my second box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

and then do the same for Combo 14 (my second box):

Option Compare Database
Option Explicit

Private Sub Combo14_AfterUpdate()
With Me.ComboDepartment (my third box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

the first expression in Combo 12 disappears. So.......

I am very confused can you tell?

"Mr. B" wrote:

MGC,

You can modify the existing code to reference the appropriate opjects and
place it in the AfterUpdate event of your second control.

You would also need to add a statement to the original code in the first
combo box that will also requery the third combo box when the first combo box
is changed:

Me.NameOfThirdComboBox.Requery

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Mr. B:

Thank you for your help!!

My next question is: if I want my second box to cascade into the third, do
I use the same code or add more to the first one?

How do I auto-populate the other three fields? Which code do I use?

"Mr. B" wrote:

MGC,

I have a demo of cascading combo boxes at:
http://www.askdoctoraccess.com/downloadpage.htm

Scroll down toward the bottom of the page and click on the
"DemoCascadeComboBoxes.zip" link to down load the zip file.

As for populating other controls utilizing the value of one or even both of
the combo boxes, use the AfterUpdate event of the combo boxes to accomplish
this.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.

Ok, let's kinda backup and see if we can start from a good base point.

If the second box (Accounting Period) is just a list of values from 1 to 12
you will not need to requery that combo box because it would not produce a
different list based on the selection made in the "Entity" combo box.
However, if a selection has already been made from the second combo box and
then the user makes a change to the first combo box there would be a need to
requery the third combo box. (Now I ask you, is that confusing enough?)

Well, try the following code for each of the first two combo boxes:

Private Sub Entity_AfterUpdate()
If Not IsNull(Me.Entity) Then
With Me.Department
.Requery
.SetFocus
.Dropdown
End With
Else
With Me.Entity
.SetFocus
.Dropdown
End With
End If
End Sub

Private Sub Accounting_Period_AfterUpdate()
If Not IsNull(Me.Entity) Then
With Me.Department
.Requery
.SetFocus
.Dropdown
End With
Else
With Me.Entity
.SetFocus
.Dropdown
End With
End If
End Sub

This will cause the "Department" combo box to be requeried and presented
when the first and second combo boxes have had a selection made.

Once we get this working, then we can decide what to do when a selection is
made from the third combo box.

If all of this does not get your combo boxes working, you may need to send
me a copy of your database where I can actually see what's going on.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I'm not sure where to begin.

Combo12 (named Entity and the first one) pulls from tbleEntity
Combo14 (named Accounting Period and the second box) is a value list 1 thru 12
Combo16 (named Department) pulls from qryBudgetData

Can you please tell me the codes to use in each 'AfterUpdate' event if I
want Combo 14 to populate based on Combo 12; and Combo 16 to populate based
on Combos 12 and 14?

Sign me lost...

"Mr. B" wrote:

You need to add a line of code to the Combo12 code to requery each of your
additional combo boxes like this:

Me.ComboAccountingPeriod.requery
me.combo14.requery

In the second combo box you would need to requery the third combo box and so
on. And by the way in one place you say that "ComboAccountingPeriod" is your
second box and in another place you say that Combo 14 is your second box. I
am confused here as well.

I need you to explain further your "the first expression in Combo 12
disappears." statement.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Thanks for all your help and patience! But I am still struggling with this
whole thing. When I add the following to Combo12 (my first box):

Option Compare Database
Option Explicit

Private Sub Combo12_AfterUpdate()
With Me.ComboAccountingPeriod (my second box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

and then do the same for Combo 14 (my second box):

Option Compare Database
Option Explicit

Private Sub Combo14_AfterUpdate()
With Me.ComboDepartment (my third box)
.Requery
.SetFocus
.Dropdown
End With

End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmBudgetData"
End Sub

the first expression in Combo 12 disappears. So.......

I am very confused can you tell?

"Mr. B" wrote:

MGC,

You can modify the existing code to reference the appropriate opjects and
place it in the AfterUpdate event of your second control.

You would also need to add a statement to the original code in the first
combo box that will also requery the third combo box when the first combo box
is changed:

Me.NameOfThirdComboBox.Requery

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

Mr. B:

Thank you for your help!!

My next question is: if I want my second box to cascade into the third, do
I use the same code or add more to the first one?

How do I auto-populate the other three fields? Which code do I use?

"Mr. B" wrote:

MGC,

I have a demo of cascading combo boxes at:
http://www.askdoctoraccess.com/downloadpage.htm

Scroll down toward the bottom of the page and click on the
"DemoCascadeComboBoxes.zip" link to down load the zip file.

As for populating other controls utilizing the value of one or even both of
the combo boxes, use the AfterUpdate event of the combo boxes to accomplish
this.

-----
HTH
Mr. B
askdoctoraccess dot com


"MGC" wrote:

I am workng in Access 2003 and need some help with cascading combo boxes.
Here's the scenario:
Combo 12 - Entity from tblEntity, Entity Field
Combo14 - AcctgPeriod-value list of 1 through 12
Combo 16 - Department from tblGlCodeInfo, Department Field

I also have a query which has all of these same items...it's name is
qryBudgetData.

Combo12 should filter into Combo14 and Combo16 selections should be based
upon the selections made in Combo12 and 14.

I also have three other boxes: Budgeted Amount, Sum of Invoices and Budget
Variance which all come from qryBudgetData. These I want to auto-populate
depending upon the Combo Box selections.

I also have a subform which will house the AP data for the specific
entity/period/dept selected previously and have linked the master and child
fields. My concern at present is getting the Combo Boxes to cascade and the
other three boxes to auto populate though.

I have already tried using TechOnTheNet, FontStuff, etc. but continue to
have problems with this. I have not set any of the events or anything in
properties as I'm not exactly sure how. Please help if you can.