Loading a combobox with values that are dependant on another combobox.

[ZaphodBBB]

HI I have a form with 2 comboBoxes.

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.

i.e.
CustomerID....Customer Name....Department
....1.........SomeHospital1......dep1
....2.........SomeHospital1......dep2
....3.........SomeHospital1......dep3
....4.........Another Hos........dep1

I Didn't write the old SQL database!!

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:

Dim sDepartment As String
 
sDepartment = "SELECT tblCustomers.Department FROM tblCustomers WHERE [tblCustomers.Company Name] =" & Me.cboSelectCustomer.Value
 
Me.cboSelectDepartment.RowSource = sDepartment
 
Me.cboSelectDepartment.Requery

Yet every time I click on the 2nd ComboBox (cboSelectDepar tment) i get a syntax error. I have played around with brackets etc but no go. This is the error I keep getting.

Syntax error (missing operator) in query expression '[tblCustomers.Co mpany 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

[NeoPa]

Replace your line setting sDepartment with :

sDepartment = "SELECT [Department] " & _
              "FROM tblCustomers " & _
              "WHERE tblCustomers.[Company Name]='" & _
              Me.cboSelectCustomer & "'"

Use Copy / Paste to get it all exactly right.

[NeoPa]

FYI You had [] around the whole reference rather than individual reference elements ([tblCustomers.Co mpany Name] =).
The selection had no quotes (') around the value as passed to SQL (...=" & Me.cboSelectCus tomer.Value).
The .Value at the end was unnecessary.

[ZaphodBBB]

HI NeoPa

Thanks again for your help. I tried it exactly as you have it and what happens is that after I have selected a Value from the first ComboBox and go to click on the arrow in the second ComboBox I get a PopUp box on screen asking me to
"Enter Parameter Value"..."tblCu stomers.Company Name".

Just for interest sake if I do enter a Customers Name in the pop up box and then click O.K. it loads the second combo box with All possible departments not just those from the Customer that has been selected in the first combo.

Again thanks so much for your assistance.

Regards
Mike

[ZaphodBBB]

HI NeoPa

GOT IT!!!

Sorry your code is 100% as usual!

Problem was when I was opening the Customers Table, at the top where the field names are it was called "Customer Name" out of frustration I went into design view and the Field is called (As might have expected) CustomerName. Feed that into the expression you gave me and all is well.

Is there anywhere (I have about 4 good solid books on Access) any solid info on the Syntax for these type of things? None of the books I have spells it out , matter of fact they barely mention the subject at all. They go into SQL and creating Queries and using the Query Design tool in Access but nothing useful about syntax. Before I typed in your version I swore it wouldn't work! What possible part do the single quotes play or apostrephees play? And why the & sign after the .....Me.cboSele ctCustomer & "'"

Again thanks for all your assistance. There has to be somewhere not just a list of correct syntax but some sort of guide to How and Why...Surely?

Regards

[ZaphodBBB]

Never realized until now that you could have a Caption for a Field Name that is different from the Field Name itself. Never done any access courses so excuse the ignorance please!

Thanks

[NeoPa]

Never realized until now that you could have a Caption for a Field Name that is different from the Field Name itself. Never done any access courses so excuse the ignorance please!

Thanks

To rename fields :
In SQL just say Formula AS NewName.
In the design grid say NewName: Formula.

For quote problems see (Quotes (') and Double-Quotes (") - Where and When to use them.)

[ZaphodBBB]

Thanks NeoPa
For the reference!!

[NeoPa]

No Problems.
There should be a new 'Articles' section up shortly and they will be in there and more visible.

[ZaphodBBB]

No Problems.
There should be a new 'Articles' section up shortly and they will be in there and more visible.

Great look forward to seeing more of it as it is still confusing to me!!