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

Syntax error when sorting subform

P: 122
I'm trying to make a very simple order form. I have two tables - OrderHeader has information specific to the order itself, such as company name and order date. OrderDetail contains details such as item number, quantity and price, and links to OrderHeader on the OrderID field. Such a setup that has probably been done thousands of times since the history of the world began.

I created a form bound to OrderHeader and put in a SubForm based on OrderDetail, linking by OrderID. I haven't changed a single property since creating the form; I want to get the basic data model working and then I'll go back and customize how everything looks and works.

As it is now, if the user clicks in the subform to sort or filter anything, I get the following error:

Expand|Select|Wrap|Line Numbers
  1. Syntax error in query expression '[SELECT [OrderDetail].[OrderNumber], [OrderDetail].[LineID], [OrderDetail].[Item], [OrderDetail].[Qty], [OrderDetail].[Price] FROM OrderDetail; ].Order'.
It looks like the record source of the sub form is being mangled with a partial field name being added to the end of the query. I'm not sure how to fix that because I'm not sure what's causing it. Has anyone encountered this before or know what the problem might be?

This is in Access 2007.
Feb 19 '10 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 234
Can't say I've seen that one before. Have you checked your recordsource to make sure the extra text isn't actually on there?
Feb 19 '10 #2

Expert Mod 15k+
P: 31,494
What is the subform bound to?
Feb 21 '10 #3

P: 122
topher23 - I did check the recordsource, and I copied it into a new query to make sure there were no other errors. I tried clearing the field and retyping it, too, but the error is still happening.

NeoPa - The subform is bound to the OrderDetail table. The record source is just a simple select statement displaying the items for a given order.

It should be noted that if I hit 'OK' on the error message, then the filter menu appears and I can sort the data or apply filters as normal. It's just when you initially click that the error appears.
Feb 22 '10 #4

Expert Mod 15k+
P: 31,494
It can be one or the other but not both. Perhaps if you pasted exactly what is in the property value that would help. Please don't tidy up any spaces or anything. Just paste as is.
I have to say, without being entirely certain in the circumstances, that this makes me feel the error message is due to something you have yet to tell us of.
Feb 23 '10 #5

P: n/a
I had the same issue... Not sure if you figured it out, but if you turn the Data Properties --> Filter Lookup to Never for the field you're trying to sort it seems to fix the syntax error.
Nov 5 '10 #6

Post your reply

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