Combo Box Sync

[Confused]

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)?

 

[Al Campagna]

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]

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]

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."

 

[Al Campagna]

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]

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?