A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Order By/Filter clause corrupted with aliases



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2010, 12:57 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Order By/Filter clause corrupted with aliases

I have a datasheet form (actually it's a subform, but this happens if you
look at the subform on its own). It has a SQL query for a record source
which joins three tables, and uses aliases (AS) for those tables. The
displayed field from two of the tables has the same name in each. (The SQL
is generated from table definitions and it has to handle the possibility that
the same table will be referenced in two different joins (example
employee-location, employee-manager-location), so aliasing is not
optional.) All works fine.

Then you use the built-in sorting feature to sort one of the fields (one
which shares a name with another joined table) ... still fine. Go into
design view and look at Order By, and it looks like:
[Lookup_control].[field]
That's OK.

Then hit save, and Access takes it upon itself to rewrite the clause into
something it can't recognise:
Lookup_control.[alias.field]

Doh. Presumably it thinks the alias is necessary because of the shared
name, though of course it isn't, because we have the control name. And why
are there two separate pieces of code writing that clause?

You can carry on, and switch between form view and design view no problem.
But when you close and reopen the form, you get:
Invalid bracketing of name 'Lookup_control.[alias.field]'
and the form will not open. (As a subform, it comes up blank.)

Filtering triggers the same problem.

Clearly this is a bug, but I don't know how to report it. I could read the
Order By and Filter clauses on load and try and patch them up but that seems
a bit horrible. Any ideas?
Ads
  #2  
Old May 14th, 2010, 03:05 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Order By/Filter clause corrupted with aliases

By the way this is Access 2007.

I just wrote a simple cleanup routine (within any pair of square brackets,
split by periods, and replace the contents with the last token), but the
syntax check occurs before Open and Load. Which means there is no workaround
- if you do the edit at any other time then you have to save the form ... and
Access kindly screws the clause up again as it saves.

I'm surprised this hasn't come up before - surely there are other people out
there who use datasheets, and not completely trivial joins? With users who
like to sort and filter?

"Boggle" wrote:

I have a datasheet form (actually it's a subform, but this happens if you
look at the subform on its own). It has a SQL query for a record source
which joins three tables, and uses aliases (AS) for those tables. The
displayed field from two of the tables has the same name in each. (The SQL
is generated from table definitions and it has to handle the possibility that
the same table will be referenced in two different joins (example
employee-location, employee-manager-location), so aliasing is not
optional.) All works fine.

Then you use the built-in sorting feature to sort one of the fields (one
which shares a name with another joined table) ... still fine. Go into
design view and look at Order By, and it looks like:
[Lookup_control].[field]
That's OK.

Then hit save, and Access takes it upon itself to rewrite the clause into
something it can't recognise:
Lookup_control.[alias.field]

Doh. Presumably it thinks the alias is necessary because of the shared
name, though of course it isn't, because we have the control name. And why
are there two separate pieces of code writing that clause?

You can carry on, and switch between form view and design view no problem.
But when you close and reopen the form, you get:
Invalid bracketing of name 'Lookup_control.[alias.field]'
and the form will not open. (As a subform, it comes up blank.)

Filtering triggers the same problem.

Clearly this is a bug, but I don't know how to report it. I could read the
Order By and Filter clauses on load and try and patch them up but that seems
a bit horrible. Any ideas?

  #3  
Old May 14th, 2010, 03:51 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Order By/Filter clause corrupted with aliases

It gets worse. I removed the aliases just to see what happened. I am still
referencing two fields with the same name in different tables. The Order By
clause is still fine until you save:
[Lookup_control].[field]

and when you save it goes to:
Lookup_control.[table1.field]

where table1 is always the first joined table with that field name. That
that still works if you sort in the column relating to the first joined table
(so apparently it isn't a syntax problem any more). But if you sort in the
column from the second joined table, then the control doesn't refer to
table1, and you get the invalid bracket error. (Though clearly it isn't
the brackets which are the problem.)

By the way my controls are combo boxes so you can drop down and see a list
of all valid entries in the column. But I can't see what that would have to
do with it.

It all worked fine in Access 2003.

"Boggle" wrote:

By the way this is Access 2007.

I just wrote a simple cleanup routine (within any pair of square brackets,
split by periods, and replace the contents with the last token), but the
syntax check occurs before Open and Load. Which means there is no workaround
- if you do the edit at any other time then you have to save the form ... and
Access kindly screws the clause up again as it saves.

I'm surprised this hasn't come up before - surely there are other people out
there who use datasheets, and not completely trivial joins? With users who
like to sort and filter?

"Boggle" wrote:
...


  #4  
Old May 14th, 2010, 04:15 PM posted to microsoft.public.access.forms
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Order By/Filter clause corrupted with aliases

Have you tried to do the SQL in a query rather than in the form?

--
Build a little, test a little.


"Boggle" wrote:

It gets worse. I removed the aliases just to see what happened. I am still
referencing two fields with the same name in different tables. The Order By
clause is still fine until you save:
[Lookup_control].[field]

and when you save it goes to:
Lookup_control.[table1.field]

where table1 is always the first joined table with that field name. That
that still works if you sort in the column relating to the first joined table
(so apparently it isn't a syntax problem any more). But if you sort in the
column from the second joined table, then the control doesn't refer to
table1, and you get the invalid bracket error. (Though clearly it isn't
the brackets which are the problem.)

By the way my controls are combo boxes so you can drop down and see a list
of all valid entries in the column. But I can't see what that would have to
do with it.

It all worked fine in Access 2003.

"Boggle" wrote:

By the way this is Access 2007.

I just wrote a simple cleanup routine (within any pair of square brackets,
split by periods, and replace the contents with the last token), but the
syntax check occurs before Open and Load. Which means there is no workaround
- if you do the edit at any other time then you have to save the form ... and
Access kindly screws the clause up again as it saves.

I'm surprised this hasn't come up before - surely there are other people out
there who use datasheets, and not completely trivial joins? With users who
like to sort and filter?

"Boggle" wrote:
...


  #5  
Old May 14th, 2010, 04:26 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Order By/Filter clause corrupted with aliases

Thanks for the suggestion - but it didn't help. After saving, Order By
becomes:
Lookup_control.[query.table1.field]

and again, table1 is always the first table joined in the query, and only
matches up with the controls that refer to that table.

"KARL DEWEY" wrote:

Have you tried to do the SQL in a query rather than in the form?

--
Build a little, test a little.


"Boggle" wrote:

It gets worse. I removed the aliases just to see what happened. I am still
referencing two fields with the same name in different tables. The Order By
clause is still fine until you save:
[Lookup_control].[field]

and when you save it goes to:
Lookup_control.[table1.field]

where table1 is always the first joined table with that field name. That
that still works if you sort in the column relating to the first joined table
(so apparently it isn't a syntax problem any more). But if you sort in the
column from the second joined table, then the control doesn't refer to
table1, and you get the invalid bracket error. (Though clearly it isn't
the brackets which are the problem.)

By the way my controls are combo boxes so you can drop down and see a list
of all valid entries in the column. But I can't see what that would have to
do with it.

It all worked fine in Access 2003.

"Boggle" wrote:

By the way this is Access 2007.

I just wrote a simple cleanup routine (within any pair of square brackets,
split by periods, and replace the contents with the last token), but the
syntax check occurs before Open and Load. Which means there is no workaround
- if you do the edit at any other time then you have to save the form ... and
Access kindly screws the clause up again as it saves.

I'm surprised this hasn't come up before - surely there are other people out
there who use datasheets, and not completely trivial joins? With users who
like to sort and filter?

"Boggle" wrote:
...


  #6  
Old May 14th, 2010, 11:46 PM posted to microsoft.public.access.forms
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Order By/Filter clause corrupted with aliases

Post the query SQL by opening in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

--
Build a little, test a little.


"Boggle" wrote:

Thanks for the suggestion - but it didn't help. After saving, Order By
becomes:
Lookup_control.[query.table1.field]

and again, table1 is always the first table joined in the query, and only
matches up with the controls that refer to that table.

"KARL DEWEY" wrote:

Have you tried to do the SQL in a query rather than in the form?

--
Build a little, test a little.


"Boggle" wrote:

It gets worse. I removed the aliases just to see what happened. I am still
referencing two fields with the same name in different tables. The Order By
clause is still fine until you save:
[Lookup_control].[field]

and when you save it goes to:
Lookup_control.[table1.field]

where table1 is always the first joined table with that field name. That
that still works if you sort in the column relating to the first joined table
(so apparently it isn't a syntax problem any more). But if you sort in the
column from the second joined table, then the control doesn't refer to
table1, and you get the invalid bracket error. (Though clearly it isn't
the brackets which are the problem.)

By the way my controls are combo boxes so you can drop down and see a list
of all valid entries in the column. But I can't see what that would have to
do with it.

It all worked fine in Access 2003.

"Boggle" wrote:

By the way this is Access 2007.

I just wrote a simple cleanup routine (within any pair of square brackets,
split by periods, and replace the contents with the last token), but the
syntax check occurs before Open and Load. Which means there is no workaround
- if you do the edit at any other time then you have to save the form ... and
Access kindly screws the clause up again as it saves.

I'm surprised this hasn't come up before - surely there are other people out
there who use datasheets, and not completely trivial joins? With users who
like to sort and filter?

"Boggle" wrote:
...

  #7  
Old May 17th, 2010, 10:16 AM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Order By/Filter clause corrupted with aliases

Hi Karl,

I just realised that after writing the query, I put SELECT * FROM query in
the Record Source, instead of what I guess you meant - setting the Record
Source directly equal to the query name. In that case it seems to leave the
initial Order By alone, so everything works OK.

Thanks a lot for the suggestion - I can make it work now.

There is still a bug, and it seems quite easy to recreate. It seems as if
the effect of bundling up the SQL into a query just hides stuff so that the
incorrect rewriting is skipped.

Here is the query after removing the aliases:
SELECT * FROM (BatFac INNER JOIN Batch ON BatFac.Batch=Batch.Batch) INNER
JOIN Facility ON BatFac.Facility=Facility.Facility;

Nothing complicated there, I'd say. The Batch and Facility fields of BatFac
are not null, and there are foreign key relationships defined between the
tables.

I have two list boxes (among other things) on the form for Batch and
Facility. The Batch one has
Control Source = BatFac.Batch
Row Source = SELECT OABDesc, [Batch] FROM Batch;
Bound Column = 2
Column Count = 2
And the Facility list box is just the same with Facility for Batch everywhere.

The OABDesc field holds a textual description of the item, so you see the
description in the datasheet, but you can click in the cell and see a
dropdown list of descriptions and codes (primary keys) together. The same
field name is used in both Batch and Facility.

With the form Record Source set directly to the SQL at the top, after
sorting the Facility column, if you go into Design View, you see
Order By = [Lookup_Facility].[OABDesc]

If you hit save at this point then this is rewritten to
Lookup_Facility.[Batch.OABDesc]
Oops.

The same happens if you just close the form without entering Design Mode or
hitting save. If Order By On Load is Yes, then when you try to open the form
again, you get
Invalid bracketing of name 'Lookup_Facility.[Batch.OABDesc]'.
and the form does not open.


"KARL DEWEY" wrote:

Post the query SQL by opening in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

--
Build a little, test a little.


"Boggle" wrote:

Thanks for the suggestion - but it didn't help. After saving, Order By
becomes:
Lookup_control.[query.table1.field]

and again, table1 is always the first table joined in the query, and only
matches up with the controls that refer to that table.

"KARL DEWEY" wrote:

Have you tried to do the SQL in a query rather than in the form?

--
Build a little, test a little.


"Boggle" wrote:

It gets worse. I removed the aliases just to see what happened. I am still
referencing two fields with the same name in different tables. The Order By
clause is still fine until you save:
[Lookup_control].[field]

and when you save it goes to:
Lookup_control.[table1.field]

where table1 is always the first joined table with that field name. That
that still works if you sort in the column relating to the first joined table
(so apparently it isn't a syntax problem any more). But if you sort in the
column from the second joined table, then the control doesn't refer to
table1, and you get the invalid bracket error. (Though clearly it isn't
the brackets which are the problem.)

By the way my controls are combo boxes so you can drop down and see a list
of all valid entries in the column. But I can't see what that would have to
do with it.

It all worked fine in Access 2003.

"Boggle" wrote:

By the way this is Access 2007.

I just wrote a simple cleanup routine (within any pair of square brackets,
split by periods, and replace the contents with the last token), but the
syntax check occurs before Open and Load. Which means there is no workaround
- if you do the edit at any other time then you have to save the form ... and
Access kindly screws the clause up again as it saves.

I'm surprised this hasn't come up before - surely there are other people out
there who use datasheets, and not completely trivial joins? With users who
like to sort and filter?

"Boggle" wrote:
...

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.