Combo Boxes on subform not syncronising

[veldskoen]

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 sContracorSourc e As String
sContractorSour ce = "SELECT [ContractorDetai l].[businessID],[ContractorDetai l].[Area.value],[ContractorDetai l].[BusinessName] " & _
"FROM ContractorDetai l " & _
"WHERE [ContractorDetai l].[Area.value] = " & Me.Area
Me.subJobsTable .Form.Contracto r.RowSource = sContractorSour ce

With this setup I am getting the following error:
"Invalid bracketing of name '[ContractorDetai l].[Area.Value]'"
Am I missing something obvious here?
Thanks for your help.
David

[anvidc]

need to use quotation mark if string

WHERE [ContractorDetail].[Area.value] = '" & Me.Area & "'"

[mbizup]

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 ContractorDetai l, the syntax should be (note that I've dropped the Value property)

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:

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.

[veldskoen]

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 sContracorSourc e As String
sContractorSour ce = "SELECT [ContractorDetai l].[BusinessID],tblArea.area,[ContractorDetai l].[BusinessName] " & _
"FROM tblArea INNER JOIN ContractorDetai l ON tblArea.ID = ContractorDetai l.Area.Value " & _
"WHERE [ContractorDetai l].Area.value = " & Me.Area.Value
Me.subJobsTable .Form.Contracto r.RowSource = sContractorSour ce

Thanks again.