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

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

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

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

Mike Storr

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

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

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

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

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

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

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

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

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

by: NewBob | last post by:

Since Access automatically highlights all of the text in a text control (I use it to hold data from a memo field) when the control is activated, I've added the following code to put the cursor at...

Help Displaying Tables in DataSheet Format

by: Aladdin | last post by:

I have an MS Access form on which I have a listbox listing tables in that database. I want to be able to click on any of those tables and view its contents on the same form using subforms or any...

DLookup in TabControl in Form

by: LSemos | last post by:

I am trying to lookup phone numbers in a separate table based on the name in the current Tab control tblContacts has the fields: Name, Phone, Fax, and Email In my Form's Tab control, I have...

Percent Format in a Control

by: Nothing | last post by:

This is going to be a really stupid question, but I am having a problem with the percent format in a control on a form. My end goal is that I want the user to enter 17 and have the control...

How to maintain Text Format?

by: Miguel Dias Moura | last post by:

Hello, I created a page with one form and 10 Input Text fields. The Form data is added to an Access Database when "OK" button is pressed. One of the Form's Input Text has more than one line....

ASP.NET

Textbox replacement?

by: Randy Jackson | last post by:

Hello, I'm creating a sample portfolio application for MS-Access XP, and I'm wondering if there's a suitable replacement for the ms-access textbox control. I'm looking for something similar to...

go to a record in a form

by: kaosyeti | last post by:

hey.... i have a duplicate record issue i could use some help with. on a form that has 2 unbound controls, txtboxyear and cboxmonth, together will automatically fill in an invisible txtboxdate...

parse or format data from memo field onto Access report

incorrect string format

by: RipperT | last post by:

Don't know if this group covers web apps, but here goes. In VS 2005, I am trying to get variables to hold thier values during postback from the server. I convert a text box's user-keyed value to an...

Visual Basic .NET

by: RMC | last post by:

Hello, I'm looking for a way to parse/format a memo field within a report. The Access 2000 database (application) has an equipment table that holds a memo field. Within the report, the memo...

Hyperconverged All-in-One Streaming Engine, ushering in new age for distributed database

by: antdb | last post by:

Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...

HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computer

by: AndyPSV | last post by:

HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...

How do I view Redshift db tables in Alteryx

by: Arjunsri | last post by:

I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...

by: WisdomUfot | last post by:

It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...

PHP Curl Not wanting to connect to localhost.

by: Matthew3360 | last post by:

Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...

PHP

FPGA Question

by: Oralloy | last post by:

Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...

One of the easiest ways to set the background color of Excel documents in Java

by: Carina712 | last post by:

Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...

Datasheet Form, iterate through records

by: Johno34 | last post by:

I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...

Hide Labels depending on checkbox value

by: ezappsrUS | last post by:

Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...