473,322 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
10 2686
NeoPa
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
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
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
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
32,556 Expert Mod 16PB
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
Thanks NeoPa
For the reference!!
Dec 12 '06 #8
NeoPa
32,556 Expert Mod 16PB
No Problems.
There should be a new 'Articles' section up shortly and they will be in there and more visible.
Dec 12 '06 #9
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
32,556 Expert Mod 16PB
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

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

Similar topics

0
by: Ken Arway | last post by:
Using .Net framework 1.1 I've got a Windows Forms application with a tab control having four tab pages. TabPage4 contains a Combobox and a TextBox, which are bound to the same XML file as a...
6
by: Doug Bell | last post by:
Hi I have a datagrid with a combo box, I need to populate the combo with data dependant on the record value. eg for record 1, field Warehouse = 2R so combo would allow selection of locations...
2
by: Richard | last post by:
I have a form with tabpages on them. On one tabpage I have three comboboxes all of which derive their datasource from the same table:- TimePeriodID TimePeriod 1. Day(s) 2. Week(s) 3....
30
by: dbuchanan | last post by:
ComboBox databindng Problem == How the ComboBox is setup and used: My comboBox is populated by a lookup table. The ValueMember is the lookup table's Id and the DisplayMember is the text from a...
4
by: jon f kaminsky | last post by:
Hi- I've seen this problem discussed a jillion times but I cannot seem to implement any advice that makes it work. I am porting a large project from VB6 to .NET. The issue is using the combo box...
6
by: dbuchanan | last post by:
VS2005 I've been reading all the help I can on the topic (MSDN, other) but I can't make sense of this. Desired behavior; The user is to choose from the displayed list of the databound combobox...
2
by: shumaker | last post by:
I have a combobox that is very much like the one found in the RSS project here: http://msdn.microsoft.com/vstudio/express/visualCSharp/learning/ My projectNameComboBox basically is filled with a...
1
by: Dale | last post by:
I have a user control on a Windows Form that, in its Load event handler, calls a method from a class library to initialize an array of objects that are then used to populate a ComboBox. When I...
12
by: Gerhard | last post by:
This is bizarre... Im having problems with the combobox AfterUpdate event: Im running Access 2003. I created an unbound combobox with 3 columns on a form. The Row Source is from a table....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.