Access 2007 subform sort

[wwwords]

I have a form in Access 2007 containing a subform. The control source
of the latter is

SELECT DISTINCTROW [Catchwords].[Catchword], [Catchwords].[Part of
speech], [Catchwords].[Explanation], [Catchwords].[Catchword ID],
[Catchwords].[Citation ID] FROM [Catchwords];

which is undoubtedly correct.

However, if I click on the down arrow on the subform header (the one
that should bring up the sort and filter menu) I get this error:

Syntax error in query expression '[SELECT DISTINCTROW [Catchwords]].
[Catchword], [Catchwords].[Part of speech], [Catchwords].
[Explanation], [Catchwords].[Catchword ID], [Catchwords].[[Citation
ID] FROM [Catchwords];].[Citation ID]'.

This is clearly incorrect SQL! Note the extra square brackets and the
extra item after the semicolon at the end.

Interestingly, this only happens on the header for "Catchword" and
"Part of speech", not for "Explanation", presumably because the last
of these is a memo field.

Can anyone suggest a reason for this?

[Allen Browne]

Forms don't have a Control Source, so I will assume this SQL statement is in
the subform's Record Source property.

If Access is that confused about things, Name AutoCorrect is the first
culprit that comes to mind. Try this sequence:

1. Open the subform directly in design view, and delete the RecordSource so
the form is unbound. Save. Close.

2. Open the main form in design view, and delete the Link Master Fields and
Link Child Fields entries from the properties of the subform control. Save
Close.

3. Uncheck the boxes under:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

4. Compact the database:
Office Button | Manage | Compact/Repair

5. Restore the RecordSource of the subform, by pasting in the SQL statement
again. Test the subform as a stand alone form to make sure it's working
correctly.

6. Restore the Link Master Fields and Link Child Fields again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"wwwords" <mi*************@gmail.comwrote in message
news:11*********************@q2g2000cwa.googlegrou ps.com...

>I have a form in Access 2007 containing a subform. The control source
of the latter is

SELECT DISTINCTROW [Catchwords].[Catchword], [Catchwords].[Part of
speech], [Catchwords].[Explanation], [Catchwords].[Catchword ID],
[Catchwords].[Citation ID] FROM [Catchwords];

which is undoubtedly correct.

However, if I click on the down arrow on the subform header (the one
that should bring up the sort and filter menu) I get this error:

Syntax error in query expression '[SELECT DISTINCTROW [Catchwords]].
[Catchword], [Catchwords].[Part of speech], [Catchwords].
[Explanation], [Catchwords].[Catchword ID], [Catchwords].[[Citation
ID] FROM [Catchwords];].[Citation ID]'.

This is clearly incorrect SQL! Note the extra square brackets and the
extra item after the semicolon at the end.

Interestingly, this only happens on the header for "Catchword" and
"Part of speech", not for "Explanation", presumably because the last
of these is a memo field.

Can anyone suggest a reason for this?

[wwwords]

Forms don't have a Control Source, so I will assume this SQL statement is in

the subform's Record Source property.

You're right, of course.

If Access is that confused about things, Name AutoCorrect is the first
culprit that comes to mind. Try this sequence:

Unfortunately that doesn't resolve the problem, as the "Track name
AutoCorrect info" option is already turned off.

I've just tried recreating the subform using the Access wizard. This
produces the same SQL code for the Record Source (hardly a surprise)
but the the error message has changed slightly (note the extra square
brackets are now only around one item, there's no final semicolon and
the FROM parameter should correctly be just "[Catchwords]" as the name
of the table; so wonder the program says there's a syntax error!):

Syntax error in query expression '[SELECT DISTINCTROW [Catchwords]].
[Catchword], [Catchwords].[Part of speech], [Catchwords].
[Explanation], [Catchwords].[Catchword ID], [Catchwords].[[Citation
ID] FROM [Catchwords][Citation ID]'.

It looks as though Access is itself wrongly reading the Record Source
SQL!

[Allen Browne]

What Access is doing here makes no sense.

What happens if you create a query, save it, verify it's working, and then
use the name of the saved query as the RecordSource of the subform?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"wwwords" <mi*************@gmail.comwrote in message
news:11**********************@s48g2000cws.googlegr oups.com...

>Forms don't have a Control Source, so I will assume this SQL statement is
in
the subform's Record Source property.

You're right, of course.

>If Access is that confused about things, Name AutoCorrect is the first
culprit that comes to mind. Try this sequence:

Unfortunately that doesn't resolve the problem, as the "Track name
AutoCorrect info" option is already turned off.

I've just tried recreating the subform using the Access wizard. This
produces the same SQL code for the Record Source (hardly a surprise)
but the the error message has changed slightly (note the extra square
brackets are now only around one item, there's no final semicolon and
the FROM parameter should correctly be just "[Catchwords]" as the name
of the table; so wonder the program says there's a syntax error!):

Syntax error in query expression '[SELECT DISTINCTROW [Catchwords]].
[Catchword], [Catchwords].[Part of speech], [Catchwords].
[Explanation], [Catchwords].[Catchword ID], [Catchwords].[[Citation
ID] FROM [Catchwords][Citation ID]'.

It looks as though Access is itself wrongly reading the Record Source
SQL!

[wwwords]

On Feb 11, 2:53 pm, "Allen Browne" <[e-mail]:

What Access is doing here makes no sense.

What happens if you create a query, save it, verify it's working, and then
use the name of the saved query as the RecordSource of the subform?

Doing that resolves the problem! Thanks for the suggestion. Might this
be a bug in Access 2007?

[wwwords]

A quick follow-up question. Is it possible to disable the down arrow
on the subform header? The menu that comes up isn't relevant to our
needs and will only cause confusion!

[Allen Browne]

I'm not clear what you mean by the down arrow in the subform header.

Is this the shortcut menu (right-click)? If so, you could open the subform
in design view, and set its Shortcut Menu property to No.

Or is this a control you added to the Form Header section of the form in the
subform control?

Or?

At this stage, I would not assume it's an A2007 bug.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"wwwords" <mi*************@gmail.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.com...

>A quick follow-up question. Is it possible to disable the down arrow
on the subform header? The menu that comes up isn't relevant to our
needs and will only cause confusion!

[wwwords]

On Feb 12, 12:45 am, "Allen Browne" <[e-mail]>
wrote:

I'm not clear what you mean by the down arrow in the subform header.

Is this the shortcut menu (right-click)? If so, you could open the subform
in design view, and set its Shortcut Menu property to No.

Many thanks again. That was the one I meant.