If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combo Box Sync
I have a form called Testing. I only want customers to test that have
Systems, so I select from the table CustomerSystemInventory in my Combo Box. The next Combo Box is what system they want to test. So how do I sync this combo box, so that I'm selecting from the pool of systems(for that particular customer selected in the first combo)? |
#2
|
|||
|
|||
Combo Box Sync
Confused,
Since you didn't provide any control names, I'll use my own... You should be selecting a key value in cboCustomers... like CustID. Your second combo, cboSystems, should use that value to filter it's RowSource query to only those systems associated to that unique CustID. Example... the CustID in cboSystems would have a criteria of... = Forms!YourFormName!cboCustomers Note: After selecting a CustID in cboCustomer, you should requery cboSystems, to always keep them in synch. On my website (below) I have a 97 and 2003 sample file called Synched Combos that shows how to set this up. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... I have a form called Testing. I only want customers to test that have Systems, so I select from the table CustomerSystemInventory in my Combo Box. The next Combo Box is what system they want to test. So how do I sync this combo box, so that I'm selecting from the pool of systems(for that particular customer selected in the first combo)? |
#3
|
|||
|
|||
Combo Box Sync
Al,
This is exactly what I need. But was hoping you could tell me how to expand it. In your DB you have states and cities. My table has regions and states. I have a subform set up to classify the customer by regions. Let's say I select region West. Then when I select the states combo I get NV, CA, NM, which mimics your DB. But how do I make it work for multiple regions e.g West and East are selected for the customer in first combo subform? And then make the second combo only show NV, CA, NM, NY, CT etc. "Al Campagna" wrote: Confused, Since you didn't provide any control names, I'll use my own... You should be selecting a key value in cboCustomers... like CustID. Your second combo, cboSystems, should use that value to filter it's RowSource query to only those systems associated to that unique CustID. Example... the CustID in cboSystems would have a criteria of... = Forms!YourFormName!cboCustomers Note: After selecting a CustID in cboCustomer, you should requery cboSystems, to always keep them in synch. On my website (below) I have a 97 and 2003 sample file called Synched Combos that shows how to set this up. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... I have a form called Testing. I only want customers to test that have Systems, so I select from the table CustomerSystemInventory in my Combo Box. The next Combo Box is what system they want to test. So how do I sync this combo box, so that I'm selecting from the pool of systems(for that particular customer selected in the first combo)? |
#4
|
|||
|
|||
Combo Box Sync
Confused,
Never tried that before... Let me try a few things, and get back to you asap. -- Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... Al, This is exactly what I need. But was hoping you could tell me how to expand it. In your DB you have states and cities. My table has regions and states. I have a subform set up to classify the customer by regions. Let's say I select region West. Then when I select the states combo I get NV, CA, NM, which mimics your DB. But how do I make it work for multiple regions e.g West and East are selected for the customer in first combo subform? And then make the second combo only show NV, CA, NM, NY, CT etc. "Al Campagna" wrote: Confused, Since you didn't provide any control names, I'll use my own... You should be selecting a key value in cboCustomers... like CustID. Your second combo, cboSystems, should use that value to filter it's RowSource query to only those systems associated to that unique CustID. Example... the CustID in cboSystems would have a criteria of... = Forms!YourFormName!cboCustomers Note: After selecting a CustID in cboCustomer, you should requery cboSystems, to always keep them in synch. On my website (below) I have a 97 and 2003 sample file called Synched Combos that shows how to set this up. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... I have a form called Testing. I only want customers to test that have Systems, so I select from the table CustomerSystemInventory in my Combo Box. The next Combo Box is what system they want to test. So how do I sync this combo box, so that I'm selecting from the pool of systems(for that particular customer selected in the first combo)? |
#5
|
|||
|
|||
Combo Box Sync
Confused,
Discussed this with some of the other MVPs. Given... a listbox named lstRegions, with values of "North", "South", "East", "West" an unbound text control named Regions a combobox named cboCities You'll need to change the cboCities RowSource "on the fly." This code will work... (use your own control names) ----------------- Private Sub lstRegions_AfterUpdate() Dim strTypes As String Dim varSelected As Variant If lstRegions.ItemsSelected.Count 0 Then For Each varSelected In lstRegions.ItemsSelected strTypes = strTypes & "'" & lstRegions.ItemData(varSelected) & "'," Next varSelected Regions = Left(strTypes, Len(strTypes) - 1) End If Me.cboCities.RowSource = "SELECT City, Region FROM tblRegions " & _ "WHERE Region In (" & Me.Regions & ")" cboCities.Requery End Sub -------------- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... Al, This is exactly what I need. But was hoping you could tell me how to expand it. In your DB you have states and cities. My table has regions and states. I have a subform set up to classify the customer by regions. Let's say I select region West. Then when I select the states combo I get NV, CA, NM, which mimics your DB. But how do I make it work for multiple regions e.g West and East are selected for the customer in first combo subform? And then make the second combo only show NV, CA, NM, NY, CT etc. "Al Campagna" wrote: Confused, Since you didn't provide any control names, I'll use my own... You should be selecting a key value in cboCustomers... like CustID. Your second combo, cboSystems, should use that value to filter it's RowSource query to only those systems associated to that unique CustID. Example... the CustID in cboSystems would have a criteria of... = Forms!YourFormName!cboCustomers Note: After selecting a CustID in cboCustomer, you should requery cboSystems, to always keep them in synch. On my website (below) I have a 97 and 2003 sample file called Synched Combos that shows how to set this up. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... I have a form called Testing. I only want customers to test that have Systems, so I select from the table CustomerSystemInventory in my Combo Box. The next Combo Box is what system they want to test. So how do I sync this combo box, so that I'm selecting from the pool of systems(for that particular customer selected in the first combo)? |
#6
|
|||
|
|||
Combo Box Sync
Al,
Thank you very much for effort on this. I'm setting this up on some combo boxes based exactly on your DB that I downloaded, and here is what is happening. When I select CBOTitles and say I select Operation Manager, all of the people with Operation Manager in their title appear in the CBOEmployee combo box, which is what you would expect. But after I select that person i.e Bob Jones, I cannot go back and select an Order Manager or a Test Manager etc. Well I can but only one appears per customer. Even all of the existing records allow me to only view the Operation Manager. E.g If I go back on the same customer and select a Test Manager after I have entered the Operations Manager, the Operations Manager disappears. Here are the details along with the RowSource statement. I'm trying to synchronize CboEmployeeName with CboTitles (these are bound combo boxes) on a subform, which is a continuous form that has Customer Names and to whom they are assigned. I.e: Customer A Account Manager John Smith Operation Manager Bob Jones Order Mangager Sally Sue Test Manager Sandy Shores etc. I have this statement on the row source of CboEmployeeNames SELECT DISTINCT Employees.EmployeeID, Employees.FullName, Employees.Title FROM Employees WHERE (((Employees.Title)=Forms!CLECS2MainForm![QryEmployeeAssignments subform1]!cbotitles)) ORDER BY Employees.FullName; I have a requery event procedure on CboTitles to keep the combos synchronized-like on your DB. Summary: The problem is that it only allows me to view or add one title and employee rather than four. I.e. it only allows me to view/add John Smith Account Manager per customer. And not in turn add Operation Manager Bob Jones. Is there a way to view/add multiple selections per customer? "Al Campagna" wrote: Confused, Discussed this with some of the other MVPs. Given... a listbox named lstRegions, with values of "North", "South", "East", "West" an unbound text control named Regions a combobox named cboCities You'll need to change the cboCities RowSource "on the fly." This code will work... (use your own control names) ----------------- Private Sub lstRegions_AfterUpdate() Dim strTypes As String Dim varSelected As Variant If lstRegions.ItemsSelected.Count 0 Then For Each varSelected In lstRegions.ItemsSelected strTypes = strTypes & "'" & lstRegions.ItemData(varSelected) & "'," Next varSelected Regions = Left(strTypes, Len(strTypes) - 1) End If Me.cboCities.RowSource = "SELECT City, Region FROM tblRegions " & _ "WHERE Region In (" & Me.Regions & ")" cboCities.Requery End Sub -------------- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... Al, This is exactly what I need. But was hoping you could tell me how to expand it. In your DB you have states and cities. My table has regions and states. I have a subform set up to classify the customer by regions. Let's say I select region West. Then when I select the states combo I get NV, CA, NM, which mimics your DB. But how do I make it work for multiple regions e.g West and East are selected for the customer in first combo subform? And then make the second combo only show NV, CA, NM, NY, CT etc. "Al Campagna" wrote: Confused, Since you didn't provide any control names, I'll use my own... You should be selecting a key value in cboCustomers... like CustID. Your second combo, cboSystems, should use that value to filter it's RowSource query to only those systems associated to that unique CustID. Example... the CustID in cboSystems would have a criteria of... = Forms!YourFormName!cboCustomers Note: After selecting a CustID in cboCustomer, you should requery cboSystems, to always keep them in synch. On my website (below) I have a 97 and 2003 sample file called Synched Combos that shows how to set this up. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Confused" wrote in message ... I have a form called Testing. I only want customers to test that have Systems, so I select from the table CustomerSystemInventory in my Combo Box. The next Combo Box is what system they want to test. So how do I sync this combo box, so that I'm selecting from the pool of systems(for that particular customer selected in the first combo)? |
Thread Tools | |
Display Modes | |
|
|