The first comboBox gives me a list of customers to select from.
I would like to have the second comboBox populated with the names of Departments. I only want the departments listed that match the Customer selected in the first combobox.
Unfortunatly the original database that the tables were drawn from (SQLServer) has one big Customers Table. It has a CustomerID for every Customer. There are multiple 'Company Name' entries that are the same in the table with different departments.
Expand|Select|Wrap|Line Numbers
- i.e.
- CustomerID....Customer Name....Department
- ....1.........SomeHospital1......dep1
- ....2.........SomeHospital1......dep2
- ....3.........SomeHospital1......dep3
- ....4.........Another Hos........dep1
So I use a SELECT DISTINCT statement to load ComboBox 1 with values. (no repeat Customer Name's loaded).
In ComboBox1 After Update event I have put the following:
Expand|Select|Wrap|Line Numbers
- Dim sDepartment As String
- sDepartment = "SELECT tblCustomers.Department FROM tblCustomers WHERE [tblCustomers.Company Name] =" & Me.cboSelectCustomer.Value
- Me.cboSelectDepartment.RowSource = sDepartment
- Me.cboSelectDepartment.Requery
Syntax error (missing operator) in query expression '[tblCustomers.Company Name] = Auckland City Hospital' (That is if I have selected Auckland City Hospital in the first combobox.
If anyone has any ideas how to do this I would be grateful, I cant use the CustomerID to sort unfortunatly. So far I have spent about 2 hours this afternoon reading books and searching the net trying to find why it doesn't work.
Many Thanks
Mike