Synchronize three or more combobox


G

Guest

Is possible to synchronize three or more combo box at once? If do, can show
me the way or the code please, thanks for the support.
 
Ad

Advertisements

D

Douglas J. Steele

What do you mean by synchronize? Do you want it such that picking something
in combo1 that will cause the values in combo2 to be filtered to a smaller
list, and picking something in combo2 will cause the values in combo3 to be
filtered to a smaller list, or do you want it such that if you pick the 3rd
item in combo1, specific items will be selected in combo2 and combo3?
 
G

Guest

:
picking something
in combo1 that will cause the values in combo2 to be filtered to a smaller
list, and picking something in combo2 will cause the values in combo3 to be
filtered to a smaller list

This one,thanks.
 
D

Douglas J. Steele

You can put code in the AfterUpdate event of combo1 to change the
RecordSource for combo2, and code in the AfterUpdate event of combo2 to
change the RowSource for combo3 and so on:

Private Sub Combo1_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Field2, Field3 " & _
"FROM Table2 " & _
"WHERE Field1 = " & Me.Combo1 & _
" ORDER BY Field3"
Me.Combo2.RowSource = strSQL

End Sub

Private Sub Combo2_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Field4, Field5 " & _
"FROM Table3 " & _
"WHERE Field1 = " & Me.Combo1 & _
" AND Field2 = " & Me.Combo2 & _
" ORDER BY Field5"
Me.Combo3.RowSource = strSQL

End Sub

etc.

You can also have the SQL statement that makes up the RowSource for combo2,
combo3, etc have a reference to the previous combo box(es). In other words,
the RowSource for Combo2 could be

SELECT Field2, Field3 FROM Table2 WHERE Field1 = [Forms]![Form1]!{Combo1]
ORDER BY Field3

You'd then invoke the Requery method for the combo box once a value has been
selected in the previous combo:

Private Sub Combo1_AfterUpdate()

Me.Combo2.Requery

End Sub
 
G

Guest

Something i dont understand, maybe you can explan to me,why field2,field3 and
field4,field5 if i got only 3 field only?
 
D

Douglas J. Steele

Since I had no idea what your table and field names were, I had to choose
names at random.
 
Ad

Advertisements

G

Guest

No,i mean,how do i do if i only got 3 field since your example is given 5
field?
 
D

Douglas J. Steele

You generate the appropriate SQL using whatever your field names are.
 
C

ccg

Hi Douglas and anyone else who sees this,

I'm trying to do something similar to what "Info" asked, except I need a
third combo box to limit its options after a selection is made for the second
combo box. Is there a way to do that?

I need to choose a RoleLevel, then limit RoleCategory by what is selected in
RoleLevel. Then I need to limit Role by what is selected in RoleCategory.

I followed the instructions on this page -
http://msdn.microsoft.com/en-us/library/bb404901.aspx - to do the first set,
but I can't figure out how to get the second set to work. I'm stumped.

Can someone help? I would appreciate it very much! Thanks!

(I am working in Access 2007.)



Douglas J. Steele said:
You can put code in the AfterUpdate event of combo1 to change the
RecordSource for combo2, and code in the AfterUpdate event of combo2 to
change the RowSource for combo3 and so on:

Private Sub Combo1_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Field2, Field3 " & _
"FROM Table2 " & _
"WHERE Field1 = " & Me.Combo1 & _
" ORDER BY Field3"
Me.Combo2.RowSource = strSQL

End Sub

Private Sub Combo2_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Field4, Field5 " & _
"FROM Table3 " & _
"WHERE Field1 = " & Me.Combo1 & _
" AND Field2 = " & Me.Combo2 & _
" ORDER BY Field5"
Me.Combo3.RowSource = strSQL

End Sub

etc.

You can also have the SQL statement that makes up the RowSource for combo2,
combo3, etc have a reference to the previous combo box(es). In other words,
the RowSource for Combo2 could be

SELECT Field2, Field3 FROM Table2 WHERE Field1 = [Forms]![Form1]!{Combo1]
ORDER BY Field3

You'd then invoke the Requery method for the combo box once a value has been
selected in the previous combo:

Private Sub Combo1_AfterUpdate()

Me.Combo2.Requery

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Info said:
:
picking something
in combo1 that will cause the values in combo2 to be filtered to a smaller
list, and picking something in combo2 will cause the values in combo3 to
be
filtered to a smaller list

This one,thanks.
 
C

ccg

Oops. Didn't read closely. Sorry. I think Info and I were/are trying to do
that same thing. I just don't understand how it is set up. I'll try again...
If someone knows how to make the 3rd box be limited by the 2nd choice using
the method on that link, that would be great. I'll try this code, though.
It's confusing to me, but maybe I can figure it out.

ccg said:
Hi Douglas and anyone else who sees this,

I'm trying to do something similar to what "Info" asked, except I need a
third combo box to limit its options after a selection is made for the second
combo box. Is there a way to do that?

I need to choose a RoleLevel, then limit RoleCategory by what is selected in
RoleLevel. Then I need to limit Role by what is selected in RoleCategory.

I followed the instructions on this page -
http://msdn.microsoft.com/en-us/library/bb404901.aspx - to do the first set,
but I can't figure out how to get the second set to work. I'm stumped.

Can someone help? I would appreciate it very much! Thanks!

(I am working in Access 2007.)



Douglas J. Steele said:
You can put code in the AfterUpdate event of combo1 to change the
RecordSource for combo2, and code in the AfterUpdate event of combo2 to
change the RowSource for combo3 and so on:

Private Sub Combo1_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Field2, Field3 " & _
"FROM Table2 " & _
"WHERE Field1 = " & Me.Combo1 & _
" ORDER BY Field3"
Me.Combo2.RowSource = strSQL

End Sub

Private Sub Combo2_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Field4, Field5 " & _
"FROM Table3 " & _
"WHERE Field1 = " & Me.Combo1 & _
" AND Field2 = " & Me.Combo2 & _
" ORDER BY Field5"
Me.Combo3.RowSource = strSQL

End Sub

etc.

You can also have the SQL statement that makes up the RowSource for combo2,
combo3, etc have a reference to the previous combo box(es). In other words,
the RowSource for Combo2 could be

SELECT Field2, Field3 FROM Table2 WHERE Field1 = [Forms]![Form1]!{Combo1]
ORDER BY Field3

You'd then invoke the Requery method for the combo box once a value has been
selected in the previous combo:

Private Sub Combo1_AfterUpdate()

Me.Combo2.Requery

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Info said:
:
picking something
in combo1 that will cause the values in combo2 to be filtered to a smaller
list, and picking something in combo2 will cause the values in combo3 to
be
filtered to a smaller list

This one,thanks.
 
D

DJS

Hi I have a similar "problem" - I think this type of scenario is common.
I have my comboxs synchronising ok but I do not want to stre redundent data.
I only want to update a field bound to lat combo e.g store the product but I
don't need to store the category as I can find it by lookup or joining tables
later. So I have made the first combo unbound it narrows the second combo and
its value is stored. This means I need code in the form current event to
ensure the first (unbound combo) has its value selected based on data in the
second.
This all works fine - the only problem is during data entry if the user does
an "undo" the comboboxes are not always reset to their original values.
Any suggestions.

I think if I store redundant data ie make all comboboxes bound this is not a
problem.
 
Ad

Advertisements

Joined
Jun 5, 2014
Messages
1
Reaction score
0
Hi Doug,
I'm trying to do something similar to this -- "if you pick the 3rd
item in combo1, specific items will be selected in combo2 and combo3" however I want it to be such that when combo1 is selected and then combo2 is selected then combo3 should get populated automatically. Is there a way? Thanks!

What do you mean by synchronize? Do you want it such that picking something
in combo1 that will cause the values in combo2 to be filtered to a smaller
list, and picking something in combo2 will cause the values in combo3 to be
filtered to a smaller list, or do you want it such that if you pick the 3rd
item in combo1, specific items will be selected in combo2 and combo3?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top