SQL String Too Long

A form that I have that feeds multiple tables has finally returned the error "The string returned by the builder was too long. The result will be truncated.". And it is. So, does this mean I can no longer add fields to it?
TIA
Trent Argante
[DC.J(158)]

On Wed, 21 Jul 2004 07:00:04 -0700, "Trent Argante" Trent
wrote:

A form that I have that feeds multiple tables has finally returned the error "The string returned by the builder was too long. The result will be truncated.". And it is. So, does this mean I can no longer add fields to it?
TIA
Trent Argante
[DC.J(158)]

You'll have to give us some more information, Trent. What is this
string? What do you mean by "feeds multiple tables"? What operation
was being performed to trigger the error?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Hi John,
"Feeds multiple tables" actually means "is linked to multiple tables". The operation that was being performed is adding a field to the form's internal query. Correct me if I'm wrong, but here's my expierence with forms: If a form is linked to only one table, then in the form's design, only the table name is in the form's Record Source property/field. But, if the form is linked to more than one table, then it creates a SQL statement. When I add a field to one of the linked tables, and I want to view that newly added field on the form, I have to update the form via the query builder in order to see the newly added field in the form's Field List (via the View menu). I've been successfully doing this for a few years now. Well, I guess I've added one too many fields to the form's SQL statement because now it's telling me that the SQL string is too long. So, what do I need to do in order to continue adding fields to my form?
Thanks for your help, John.
Trent Argante
[DC.J(158)]

"John Vinson" wrote:

On Wed, 21 Jul 2004 07:00:04 -0700, "Trent Argante" Trent
wrote:

A form that I have that feeds multiple tables has finally returned the error "The string returned by the builder was too long. The result will be truncated.". And it is. So, does this mean I can no longer add fields to it?
TIA
Trent Argante
[DC.J(158)]

You'll have to give us some more information, Trent. What is this
string? What do you mean by "feeds multiple tables"? What operation
was being performed to trigger the error?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Hi John,
"Feeds multiple tables" actually means "is linked to multiple tables".
The operation that was being performed was my attemping to add another field to a form that is linked to multiple tables. Correct me if I'm wrong, but here's my understanding of forms: When a form is linked to only one table, then only the table's name is in the form's "Record Source" property field (via the Properties Dialog). But, if a form is linked to more than one table, then a SQL statement is in the form's "Record Source" property field. When I add a field to one of the linked tables and want to view it on the form, I have to update the form's internal query via the Query Builder, which returns a SQL statement into the form's "Record Source" property field. This update is required in order for the newly added field to be displayed in the Field List of the View menu. I've been doing this successfully for a few years now. The last time I attempted it, as a result of the save, it returned the error "SQL string is too long" then truncated the SQL string. So, what do I need to do in order to continue adding fields to my form?
Thanks for your help, John.
Trent Argante
[DC.J(158)]


"John Vinson" wrote:

On Wed, 21 Jul 2004 07:00:04 -0700, "Trent Argante" Trent
wrote:

A form that I have that feeds multiple tables has finally returned the error "The string returned by the builder was too long. The result will be truncated.". And it is. So, does this mean I can no longer add fields to it?
TIA
Trent Argante
[DC.J(158)]

You'll have to give us some more information, Trent. What is this
string? What do you mean by "feeds multiple tables"? What operation
was being performed to trigger the error?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Just a guess.

But if your query is that long and complex, you souuld build it as a
separate (named) query then call that query name from your form instead of a
table name.

You have probably reached the limit of characters allowed in the
recordsource properties box (probably 255). All you need there is the name
of your query.

Copy the SQL statement from the properties box. Go to the Query window and
create a new query. Choose SQL view and paste the statement. Go back to
Design view and make addtions. Name it. Use that name as your recordsource
in the form


"Trent Argante" wrote in message
...
Hi John,
"Feeds multiple tables" actually means "is linked to multiple tables".
The operation that was being performed was my attemping to add another
field to a form that is linked to multiple tables. Correct me if I'm wrong,
but here's my understanding of forms: When a form is linked to only one
table, then only the table's name is in the form's "Record Source" property
field (via the Properties Dialog). But, if a form is linked to more than one
table, then a SQL statement is in the form's "Record Source" property field.
When I add a field to one of the linked tables and want to view it on the
form, I have to update the form's internal query via the Query Builder,
which returns a SQL statement into the form's "Record Source" property
field. This update is required in order for the newly added field to be
displayed in the Field List of the View menu. I've been doing this
successfully for a few years now. The last time I attempted it, as a result
of the save, it returned the error "SQL string is too long" then truncated
the SQL string. So, what do I need to do in order to continue adding fields
to my form?
Thanks for your help, John.
Trent Argante
[DC.J(158)]


"John Vinson" wrote:

On Wed, 21 Jul 2004 07:00:04 -0700, "Trent Argante" Trent
wrote:

A form that I have that feeds multiple tables has finally returned the
error "The string returned by the builder was too long. The result will be
truncated.". And it is. So, does this mean I can no longer add fields to it?
TIA
Trent Argante
[DC.J(158)]

You'll have to give us some more information, Trent. What is this
string? What do you mean by "feeds multiple tables"? What operation
was being performed to trigger the error?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

On Thu, 22 Jul 2004 13:08:22 -0500, "Tom Ross"
wrote:

You have probably reached the limit of characters allowed in the
recordsource properties box (probably 255).

It's a LOT more than 255 - and I saw his query in another forum (my
Compuserve chat actually) and it's a lot smaller than many
recordsource queries I've used. And it's being truncated VERY wierdly,
chopping some 60 bytes out of the WHERE clause, cutting right in the
middle of a word!

I've never seen such a thing, and have NO clue why Access might be
doing it. Your suggestion of using a named query is indeed probably
the right way to go.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

"John Vinson" wrote:

You have probably reached the limit of characters allowed in the
recordsource properties box (probably 255).

It's a LOT more than 255 - and I saw his query in another forum (my
Compuserve chat actually) and it's a lot smaller than many
recordsource queries I've used. And it's being truncated VERY wierdly,
chopping some 60 bytes out of the WHERE clause, cutting right in the
middle of a word!

I've never seen such a thing, and have NO clue why Access might be
doing it. Your suggestion of using a named query is indeed probably
the right way to go.

That IS weird!
I've never seen anything like that before either.
I agree that the named query seems like the only logical alternative.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Tom, John, & Jeff,
Thank you all, very much, for your help. The named query did the trick. It also made the response time faster.
(John, thanks for the step-by-step via Compuserve.com).
And, regarding the "weirdness" of the SQL, I have an innate way of finding the most obscure way of doing things. I'm sure Access was as baffled as you are as it saw me doing whatever it was I was doing when creating my form.
Thanks again, Gents.
Trent Argante
[DC.J(158)!Task(154)]


"Jeff Conrad" wrote:

"John Vinson" wrote:

You have probably reached the limit of characters allowed in the
recordsource properties box (probably 255).

It's a LOT more than 255 - and I saw his query in another forum (my
Compuserve chat actually) and it's a lot smaller than many
recordsource queries I've used. And it's being truncated VERY wierdly,
chopping some 60 bytes out of the WHERE clause, cutting right in the
middle of a word!

I've never seen such a thing, and have NO clue why Access might be
doing it. Your suggestion of using a named query is indeed probably
the right way to go.

That IS weird!
I've never seen anything like that before either.
I agree that the named query seems like the only logical alternative.

--
Jeff Conrad
Access Junkie
Bend, Oregon