473,320 Members | 1,921 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,320 software developers and data experts.

Combo Boxes on subform not syncronising

Hi there, this question has probably being asked many times, but it has got me stuck.
I have a table for customers that includes a control for the area that they reside in.
There is also a table for contractors that shows the areas they work in. A table called tblArea has the only two options, "North" and "South" which is linked to the customer and contractor table. The customer can only select one option but the contractor can be "North", "South" or both. This is done by ticking the tick boxes in the drop down list (combo box).
All this is on a form with a subform (subJobsTable).
When the user enters the customer detail on the mainform and selects the area for the customer, I need the list of contractors (on the subform) restricted to those that work in the are of the customer.
I have the following code in the After_update event of the area control on the main form:
Dim sContracorSource As String
sContractorSource = "SELECT [ContractorDetail].[businessID],[ContractorDetail].[Area.value],[ContractorDetail].[BusinessName] " & _
"FROM ContractorDetail " & _
"WHERE [ContractorDetail].[Area.value] = " & Me.Area
Me.subJobsTable.Form.Contractor.RowSource = sContractorSource

With this setup I am getting the following error:
"Invalid bracketing of name '[ContractorDetail].[Area.Value]'"
Am I missing something obvious here?
Thanks for your help.
David
Mar 18 '16 #1
3 871
anvidc
28
need to use quotation mark if string
Expand|Select|Wrap|Line Numbers
  1. WHERE [ContractorDetail].[Area.value] = '" & Me.Area & "'"
Mar 18 '16 #2
mbizup
80 64KB
The quotation mark delimeter for text suggestion is excellent and accurate. However, I believe the immediate cause of the 'invalid bracketing' error is your use of the VALUE property.

The Value property is part of VBA, not SQL syntax. If you are trying to refer to a field named Area in a query or table named ContractorDetail, the syntax should be (note that I've dropped the Value property)

Expand|Select|Wrap|Line Numbers
  1. WHERE [ContractorDetail].[Area]
Additionally, since your table and field names contain no spaces,special characters or reserved words, the square brackets are actually unneeded. This is cleaner, and syntactically valid:

Expand|Select|Wrap|Line Numbers
  1. WHERE ContractorDetail.Area
Removing all instances of the .Value property should resolve your invalid bracketing error... at which point the suggestion above for embedded quotes will resolve any data type mismatch errors you may get for improperly delimited TEXT data.
Mar 21 '16 #3
Thanks for the replies. I have it working in part now, but the result in the second combo box is showing the area and not the business name. How do I get BusinessName in the second combo box? My code is :
Dim sContracorSource As String
sContractorSource = "SELECT [ContractorDetail].[BusinessID],tblArea.area,[ContractorDetail].[BusinessName] " & _
"FROM tblArea INNER JOIN ContractorDetail ON tblArea.ID = ContractorDetail.Area.Value " & _
"WHERE [ContractorDetail].Area.value = " & Me.Area.Value
Me.subJobsTable.Form.Contractor.RowSource = sContractorSource

Thanks again.
Mar 22 '16 #4

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

Similar topics

1
by: Stephanie Wright | last post by:
I'm having a probem with my 3rd and 4th boxes not showing the correct data to pick. My database is normalized and I have Me! statements for each combo box in the After Update but something is...
2
by: CSDunn | last post by:
Hello, In an Access 2000 ADP subform, I have a set of two Radio buttons in a single group that are bound to a field called 'Completed'. The Completed field comes from an SQL Server 2000 table, is...
3
by: Mike Jakes | last post by:
I hope that someone can offer a little advice on this one - I've searched the group but can't find an answer. I think that I'm doing something really stupid or missing something trivial, but see...
7
by: badboybrown | last post by:
I know that this is an age old question and it has been approached a few times, but I've hit a wall and require some help. I have various combo boxes that contain values that are both "current"...
3
by: Phil | last post by:
I've posted this a few times without any luck, hoping for some fresh ideas on this. I'm pretty certain this can be done. I've been using a micrsoft template to create a supplier database called...
5
by: NateDogg | last post by:
This is what I am using to filter my combo box based on what is selected in the previous box. Open the properties for the second combo box. Next to the 'Row Source' property, click on the '...'. ...
6
by: Dave | last post by:
I want to put the information that the user selects in my combo boxes into a subform that lies on the same form as the combo boxes. Thanks for your help already, Dave
1
by: Dave | last post by:
Hello all, First I'd like to apologize...This post was meant to be put in my previous post, but I tried many times without success to reply within my previous post. Now here goes... I have a...
5
by: samdev | last post by:
I have created two combo boxes in a subform.... For example 1. Combo Box State 2. Combo Box City 3. When a state from the Combo Box State is selected, the City combo box updates to reflect...
1
by: Yasmeen Pannu | last post by:
HI, I am new to MS Access and stuck in a problem from few days. My Question: I have 3 combo box (Submitter, date from and date to) on my form and i want that when i select values from each of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
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...
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.