By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,092 Members | 1,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,092 IT Pros & Developers. It's quick & easy.

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

P: 54
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.

Expand|Select|Wrap|Line Numbers
  1. i.e.
  2. CustomerID....Customer Name....Department
  3. ....1.........SomeHospital1......dep1
  4. ....2.........SomeHospital1......dep2
  5. ....3.........SomeHospital1......dep3
  6. ....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:


Expand|Select|Wrap|Line Numbers
  1. Dim sDepartment As String
  2.  
  3. sDepartment = "SELECT tblCustomers.Department FROM tblCustomers WHERE [tblCustomers.Company Name] =" & Me.cboSelectCustomer.Value
  4.  
  5. Me.cboSelectDepartment.RowSource = sDepartment
  6.  
  7. Me.cboSelectDepartment.Requery
Yet every time I click on the 2nd ComboBox (cboSelectDepartment) 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.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
Dec 10 '06 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,560
Replace your line setting sDepartment with :
Expand|Select|Wrap|Line Numbers
  1. sDepartment = "SELECT [Department] " & _
  2.               "FROM tblCustomers " & _
  3.               "WHERE tblCustomers.[Company Name]='" & _
  4.               Me.cboSelectCustomer & "'"
Use Copy / Paste to get it all exactly right.
Dec 10 '06 #2

NeoPa
Expert Mod 15k+
P: 31,560
FYI You had [] around the whole reference rather than individual reference elements ([tblCustomers.Company Name] =).
The selection had no quotes (') around the value as passed to SQL (...=" & Me.cboSelectCustomer.Value).
The .Value at the end was unnecessary.
Dec 10 '06 #3

P: 54
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"..."tblCustomers.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
Dec 10 '06 #4

P: 54
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.cboSelectCustomer & "'"

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
Dec 10 '06 #5

P: 54
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
Dec 10 '06 #6

NeoPa
Expert Mod 15k+
P: 31,560
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.)
Dec 10 '06 #7

P: 54
Thanks NeoPa
For the reference!!
Dec 12 '06 #8

NeoPa
Expert Mod 15k+
P: 31,560
No Problems.
There should be a new 'Articles' section up shortly and they will be in there and more visible.
Dec 12 '06 #9

P: 54
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!!
Dec 16 '06 #10

NeoPa
Expert Mod 15k+
P: 31,560
Great look forward to seeing more of it as it is still confusing to me!!
We're planning on a fairly comprehensive section for Access.
It will depend on what gets contributed mind, but we're very hopeful of a really useful section when it's up and running.
Dec 17 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.