Hi
I have 3 cascading combo boxes. Combo1 and 2 cascade and they work fine.
I wanted to find out how can I link combo 2 to combo 3.
I tried the same way i did for the first 2 they dont work.
They all use the same table
Combo1: Region
Combo2: SBU
Combo3: Officer Name
Combo1_AfterUpdate()
Forms![Form1]![Combo2].Requery
Combo2
RowSource: Select id, sbu, region from lntable
Where region=[Forms]![Form1]![Combo1]
Combo1_AfterUpdate()
Forms![Form1]![Combo3].Requery
Combo3:
RowSource: Select id, officername, sbu from lntable
Where Sbu=[Forms]![Form1]![Combo2]
Greatly Appreciate help
Thanks
AG
TinaR wrote:
Marshall,THANK YOU for your patience and for sticking with me.
18-Feb-10
Marshall,
THANK YOU for your patience and for sticking with me. I made the changes
you
indicated and it works perfectly. Thank you so very much!!
Tina
:
Previous Posts In This Thread:
Need help cascading 5 combo boxes
I???m working with Access 2007. I have a form with two groups of
cascading
combo boxes. My first group has two cascading combo boxes, which works.
My
second group has 5 cascading combo boxes. This second group is giving me
trouble. The first 3 combo boxes cascade nicely. But combos 4 & 5
display
everything. I don???t know what I???m doing wrong. Everything I???ve
seen posted
only refers to two combos so I can???t seem to fill in the blanks when
working
with 5 combo???s. I know I???m missing something.
The table name is tblICGDM.
The 5 combo boxes are: cboInvestor; cboCompany; cboGroup; cboDivision;
cboMarket
(This is also the order in which they should cascade)
Below is what I have for my Record Source and AfterUpdate event for each
combo.
cboInvestor Row Source: SELECT DISTINCT tblICGDM.Investor FROM tblICGDM
ORDER BY Investor;
cboInvestorAfterUpdate event:
Private Sub cboInvestor_AfterUpdate()
On Error Resume Next
cboCompany.RowSource = "SELECT DISTINCT tblICGDM.Company " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Investor = '" & cboInvestor.Value & "' " & _
"ORDER BY tblICGDM.Company;"
Me.cboCompany.Requery
Me.cboGroup.Requery
Me.cboDivision.Requery
Me.cboMarket.Requery
Me!cboCompany = Null
Me!cboGroup = Null
Me!Division = Null
Me!Market = Null
End Sub
cboCompany Row Source: SELECT DISTINCT tblICGDM.Company FROM tblICGDM
WHERE
tblICGDM.Investor=cboinvestor ORDER BY tblICGDM.Company;
cboCompany AfterUpdate event:
Private Sub cboCompany_AfterUpdate()
Me.cboGroup.Requery
Me.cboDivision.Requery
Me.cboMarket.Requery
Me!cboGroup = Null
Me!cboDivision = Null
Me!cboMarket = Null
End Sub
cboGroup Row Source: SELECT DISTINCT Group FROM tblICGDM WHERE
[Company]=cboCompany;
cboGroup AfterUpdate event:
Private Sub cboGroup_AfterUpdate()
Me.cboDivision.Requery
Me.cboMarket.Requery
Me!cboDivision = Null
Me!cboMarket = Null
End Sub
cboDivision Row Source: SELECT DISTINCT Division FROM tblICGDM WHERE
[Group]=cboGroup;
cboDivision AfterUpdate event:
Private Sub cboDivision_AfterUpdate()
Me.cboMarket.Requery
Me!cboMarket = Null
End Sub
cboMarket RowSource: SELECT DISTINCT Market FROM tblICGDM WHERE
[Division]=cboDivision;
I know I need to do something with the form???s OnCurrent event but I???m
not
sure exactly what to do or how to combine it with my first group of 2
cascading combo boxes. Sorry this post is so long but I would appreciate
any
help.
Thanks in advance!
Tina
TinaR wrote:The general idea looks to be correct.
TinaR wrote:
The general idea looks to be correct. BUT, Group is an SQL
reserved word and I would expect cboGroup's query to
generate an error.
I would also expect to get a prompt for the criteria in all
except the first query, but maybe your version of Access has
learned to get a value from a control on a form without
being told which form it is in?? At least in the past, I
thought I needed to use code in each AfterUpdate event
similar to what you have in the first one:
cboCompany.RowSource = "SELECT DISTINCT ...
"WHERE tblICGDM.Investor = '" & cboInvestor.Value & "' " & _
You do not have the equivalent line in the other procedures.
That would be ok, but I have always thought I needed to use:
WHERE tblICGDM.Investor=Forms!yourform.cboinvestor
instead of what you posted:
WHERE tblICGDM.Investor=cboinvestor
--
Marshall,Sorry this has taken me so long to get back to you but I was
pulled
Marshall,
Sorry this has taken me so long to get back to you but I was pulled away
on
a different project. But now I am back so I can focus on this one again.
I took your advice and changed my Row Sources and AfterUpdate events as
you
suggested. To be honest, I do not know enough about this to understand
why
this works or does not work. I tried to be consistent with the code
changes
you suggested. The changes I made seem to work but I noticed that if, for
example, I change the second combo box, the remaining 3 combo boxes go
blank
(which is correct) but if select the drop down, the old value is still
there.
This actually happens to all the drop downs after the one I changed. I
do not know what I am doing wrong. I think it might have something to do
with
the form's OnCurrent event. Currently, I have code in the form's
OnCurrent
event for another group of two cascading combo boxes. I should probably
put
something in there for this group of 5 cascading combo boxes, but I do not
know how to integrate the two groups. I am re-posting all my code with
the
changes I made. Thank you for your help.
Tina
Here is my code:
cboInvestor Row Source:
SELECT DISTINCT tblICGDM.Investor FROM tblICGDM ORDER BY Investor;
cboInvestorAfterUpdate event:
Private Sub cboInvestor_AfterUpdate()
On Error Resume Next
cboCompany.RowSource = "SELECT DISTINCT tblICGDM.Company " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Investor = '" & cboInvestor.Value & "' " & _
"ORDER BY tblICGDM.Company;"
Me.cboCompany.Requery
Me.cboGroup.Requery
Me.cboDivision.Requery
Me.cboMarket.Requery
Me!cboCompany = Null
Me!cboGroup = Null
Me!Division = Null
Me!Market = Null
End Sub
________________________________________
cboCompany Row Source:
SELECT DISTINCT tblICGDM.Company FROM tblICGDM WHERE
tblICGDM.Investor=Forms!frmAdvantageTrust Adds.cboinvestor ORDER BY
tblICGDM.Company;
cboCompany AfterUpdate event:
Private Sub cboCompany_AfterUpdate()
On Error Resume Next
cboGroup.RowSource = "SELECT DISTINCT tblICGDM.Group " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Company = '" & cboCompany.Value & "' " & _
"ORDER BY tblICGDM.Group;"
Me.cboGroup.Requery
Me.cboDivision.Requery
Me.cboMarket.Requery
Me!cboGroup = Null
Me!cboDivision = Null
Me!cboMarket = Null
End Sub
________________________________________
cboGroup Row Source:
SELECT DISTINCT tblICGDM.Group FROM tblICGDM WHERE
tblICGDM.Company=Forms!frmAdvantageTrust Adds.cboCompany ORDER BY
tblICGDM.Group;
cboGroup AfterUpdate event:
Private Sub cboGroup_AfterUpdate()
On Error Resume Next
cboDivision.RowSource = "SELECT DISTINCT tblICGDM.Division " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Group = '" & cboGroup.Value & "' " & _
"ORDER BY tblICGDM.Division;"
Me.cboDivision.Requery
Me.cboMarket.Requery
Me!cboDivision = Null
Me!cboMarket = Null
End Sub
________________________________________
cboDivision Row Source:
SELECT DISTINCT Division FROM tblICGDM WHERE
tblICGDM.Group=Forms!frmAdvantageTrust Adds.cboGroup ORDER BY
tblICGDM.Division;
cboDivision AfterUpdate event:
Private Sub cboDivision_AfterUpdate()
On Error Resume Next
cboMarket.RowSource = "SELECT DISTINCT tblICGDM.Market " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Division = '" & cboDivision.Value & "' " & _
"ORDER BY tblICGDM.Market;"
Me.cboMarket.Requery
You are still being inconsistent about how you set the rowsource queries.
You are still being inconsistent about how you set the row
source queries.
Because of the need to use the current event to sync the
combo boes with the data as you navigate to different
records, it is much simpler to use Where clauses that
reference the previous combo box. The setting for the row
sources in design view was fine, so you do not need to use
code to change them. A Requery will suffice to use the new
value. The Current event then only needs to do the
requeries.
I will try to modify what you posted to what I think you need,
but check it over carefully to make sure it is appropriate
and correct.
Note: If you must use spaces in names, then you have to
enclose the name in [ ]
--
Marsh
MVP [MS Access]
TinaR wrote:
WHERE Investor=Forms![frmAdvantageTrust Adds].cboinvestor
WHERE Company=Forms![frmAdvantageTrust Adds].cboCompany
WHERE tblICGDM.Group=Forms![frmAdvantageTrust Adds].cboGroup
Marshall,Thanks for the help. Much of this is over my head.
Marshall,
Thanks for the help. Much of this is over my head. I know enough to get
myself into trouble.
I have tried and re-tried with some of the
suggestions you gave me and what I found was that even if my code is not
the
best, with a little tweeking (syntax), it works. The only thing that does
not
work is when I go back and select something else from the 1st, 2nd or 3rd
drop downs, the old selections are still there. I tried just the 4
requery's
you suggested at the end of your last post, but I still see the old
selections in the drop downs. I am stumped. I do not know what else to
try.
All the posts talk about two cascading combos, and I was able to get that
to
work. But I cannot seem to find anything that really addresses anything
more
than than two cascading combos. I did not re-post my code because it
takes so
much space. I can if you think it will help. Any suggestion is very much
appreciated.
Thank you,
Tina
:
TinaR wrote:More than two dependent combo is done the same way as
thesecond
TinaR wrote:
More than two dependent combo is done the same way as the
second one. The thing I was trying to get across is to
forget about setting the row source property and just use
the previous combo box as criteria in the row source query.
If that is still over your head, post the code and I will take
another stab at fixing it.
--
Marsh
MVP [MS Access]
Thanks for your patience... I have spent the last week playing with this.
Thanks for your patience... I have spent the last week playing with this.
If I
make the changes you suggested, I "break" something else. Obviously, I am
going about this wrong somewhere. So I am posting the code I have. This
works EXCEPT for the requery part. (If I change the first combo, my
previous
selection still shows up in combo's 3, 4 & 5 but combo 2 is correct and as
I
make my new selections, the combo's correct themselves.)
cboInvestor RowSource:
SELECT DISTINCT tblICGDM.Investor FROM tblICGDM ORDER BY
tblICGDM.Investor;
cboInvestor AfterUpdate Event:
Private Sub cboCompany_AfterUpdate()
On Error Resume Next
cboGroup.RowSource = "SELECT DISTINCT tblICGDM.Group " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Company = '" & cboCompany.Value & "' " & _
"ORDER BY tblICGDM.Group;"
Me.cboGroup = vbNullString
Me.cboDivision = vbNullString
Me.cboMarket = vbNullString
Me.cboGroup.Requery
Me.cboDivision.Requery
Me.cboMarket.Requery
End Sub
cboCompany RowSource:
SELECT DISTINCT tblICGDM.Company FROM tblICGDM WHERE tblICGDM.Investor =
''
ORDER BY tblICGDM.Company;
cboCompany AfterUpdate Event:
Private Sub cboCompany_AfterUpdate()
On Error Resume Next
cboGroup.RowSource = "SELECT DISTINCT tblICGDM.Group " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Company = '" & cboCompany.Value & "' " & _
"ORDER BY tblICGDM.Group;"
Me.cboGroup = vbNullString
Me.cboDivision = vbNullString
Me.cboMarket = vbNullString
Me.cboGroup.Requery
Me.cboDivision.Requery
Me.cboMarket.Requery
End Sub
cboGroup RowSource:
SELECT DISTINCT tblICGDM.Group FROM tblICGDM WHERE tblICGDM.Company = ''
ORDER BY tblICGDM.Group;
cboGroup AfterUpdate Event:
Private Sub cboGroup_AfterUpdate()
On Error Resume Next
cboDivision.RowSource = "SELECT DISTINCT tblICGDM.Division " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Group = '" & cboGroup.Value & "' " & _
"ORDER BY tblICGDM.Division;"
Me.cboDivision = vbNullString
Me.cboMarket = vbNullString
Me.cboDivision.Requery
Me.cboMarket.Requery
End Sub
cboDivision RowSource:
SELECT DISTINCT tblICGDM.Division FROM tblICGDM WHERE tblICGDM.Group = ''
ORDER BY tblICGDM.Division;
cboDivision AfterUpdate Event:
Private Sub cboDivision_AfterUpdate()
On Error Resume Next
cboMarket.RowSource = "SELECT DISTINCT tblICGDM.Market " & _
"FROM tblICGDM " & _
"WHERE tblICGDM.Division = '" & cboDivision.Value & "' " & _
"ORDER BY tblICGDM.Market;"
Me.cboMarket = vbNullString
Me.cboMarket.Requery
End Sub
cboMarket RowSource:
SELECT DISTINCT tblICGDM.Market FROM tblICGDM WHERE tblICGDM.Division = ''
ORDER BY tblICGDM.Market;
Form???s OnCurrent Event:
Private Sub Form_Current()
On Error Resume Next
'Synchronize COT Combo with existing Type
cboCOT = DLookup("[COT]", "tblCOTandType" & _
"[Type]='" & cboType.Value & "'")
'Synchronize type combo with existing type
cboType.RowSource = "Select tblCOTandType.Type " & _
"FROM tblCotandType " & _
"Where tblCOTandType.COT = '" & cboCOT.Value & "' " & _
"ORDER BY tblCOTandType.Type;"
I am just typing changes into what you posted, so doublecheck the
specifics.
I am just typing changes into what you posted, so double
check the specifics. At least the general idea should help
straighten it out.
--
Marsh
MVP [MS Access]
TinaR wrote:
You posted the wrong AfterUpdate code here so I cannot edit
it. It should be very similar to the other combo boxes.
I.e.
Private Sub cboInvestor_AfterUpdate()
Me.cboCompany = Null
Me.cboGroup = Null
Me.cboDivision = Null
Me.cboMarket = Null
Me.cboCompany.Requery
Me.cboGroup.Requery
Me.cboDivision.Requery
Me.cboMarket.Requery
End Sub
Change that query to:
SELECT DISTINCT tblICGDM.Company
FROM tblICGDM
WHERE tblICGDM.Investor = Forms!yourform.cboInvestor
ORDER BY tblICGDM.Company
Delete the next 5 lines
Me.cboGroup = Null
Me.cboDivision = Null
Me.cboMarket = Null
Change that query to:
SELECT DISTINCT tblICGDM.Group
FROM tblICGDM
WHERE tblICGDM.Company = Forms!yourform.cboCompany
ORDER BY tblICGDM.Group
Delete the next 5 lines
Me.cboDivision = Null
Me.cboMarket = Null
Change that query to:
SELECT DISTINCT tblICGDM.Division
FROM tblICGDM
WHERE tblICGDM.Group = Forms!yourform.cboGroup
ORDER BY tblICGDM.Division
Marshall,THANK YOU for your patience and for sticking with me.
Marshall,
THANK YOU for your patience and for sticking with me. I made the changes
you
indicated and it works perfectly. Thank you so very much!!
Tina
:
Multiple Cascading Combo boxes Access 2003
Hi all,
I am trying to create a form with 3 cascading combo boxes.
The first 2 comboboxes casacade, but I am not able to cascade the 3rd one
based on 2nd combo selection.
I have
ComboBox1: Region
ComboBox2: SBU
ComboBox3: Officer Name
I am using one single table for all 3.
ComboBox1: Region
RowSource: Select region from Lntable
ComboBox1_AfterUpdate()
Forms![Form1]![Combo2].Requery
ComboBox2: SBU
RowSource: Select ID,SBU, Region from Lntable
Where Region = [Forms]![Form1]![Combobox1]
ComboBox2_AfterUpdate()
Forms![Form1]![Combo3].Requery
ComboBox3: Officer Name
RowSource: Select ID,Officer Name, SBU from Lntable
Where SBU = [Forms]![Form1]![Combobox2]
Really appreciate help on this!!
Thanks
AG
Submitted via EggHeadCafe - Software Developer Portal of Choice
Excel 2007 Filter Tool
http://www.eggheadcafe.com/tutorial...a2cb-1f3a46fbea8f/excel-2007-filter-tool.aspx
