Syntax error when sorting subform

[gershwyn]

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:

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.

[topher23]

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?

[NeoPa]

What is the subform bound to?

[gershwyn]

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.

[NeoPa]

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 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.

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.

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.

[Chris Chagdes]

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.