By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

no format assignment for a form's textbox control... Access assumes control's data is going to be placed in a finite length text field of a table

P: n/a
MLH
I have a form with two controls:
[Forms]![frmMainMenu]![RootTable] - combo box
[Forms]![frmMainMenu]![BodyTextHere] - text box

A button on the form tries to run this SQL when clicked...
INSERT INTO BodyMsgsSent (ToWhom, BodyText)
SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;

When the SQL runs, it fails with msg saying "Field length is too
long". I don't know why, because BodyMsgsSent.BodyTextHere is a
memo-type field. If the string I type in
[Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
occur. But naturally, this control is likely to have a LOT of text in
it. It is, after all, made to hold the body text of a letter.

So, why does Access fail when trying to put the contents of this
control into a table's memo field when there are, say, 400 chars
typed into the control??? I have no way of formatting the control
to tell Access 2.0 that the control is supposed to contain VLSs
intended for storage into a memo field.
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
What is the bound column of the combobox? Is it's data longer than the field
it is going into?

Mike Storr
www.veraccess.com
"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com...
I have a form with two controls:
[Forms]![frmMainMenu]![RootTable] - combo box
[Forms]![frmMainMenu]![BodyTextHere] - text box

A button on the form tries to run this SQL when clicked...
INSERT INTO BodyMsgsSent (ToWhom, BodyText)
SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;

When the SQL runs, it fails with msg saying "Field length is too
long". I don't know why, because BodyMsgsSent.BodyTextHere is a
memo-type field. If the string I type in
[Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
occur. But naturally, this control is likely to have a LOT of text in
it. It is, after all, made to hold the body text of a letter.

So, why does Access fail when trying to put the contents of this
control into a table's memo field when there are, say, 400 chars
typed into the control??? I have no way of formatting the control
to tell Access 2.0 that the control is supposed to contain VLSs
intended for storage into a memo field.

Nov 12 '05 #2

P: n/a
You say that BodyMsgsSent.BodyTextHere is a memo field;
what about BodyMsgsSent.BodyText?

That's where you're trying to stuff this information.

- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com...
I have a form with two controls:
[Forms]![frmMainMenu]![RootTable] - combo box
[Forms]![frmMainMenu]![BodyTextHere] - text box

A button on the form tries to run this SQL when clicked...
INSERT INTO BodyMsgsSent (ToWhom, BodyText)
SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;

When the SQL runs, it fails with msg saying "Field length is too
long". I don't know why, because BodyMsgsSent.BodyTextHere is a
memo-type field. If the string I type in
[Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
occur. But naturally, this control is likely to have a LOT of text in
it. It is, after all, made to hold the body text of a letter.

So, why does Access fail when trying to put the contents of this
control into a table's memo field when there are, say, 400 chars
typed into the control??? I have no way of formatting the control
to tell Access 2.0 that the control is supposed to contain VLSs
intended for storage into a memo field.

Nov 12 '05 #3

P: n/a
MLH
Its an UNbound control
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sun, 11 Jan 2004 15:24:12 -0500, "Mike Storr"
<st******@sympatico.ca> wrote:
What is the bound column of the combobox? Is it's data longer than the field
it is going into?

Mike Storr
www.veraccess.com
"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com.. .
I have a form with two controls:
[Forms]![frmMainMenu]![RootTable] - combo box
[Forms]![frmMainMenu]![BodyTextHere] - text box

A button on the form tries to run this SQL when clicked...
INSERT INTO BodyMsgsSent (ToWhom, BodyText)
SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;

When the SQL runs, it fails with msg saying "Field length is too
long". I don't know why, because BodyMsgsSent.BodyTextHere is a
memo-type field. If the string I type in
[Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
occur. But naturally, this control is likely to have a LOT of text in
it. It is, after all, made to hold the body text of a letter.

So, why does Access fail when trying to put the contents of this
control into a table's memo field when there are, say, 400 chars
typed into the control??? I have no way of formatting the control
to tell Access 2.0 that the control is supposed to contain VLSs
intended for storage into a memo field.


Nov 12 '05 #4

P: n/a
MLH
Its a memo field
xxxxxxxxxxxxxxxxxxxxx

On Sun, 11 Jan 2004 21:59:33 GMT, "MacDermott" <ma********@nospam.com>
wrote:
You say that BodyMsgsSent.BodyTextHere is a memo field;
what about BodyMsgsSent.BodyText?

That's where you're trying to stuff this information.

- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com.. .
I have a form with two controls:
[Forms]![frmMainMenu]![RootTable] - combo box
[Forms]![frmMainMenu]![BodyTextHere] - text box

A button on the form tries to run this SQL when clicked...
INSERT INTO BodyMsgsSent (ToWhom, BodyText)
SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;

When the SQL runs, it fails with msg saying "Field length is too
long". I don't know why, because BodyMsgsSent.BodyTextHere is a
memo-type field. If the string I type in
[Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
occur. But naturally, this control is likely to have a LOT of text in
it. It is, after all, made to hold the body text of a letter.

So, why does Access fail when trying to put the contents of this
control into a table's memo field when there are, say, 400 chars
typed into the control??? I have no way of formatting the control
to tell Access 2.0 that the control is supposed to contain VLSs
intended for storage into a memo field.


Nov 12 '05 #5

P: n/a
One column of the combo box is set to return a value when a choice is made.
This is called the "Bound Column". It does not mean it is bound to a field
in a table. The default for the column is 0, meaning the first one. When a
choice is made, the value of the combobox becomes whatever value is in the
bound column. Check to see that it's the right datatype and within the
length limit of the table field you're trying to put it in.
"MLH" <CR**@NorthState.net> wrote in message
news:li********************************@4ax.com...
Its an UNbound control
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sun, 11 Jan 2004 15:24:12 -0500, "Mike Storr"
<st******@sympatico.ca> wrote:
What is the bound column of the combobox? Is it's data longer than the fieldit is going into?

Mike Storr
www.veraccess.com
"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com.. .
I have a form with two controls:
[Forms]![frmMainMenu]![RootTable] - combo box
[Forms]![frmMainMenu]![BodyTextHere] - text box

A button on the form tries to run this SQL when clicked...
INSERT INTO BodyMsgsSent (ToWhom, BodyText)
SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;

When the SQL runs, it fails with msg saying "Field length is too
long". I don't know why, because BodyMsgsSent.BodyTextHere is a
memo-type field. If the string I type in
[Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
occur. But naturally, this control is likely to have a LOT of text in
it. It is, after all, made to hold the body text of a letter.

So, why does Access fail when trying to put the contents of this
control into a table's memo field when there are, say, 400 chars
typed into the control??? I have no way of formatting the control
to tell Access 2.0 that the control is supposed to contain VLSs
intended for storage into a memo field.

Nov 12 '05 #6

P: n/a
MLH
On Sun, 11 Jan 2004 22:31:29 -0500, "Mike Storr"
<st******@sympatico.ca> wrote:
One column of the combo box is set to return a value when a choice is made.
This is called the "Bound Column". It does not mean it is bound to a field
in a table. The default for the column is 0, meaning the first one. When a
choice is made, the value of the combobox becomes whatever value is in the
bound column. Check to see that it's the right datatype and within the
length limit of the table field you're trying to put it in.

The combo box control contents are not producing the error. If we take
the combo box off the form and remove its reference from the query,
the error still occurs. Its the LONG text string in the text box that
produces the error.

My work-around has been to Dim 2 global vars as String, assign
them values of the strings in the 2 controls on the form AfterUpdate
of each control and reference the global vars inside my append
query. The query works as desired - putting the same identical VLS
into the table's memo field - but with no "Field length is too
long" error. I'm still quite curious as to why I can't put the same
string in there with a Forms!FormName!ControlName reference.
Nov 12 '05 #7

P: n/a
How are you running this SQL?
Is it saved as a query?
Are you using DoCmd.RunSQL or CurrentDB.Execute?

- Turtle

"MacDermott" <ma********@nospam.com> wrote in message
news:9H****************@newsread3.news.atl.earthli nk.net...
You say that BodyMsgsSent.BodyTextHere is a memo field;
what about BodyMsgsSent.BodyText?

That's where you're trying to stuff this information.

- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com...
I have a form with two controls:
[Forms]![frmMainMenu]![RootTable] - combo box
[Forms]![frmMainMenu]![BodyTextHere] - text box

A button on the form tries to run this SQL when clicked...
INSERT INTO BodyMsgsSent (ToWhom, BodyText)
SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;

When the SQL runs, it fails with msg saying "Field length is too
long". I don't know why, because BodyMsgsSent.BodyTextHere is a
memo-type field. If the string I type in
[Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
occur. But naturally, this control is likely to have a LOT of text in
it. It is, after all, made to hold the body text of a letter.

So, why does Access fail when trying to put the contents of this
control into a table's memo field when there are, say, 400 chars
typed into the control??? I have no way of formatting the control
to tell Access 2.0 that the control is supposed to contain VLSs
intended for storage into a memo field.


Nov 12 '05 #8

P: n/a
MLH
I'm using RunSQL to launch it. I've saved it as a query
and ran it that way - same problem either way.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxx
On Tue, 13 Jan 2004 01:43:02 GMT, "MacDermott" <ma********@nospam.com>
wrote:
How are you running this SQL?
Is it saved as a query?
Are you using DoCmd.RunSQL or CurrentDB.Execute?

- Turtle

"MacDermott" <ma********@nospam.com> wrote in message
news:9H****************@newsread3.news.atl.earthl ink.net...
You say that BodyMsgsSent.BodyTextHere is a memo field;
what about BodyMsgsSent.BodyText?

That's where you're trying to stuff this information.

- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com...
> I have a form with two controls:
> [Forms]![frmMainMenu]![RootTable] - combo box
> [Forms]![frmMainMenu]![BodyTextHere] - text box
>
> A button on the form tries to run this SQL when clicked...
> INSERT INTO BodyMsgsSent (ToWhom, BodyText)
> SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
> [Forms]![frmMainMenu]![BodyTextHere] AS MyList;
>
> When the SQL runs, it fails with msg saying "Field length is too
> long". I don't know why, because BodyMsgsSent.BodyTextHere is a
> memo-type field. If the string I type in
> [Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not
> occur. But naturally, this control is likely to have a LOT of text in
> it. It is, after all, made to hold the body text of a letter.
>
> So, why does Access fail when trying to put the contents of this
> control into a table's memo field when there are, say, 400 chars
> typed into the control??? I have no way of formatting the control
> to tell Access 2.0 that the control is supposed to contain VLSs
> intended for storage into a memo field.



Nov 12 '05 #9

P: n/a
Could you post the actual line of code?

- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:qp********************************@4ax.com...
I'm using RunSQL to launch it. I've saved it as a query
and ran it that way - same problem either way.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxx
On Tue, 13 Jan 2004 01:43:02 GMT, "MacDermott" <ma********@nospam.com>
wrote:
How are you running this SQL?
Is it saved as a query?
Are you using DoCmd.RunSQL or CurrentDB.Execute?

- Turtle

"MacDermott" <ma********@nospam.com> wrote in message
news:9H****************@newsread3.news.atl.earthl ink.net...
You say that BodyMsgsSent.BodyTextHere is a memo field;
what about BodyMsgsSent.BodyText?

That's where you're trying to stuff this information.

- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:mo********************************@4ax.com...
> I have a form with two controls:
> [Forms]![frmMainMenu]![RootTable] - combo box
> [Forms]![frmMainMenu]![BodyTextHere] - text box
>
> A button on the form tries to run this SQL when clicked...
> INSERT INTO BodyMsgsSent (ToWhom, BodyText)
> SELECT DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
> [Forms]![frmMainMenu]![BodyTextHere] AS MyList;
>
> When the SQL runs, it fails with msg saying "Field length is too
> long". I don't know why, because BodyMsgsSent.BodyTextHere is a
> memo-type field. If the string I type in
> [Forms]![frmMainMenu]![BodyTextHere] is very short, the error does not > occur. But naturally, this control is likely to have a LOT of text in
> it. It is, after all, made to hold the body text of a letter.
>
> So, why does Access fail when trying to put the contents of this
> control into a table's memo field when there are, say, 400 chars
> typed into the control??? I have no way of formatting the control
> to tell Access 2.0 that the control is supposed to contain VLSs
> intended for storage into a memo field.

Nov 12 '05 #10

P: n/a
MLH
Sure, no problem...

MySQL = "INSERT INTO BodyMsgsSent (ToWhom, BodyText) SELECT
DISTINCTROW [Forms]![frmMainMenu]![RootTable] AS MyTargets,
[Forms]![frmMainMenu]![BodyTextHere] AS MyList;"
DoCmd SetWarnings False
DoCmd RunSQL MySQL
DoCmd SetWarnings True

(watch out for newsreader line-wrap - there are only 4 lines of code)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxx

On Tue, 13 Jan 2004 23:55:42 GMT, "MacDermott" <ma********@nospam.com>
wrote:
Could you post the actual line of code?

- Turtle

SNIP
Nov 12 '05 #11

P: n/a
MLH
In case my original post wasn't clear, I have two textbox controls on
a form containing strings I wish to write into a new record (append)
in a table:

[Forms]![frmMainMenu]![RootTable] contains a short string
[Forms]![frmMainMenu]![BodyTextHere] contains a VLS (very long string)

The target table is named BodyMsgsSent. The two pertinent fields are:
[ToWhom] - a 255 char text field
[BodyText] - a memo field

The problem I'm having is using Forms!FormName!ControlName references
in my query (standard QBF technique) causes the error. If I simply
assign two global string vars the string values & reference them in
query (by using FN's that return their values) - I don't get the
error.
Nov 12 '05 #12

P: n/a
I would expect these to return an error of undefined parameters, since Jet
doesn't resolve Access references.

I would try storing a query, and explicitly declaring the problem parameter
as type Memo. (Query - Parameters from the menu)

HTH
- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:de********************************@4ax.com...
In case my original post wasn't clear, I have two textbox controls on
a form containing strings I wish to write into a new record (append)
in a table:

[Forms]![frmMainMenu]![RootTable] contains a short string
[Forms]![frmMainMenu]![BodyTextHere] contains a VLS (very long string)

The target table is named BodyMsgsSent. The two pertinent fields are:
[ToWhom] - a 255 char text field
[BodyText] - a memo field

The problem I'm having is using Forms!FormName!ControlName references
in my query (standard QBF technique) causes the error. If I simply
assign two global string vars the string values & reference them in
query (by using FN's that return their values) - I don't get the
error.

Nov 12 '05 #13

P: n/a
MLH
Not a bad suggestion. I'll have a go at it. Thx.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Wed, 14 Jan 2004 03:22:50 GMT, "MacDermott" <ma********@nospam.com>
wrote:
I would expect these to return an error of undefined parameters, since Jet
doesn't resolve Access references.

I would try storing a query, and explicitly declaring the problem parameter
as type Memo. (Query - Parameters from the menu)

HTH
- Turtle

"MLH" <CR**@NorthState.net> wrote in message
news:de********************************@4ax.com.. .
In case my original post wasn't clear, I have two textbox controls on
a form containing strings I wish to write into a new record (append)
in a table:

[Forms]![frmMainMenu]![RootTable] contains a short string
[Forms]![frmMainMenu]![BodyTextHere] contains a VLS (very long string)

The target table is named BodyMsgsSent. The two pertinent fields are:
[ToWhom] - a 255 char text field
[BodyText] - a memo field

The problem I'm having is using Forms!FormName!ControlName references
in my query (standard QBF technique) causes the error. If I simply
assign two global string vars the string values & reference them in
query (by using FN's that return their values) - I don't get the
error.


Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.