Access 2007 corrupts record source SQL string

[David G.]

I've experienced the following several times and with various forms
and associated SQL strings as their records source.

I click on the ...(build ellipses) in the Record Source for a form.
Instead of opening the SQL up in the query builder, I get a blank
query builder form and the Show/Add tables form is opened. I
close/cancel the query builder and discover the SQL string has been
shortened, rendering it useless to the builder.

On occasion I've been able to close the form (without saving), then
reopen the form and find the SQL string is restored. I've also had to
rebuild the query a few times.

I thought it was the way I was clicking on the ellipses. So I took
extra care in not dragging the mouse, or any other extra movement that
might cause the problem. It just happened again.
THANKS!
David G.

 

[John W. Vinson]

I've experienced the following several times and with various forms
and associated SQL strings as their records source.

I click on the ...(build ellipses) in the Record Source for a form.
Instead of opening the SQL up in the query builder, I get a blank
query builder form and the Show/Add tables form is opened. I
close/cancel the query builder and discover the SQL string has been
shortened, rendering it useless to the builder.

On occasion I've been able to close the form (without saving), then
reopen the form and find the SQL string is restored. I've also had to
rebuild the query a few times.

I thought it was the way I was clicking on the ellipses. So I took
extra care in not dragging the mouse, or any other extra movement that
might cause the problem. It just happened again.
THANKS!
David G.

Try copying the string directly into the recordsource property box - without
clicking on the ... at all. Does that give the same problem?

 

[David G.]

Try copying the string directly into the recordsource property box - without
clicking on the ... at all. Does that give the same problem?

John:

The problem doesn't occur while trying to paste a SQL string into the
record source box. It occurs with existing record source SQL strings
when I attempt to open the SQL in the query builder.

Regards,
David
THANKS!
David G.

 

[Paul Shapiro]

I've seen the behavior you describe when there are linked tables referenced
in the sql that don't exist in the db, or when the sql includes syntax not
understood by the query builder. You could try copying the current record
source sql, create a new query, go to sql mode, and paste in the sql. Then
try switching to Design mode to see if the query builder understands your
sql. If you can work with the query that way, maybe something is corrupted
in the form? If not, then maybe the sql is not amenable to the query
builder. If you show us the current sql statement and the one you are trying
to generate with the query builder, maybe someone will have other
suggestions.

Try copying the string directly into the recordsource property box -
without
clicking on the ... at all. Does that give the same problem?

John:

The problem doesn't occur while trying to paste a SQL string into the
record source box. It occurs with existing record source SQL strings
when I attempt to open the SQL in the query builder.

 

[dweeber62]

Paul:
In trying to think about the exact circumstances, generally speaking,
I would be trying to open the query builder to either change the
included fields or the criteria. It is possible that I've been making
changes to controls on the form which are used to fill in the criteria
for the query. I may be renaming a control, or even removing the
control, then trying to open the query builder to make the SQL match
the form changes. In the process, I'm confusing access or corrupting
the relationships between form controls and the SQL.

I will pay closer attention to my modification steps in the future and
see if my design change method might be the problem.

Thanks for commenting.

I've seen the behavior you describe when there are linked tables referenced
in the sql that don't exist in the db, or when the sql includes syntax not
understood by the query builder. You could try copying the current record
source sql, create a new query, go to sql mode, and paste in the sql. Then
try switching to Design mode to see if the query builder understands your
sql. If you can work with the query that way, maybe something is corrupted
in the form? If not, then maybe the sql is not amenable to the query
builder. If you show us the current sql statement and the one you are trying
to generate with the query builder, maybe someone will have other
suggestions.


John:

The problem doesn't occur while trying to paste a SQL string into the
record source box. It occurs with existing record source SQL strings
when I attempt to open the SQL in the query builder.

Hidi Ho,
The Dweeber

 

[Sadao Imajou]

é€å—信ボタンをクリックã™ã‚‹ã¨woody.ocn.ne.jpã«ãƒ­ã‚°ã‚ªãƒ³ã€€ãƒ¡ãƒ¼ãƒ«ã‚µãƒãƒ¼ã®ãƒ¦ãƒ¼ã‚¶ãƒ¼åã¨ãƒ‘スワードãŒæ­£ã—ã„ã“ã¨ã‚’確èªã—ã¦ãã ã•ã„。ã¨ã€ã‚¨ãƒ©ãƒ¼ãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ãŒå‡ºã‚‹ãŒã€ãã®ãƒ¦ãƒ¼ã‚¶åã¨ãƒ‘スワードãŒã‚ã‹ã‚‰ãªã„。PCåˆå¿ƒè€…ãªã®ã§ã€ã‚ã‹ã‚Šã‚„ã™ãæ•™ãˆã¦ãã ã•ã„。
In trying to think about the exact circumstances, generally speaking,
I would be trying to open the query builder to either change the
included fields or the criteria. It is possible that I've been making
changes to controls on the form which are used to fill in the criteria
for the query. I may be renaming a control, or even removing the
control, then trying to open the query builder to make the SQL match
the form changes. In the process, I'm confusing access or corrupting
the relationships between form controls and the SQL.

I will pay closer attention to my modification steps in the future and
see if my design change method might be the problem.

Thanks for commenting.

I've seen the behavior you describe when there are linked tables referenced
in the sql that don't exist in the db, or when the sql includes syntax not
understood by the query builder. You could try copying the current record
source sql, create a new query, go to sql mode, and paste in the sql. Then
try switching to Design mode to see if the query builder understands your
sql. If you can work with the query that way, maybe something is corrupted
in the form? If not, then maybe the sql is not amenable to the query
builder. If you show us the current sql statement and the one you are
trying
to generate with the query builder, maybe someone will have other
suggestions.


John:

The problem doesn't occur while trying to paste a SQL string into the
record source box. It occurs with existing record source SQL strings
when I attempt to open the SQL in the query builder.

Hidi Ho,
The Dweeber