A97 error #2246 - can't run uery because the parameter values are too large???

[MLH]

I have a query (SQL below) that operates on values entered by users
into an unbound form to append a record to tblAdmin. I do not under-
stand the basis for the error. There are some 17 or so data entry
fields on the form housing perhaps 600-700 characters between the
lot of them. Why might Access 97 need to err on such a simple
operation?

SQL:
INSERT INTO tblAdmin (TowCompany, TowCoAddr, TowCoCity, TowCoState,
TowCoZip, TowCoCounty, TowCoPhn, TowCoFax, TowCoEMail,
TowCoOwnerFName, TowCoOwnerLName, UserID, PetitionerType, TowCoTaxID,
RUADealer, PrimaryBsns, ClusterID, NewsPprName, PagerEmail,
AdminNotes) SELECT [Forms]![frmAddNewCustomer]![TowCompany] AS
MyTowCompany, [Forms]![frmAddNewCustomer]![TowCoAddr] AS MyTowCoAddr,
[Forms]![frmAddNewCustomer]![TowCoCity] AS MyTowCoCity,
[Forms]![frmAddNewCustomer]![TowCoState] AS MyTowCoState,
[Forms]![frmAddNewCustomer]![TowCoZip] AS MyTowCoZip,
[Forms]![frmAddNewCustomer]![TowCoCounty] AS MyTowCoCounty,
[Forms]![frmAddNewCustomer]![TowCoPhn] AS MyTowCoPhn,
[Forms]![frmAddNewCustomer]![ClusterFAX] AS MyTowCoFax,
[Forms]![frmAddNewCustomer]![TowCoEMail] AS MyTowCoEMail,
[Forms]![frmAddNewCustomer]![TowCoOwnerFName] AS MyTowCoOwnerFName,
[Forms]![frmAddNewCustomer]![TowCoOwnerLName] AS MyTowCoOwnerLName,
[Forms]![frmAddNewCustomer]![UserID] AS MyUserID,
[Forms]![frmAddNewCustomer]![PetitionerType] AS MyPetitionerType,
[Forms]![frmAddNewCustomer]![TowCoTaxID] AS MyTowCoTaxID,
[Forms]![frmAddNewCustomer]![RUADealer] AS MyRUADealer,
[Forms]![frmAddNewCustomer]![PrimaryBsns] AS MyPrimaryBsns,
[Forms]![frmAddNewCustomer]![ClusterID] AS MyClusterID,
[Forms]![frmAddNewCustomer]![NewsPprName] AS MyNewsPprName,
[Forms]![frmAddNewCustomer]![PagerEmail] AS MyPagerEmail,
[Forms]![frmAddNewCustomer]![AdminNotes] AS MyAdminNotes;

[tina]

have you checked the fields in tblAdmin to see if the FieldSize property of
any Text field is less than the value you're trying to insert - such as
appending a 30-character value into a 25-character field size; or if the
FieldSize property of any Number field is smaller than the value you're
trying to insert - such as appending a Long Integer value into an Integer
field size.

hth
"MLH" <CR**@NorthState.netwrote in message
news:94********************************@4ax.com...

I have a query (SQL below) that operates on values entered by users
into an unbound form to append a record to tblAdmin. I do not under-
stand the basis for the error. There are some 17 or so data entry
fields on the form housing perhaps 600-700 characters between the
lot of them. Why might Access 97 need to err on such a simple
operation?

SQL:
INSERT INTO tblAdmin (TowCompany, TowCoAddr, TowCoCity, TowCoState,
TowCoZip, TowCoCounty, TowCoPhn, TowCoFax, TowCoEMail,
TowCoOwnerFName, TowCoOwnerLName, UserID, PetitionerType, TowCoTaxID,
RUADealer, PrimaryBsns, ClusterID, NewsPprName, PagerEmail,
AdminNotes) SELECT [Forms]![frmAddNewCustomer]![TowCompany] AS
MyTowCompany, [Forms]![frmAddNewCustomer]![TowCoAddr] AS MyTowCoAddr,
[Forms]![frmAddNewCustomer]![TowCoCity] AS MyTowCoCity,
[Forms]![frmAddNewCustomer]![TowCoState] AS MyTowCoState,
[Forms]![frmAddNewCustomer]![TowCoZip] AS MyTowCoZip,
[Forms]![frmAddNewCustomer]![TowCoCounty] AS MyTowCoCounty,
[Forms]![frmAddNewCustomer]![TowCoPhn] AS MyTowCoPhn,
[Forms]![frmAddNewCustomer]![ClusterFAX] AS MyTowCoFax,
[Forms]![frmAddNewCustomer]![TowCoEMail] AS MyTowCoEMail,
[Forms]![frmAddNewCustomer]![TowCoOwnerFName] AS MyTowCoOwnerFName,
[Forms]![frmAddNewCustomer]![TowCoOwnerLName] AS MyTowCoOwnerLName,
[Forms]![frmAddNewCustomer]![UserID] AS MyUserID,
[Forms]![frmAddNewCustomer]![PetitionerType] AS MyPetitionerType,
[Forms]![frmAddNewCustomer]![TowCoTaxID] AS MyTowCoTaxID,
[Forms]![frmAddNewCustomer]![RUADealer] AS MyRUADealer,
[Forms]![frmAddNewCustomer]![PrimaryBsns] AS MyPrimaryBsns,
[Forms]![frmAddNewCustomer]![ClusterID] AS MyClusterID,
[Forms]![frmAddNewCustomer]![NewsPprName] AS MyNewsPprName,
[Forms]![frmAddNewCustomer]![PagerEmail] AS MyPagerEmail,
[Forms]![frmAddNewCustomer]![AdminNotes] AS MyAdminNotes;

[Lyle Fairfield]

MLH <CR**@NorthState.netwrote in
news:94********************************@4ax.com:

I have a query (SQL below) that operates on values entered by users
into an unbound form to append a record to tblAdmin. I do not under-
stand the basis for the error. There are some 17 or so data entry
fields on the form housing perhaps 600-700 characters between the
lot of them. Why might Access 97 need to err on such a simple
operation?

SQL:
INSERT INTO tblAdmin (TowCompany, TowCoAddr, TowCoCity, TowCoState,
TowCoZip, TowCoCounty, TowCoPhn, TowCoFax, TowCoEMail,
TowCoOwnerFName, TowCoOwnerLName, UserID, PetitionerType, TowCoTaxID,
RUADealer, PrimaryBsns, ClusterID, NewsPprName, PagerEmail,
AdminNotes) SELECT [Forms]![frmAddNewCustomer]![TowCompany] AS
MyTowCompany, [Forms]![frmAddNewCustomer]![TowCoAddr] AS MyTowCoAddr,
[Forms]![frmAddNewCustomer]![TowCoCity] AS MyTowCoCity,
[Forms]![frmAddNewCustomer]![TowCoState] AS MyTowCoState,
[Forms]![frmAddNewCustomer]![TowCoZip] AS MyTowCoZip,
[Forms]![frmAddNewCustomer]![TowCoCounty] AS MyTowCoCounty,
[Forms]![frmAddNewCustomer]![TowCoPhn] AS MyTowCoPhn,
[Forms]![frmAddNewCustomer]![ClusterFAX] AS MyTowCoFax,
[Forms]![frmAddNewCustomer]![TowCoEMail] AS MyTowCoEMail,
[Forms]![frmAddNewCustomer]![TowCoOwnerFName] AS MyTowCoOwnerFName,
[Forms]![frmAddNewCustomer]![TowCoOwnerLName] AS MyTowCoOwnerLName,
[Forms]![frmAddNewCustomer]![UserID] AS MyUserID,
[Forms]![frmAddNewCustomer]![PetitionerType] AS MyPetitionerType,
[Forms]![frmAddNewCustomer]![TowCoTaxID] AS MyTowCoTaxID,
[Forms]![frmAddNewCustomer]![RUADealer] AS MyRUADealer,
[Forms]![frmAddNewCustomer]![PrimaryBsns] AS MyPrimaryBsns,
[Forms]![frmAddNewCustomer]![ClusterID] AS MyClusterID,
[Forms]![frmAddNewCustomer]![NewsPprName] AS MyNewsPprName,
[Forms]![frmAddNewCustomer]![PagerEmail] AS MyPagerEmail,
[Forms]![frmAddNewCustomer]![AdminNotes] AS MyAdminNotes;

I've never seen this done where we use a SELECT with the values of Form
fields and have no way of checking to see if it actually works, although
I'd be surprised. I also doubt the aliases are necessary.
I expect a more common syntax is:

INSERT INTO tblAdmin

(
TowCompany,
TowCoAddr,
TowCoCity,
TowCoState,
TowCoZip,
TowCoCounty,
TowCoPhn,
TowCoFax,
TowCoEMail,
TowCoOwnerFName,
TowCoOwnerLName,
UserID,
PetitionerType,
TowCoTaxID,
RUADealer,
PrimaryBsns,
ClusterID,
NewsPprName,
PagerEmail,
AdminNotes)

VALUES

(
Forms![frmAddNewCustomer]![TowCompany],
Forms![frmAddNewCustomer]![TowCoAddr],
Forms![frmAddNewCustomer]![TowCoCity],
Forms![frmAddNewCustomer]![TowCoState],
Forms![frmAddNewCustomer]![TowCoZip],
Forms![frmAddNewCustomer]![TowCoCounty],
Forms![frmAddNewCustomer]![TowCoPhn],
Forms![frmAddNewCustomer]![ClusterFAX],
Forms![frmAddNewCustomer]![TowCoEMail],
Forms![frmAddNewCustomer]![TowCoOwnerFName],
Forms![frmAddNewCustomer]![TowCoOwnerLName],
Forms![frmAddNewCustomer]![UserID]
Forms![frmAddNewCustomer]![PetitionerType],
Forms![frmAddNewCustomer]![TowCoTaxID],
Forms![frmAddNewCustomer]![RUADealer],
Forms![frmAddNewCustomer]![PrimaryBsns],
Forms![frmAddNewCustomer]![ClusterID],
Forms![frmAddNewCustomer]![NewsPprName],
Forms![frmAddNewCustomer]![PagerEmail],
Forms![frmAddNewCustomer]![AdminNotes]
);

I have a vague recollection that if the field list includes all the
updateable fields then

INSERT INTO tblAdmin

VALUES

(
Forms![frmAddNewCustomer]![TowCompany],
Forms![frmAddNewCustomer]![TowCoAddr],
Forms![frmAddNewCustomer]![TowCoCity],
Forms![frmAddNewCustomer]![TowCoState],
Forms![frmAddNewCustomer]![TowCoZip],
Forms![frmAddNewCustomer]![TowCoCounty],
Forms![frmAddNewCustomer]![TowCoPhn],
Forms![frmAddNewCustomer]![ClusterFAX],
Forms![frmAddNewCustomer]![TowCoEMail],
Forms![frmAddNewCustomer]![TowCoOwnerFName],
Forms![frmAddNewCustomer]![TowCoOwnerLName],
Forms![frmAddNewCustomer]![UserID]
Forms![frmAddNewCustomer]![PetitionerType],
Forms![frmAddNewCustomer]![TowCoTaxID],
Forms![frmAddNewCustomer]![RUADealer],
Forms![frmAddNewCustomer]![PrimaryBsns],
Forms![frmAddNewCustomer]![ClusterID],
Forms![frmAddNewCustomer]![NewsPprName],
Forms![frmAddNewCustomer]![PagerEmail],
Forms![frmAddNewCustomer]![AdminNotes]
);

is sufficient but maybe that’s just for Inserts based on Selects.
--
Lyle Fairfield

http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm

[MLH]

Yes, in a way, I did. If I eliminate the text in the last of the
textbox controls on the form, the error does not occur. That
control contained 446-characters which were destined for a
memo type table field. The error seems to suggest not any
one field in violation of the 1024 character limitation - but a
combination of all the query's parameter information. Here's
the actual error message again:

Access can't run the query; the parameter values are too large. The
total length of all the values entered for the parameters cannot
exceed 1024 characters.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxx

On Sat, 23 Dec 2006 19:01:48 GMT, "tina" <no****@address.comwrote:

>have you checked the fields in tblAdmin to see if the FieldSize property of
any Text field is less than the value you're trying to insert - such as
appending a 30-character value into a 25-character field size; or if the
FieldSize property of any Number field is smaller than the value you're
trying to insert - such as appending a Long Integer value into an Integer
field size.

hth
"MLH" <CR**@NorthState.netwrote in message
news:94********************************@4ax.com.. .

>I have a query (SQL below) that operates on values entered by users
into an unbound form to append a record to tblAdmin. I do not under-
stand the basis for the error. There are some 17 or so data entry
fields on the form housing perhaps 600-700 characters between the
lot of them. Why might Access 97 need to err on such a simple
operation?

SQL:
INSERT INTO tblAdmin (TowCompany, TowCoAddr, TowCoCity, TowCoState,
TowCoZip, TowCoCounty, TowCoPhn, TowCoFax, TowCoEMail,
TowCoOwnerFName, TowCoOwnerLName, UserID, PetitionerType, TowCoTaxID,
RUADealer, PrimaryBsns, ClusterID, NewsPprName, PagerEmail,
AdminNotes) SELECT [Forms]![frmAddNewCustomer]![TowCompany] AS
MyTowCompany, [Forms]![frmAddNewCustomer]![TowCoAddr] AS MyTowCoAddr,
[Forms]![frmAddNewCustomer]![TowCoCity] AS MyTowCoCity,
[Forms]![frmAddNewCustomer]![TowCoState] AS MyTowCoState,
[Forms]![frmAddNewCustomer]![TowCoZip] AS MyTowCoZip,
[Forms]![frmAddNewCustomer]![TowCoCounty] AS MyTowCoCounty,
[Forms]![frmAddNewCustomer]![TowCoPhn] AS MyTowCoPhn,
[Forms]![frmAddNewCustomer]![ClusterFAX] AS MyTowCoFax,
[Forms]![frmAddNewCustomer]![TowCoEMail] AS MyTowCoEMail,
[Forms]![frmAddNewCustomer]![TowCoOwnerFName] AS MyTowCoOwnerFName,
[Forms]![frmAddNewCustomer]![TowCoOwnerLName] AS MyTowCoOwnerLName,
[Forms]![frmAddNewCustomer]![UserID] AS MyUserID,
[Forms]![frmAddNewCustomer]![PetitionerType] AS MyPetitionerType,
[Forms]![frmAddNewCustomer]![TowCoTaxID] AS MyTowCoTaxID,
[Forms]![frmAddNewCustomer]![RUADealer] AS MyRUADealer,
[Forms]![frmAddNewCustomer]![PrimaryBsns] AS MyPrimaryBsns,
[Forms]![frmAddNewCustomer]![ClusterID] AS MyClusterID,
[Forms]![frmAddNewCustomer]![NewsPprName] AS MyNewsPprName,
[Forms]![frmAddNewCustomer]![PagerEmail] AS MyPagerEmail,
[Forms]![frmAddNewCustomer]![AdminNotes] AS MyAdminNotes;

[tina]

hmm, okay. if Lyle's suggestion doesn't take care of it, you could try
running the SQL statement from VBA, as

Dim strSQL As String

strSQL = "INSERT INTO tblAdmin (TowCompany, " _
& "TowCoAddr, TowCoCity, TowCoState, " _
& "TowCoZip, TowCoCounty, TowCoPhn, " _
& "TowCoFax, TowCoEMail, TowCoOwnerFName, " _
& "TowCoOwnerLName, UserID, PetitionerType, " _
& "TowCoTaxID, RUADealer, PrimaryBsns, " _
& "ClusterID, NewsPprName, PagerEmail, " _
& "AdminNotes) SELECT '" & Me!TowCompany _
& "', '" & Me!TowCoAddr & "', '" & Me!TowCoCity _
& "', '" & Me!TowCoState & "', '" & Me!TowCoZip _
& "', '" & Me!TowCoCounty & "', '" & Me!TowCoPhn _
& "', '" & Me!ClusterFAX & "', '" & Me!TowCoEMail _
& "', '" & Me!TowCoOwnerFName & "', '" _
& Me!TowCoOwnerLName & "', '" & Me!UserID _
& "', '" & Me!PetitionerType & "', '" & Me!TowCoTaxID _
& "', '" & Me!RUADealer & "', '" & Me!PrimaryBsns _
& "', '" & Me!ClusterID & "', '" & Me!NewsPprName _
& "', '" & Me!PagerEmail & "', '" & Me!AdminNotes & "'"

the above statement assumes that all fields are Text data type; if any are
Number or Date/Time, you'll have to adjust the syntax accordingly, of
course.

hth
"MLH" <CR**@NorthState.netwrote in message
news:nf********************************@4ax.com...

Yes, in a way, I did. If I eliminate the text in the last of the
textbox controls on the form, the error does not occur. That
control contained 446-characters which were destined for a
memo type table field. The error seems to suggest not any
one field in violation of the 1024 character limitation - but a
combination of all the query's parameter information. Here's
the actual error message again:

Access can't run the query; the parameter values are too large. The
total length of all the values entered for the parameters cannot
exceed 1024 characters.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxx

On Sat, 23 Dec 2006 19:01:48 GMT, "tina" <no****@address.comwrote:

have you checked the fields in tblAdmin to see if the FieldSize property

of

any Text field is less than the value you're trying to insert - such as
appending a 30-character value into a 25-character field size; or if the
FieldSize property of any Number field is smaller than the value you're
trying to insert - such as appending a Long Integer value into an Integer
field size.

hth
"MLH" <CR**@NorthState.netwrote in message
news:94********************************@4ax.com...

I have a query (SQL below) that operates on values entered by users
into an unbound form to append a record to tblAdmin. I do not under-
stand the basis for the error. There are some 17 or so data entry
fields on the form housing perhaps 600-700 characters between the
lot of them. Why might Access 97 need to err on such a simple
operation?

SQL:
INSERT INTO tblAdmin (TowCompany, TowCoAddr, TowCoCity, TowCoState,
TowCoZip, TowCoCounty, TowCoPhn, TowCoFax, TowCoEMail,
TowCoOwnerFName, TowCoOwnerLName, UserID, PetitionerType, TowCoTaxID,
RUADealer, PrimaryBsns, ClusterID, NewsPprName, PagerEmail,
AdminNotes) SELECT [Forms]![frmAddNewCustomer]![TowCompany] AS
MyTowCompany, [Forms]![frmAddNewCustomer]![TowCoAddr] AS MyTowCoAddr,
[Forms]![frmAddNewCustomer]![TowCoCity] AS MyTowCoCity,
[Forms]![frmAddNewCustomer]![TowCoState] AS MyTowCoState,
[Forms]![frmAddNewCustomer]![TowCoZip] AS MyTowCoZip,
[Forms]![frmAddNewCustomer]![TowCoCounty] AS MyTowCoCounty,
[Forms]![frmAddNewCustomer]![TowCoPhn] AS MyTowCoPhn,
[Forms]![frmAddNewCustomer]![ClusterFAX] AS MyTowCoFax,
[Forms]![frmAddNewCustomer]![TowCoEMail] AS MyTowCoEMail,
[Forms]![frmAddNewCustomer]![TowCoOwnerFName] AS MyTowCoOwnerFName,
[Forms]![frmAddNewCustomer]![TowCoOwnerLName] AS MyTowCoOwnerLName,
[Forms]![frmAddNewCustomer]![UserID] AS MyUserID,
[Forms]![frmAddNewCustomer]![PetitionerType] AS MyPetitionerType,
[Forms]![frmAddNewCustomer]![TowCoTaxID] AS MyTowCoTaxID,
[Forms]![frmAddNewCustomer]![RUADealer] AS MyRUADealer,
[Forms]![frmAddNewCustomer]![PrimaryBsns] AS MyPrimaryBsns,
[Forms]![frmAddNewCustomer]![ClusterID] AS MyClusterID,
[Forms]![frmAddNewCustomer]![NewsPprName] AS MyNewsPprName,
[Forms]![frmAddNewCustomer]![PagerEmail] AS MyPagerEmail,
[Forms]![frmAddNewCustomer]![AdminNotes] AS MyAdminNotes;

[MLH]

The syntax below looked promising. So, I made a new TEST
form and tried the syntax below (cannot be represented in design
view, by the way. Same identical error was returned.

INSERT INTO tblAdmin

VALUES

(
Forms![frmAddNewCustomerTEST]![TowCompany],
Forms![frmAddNewCustomerTEST]![TowCoAddr],
Forms![frmAddNewCustomerTEST]![TowCoCity],
Forms![frmAddNewCustomerTEST]![TowCoState],
Forms![frmAddNewCustomerTEST]![TowCoZip],
Forms![frmAddNewCustomerTEST]![TowCoCounty],
Forms![frmAddNewCustomerTEST]![TowCoPhn],
Forms![frmAddNewCustomerTEST]![ClusterFAX],
Forms![frmAddNewCustomerTEST]![TowCoEMail],
Forms![frmAddNewCustomerTEST]![TowCoOwnerFName],
Forms![frmAddNewCustomerTEST]![TowCoOwnerLName],
Forms![frmAddNewCustomerTEST]![UserID],
Forms![frmAddNewCustomerTEST]![PetitionerType],
Forms![frmAddNewCustomerTEST]![TowCoTaxID],
Forms![frmAddNewCustomerTEST]![RUADealer],
Forms![frmAddNewCustomerTEST]![PrimaryBsns],
Forms![frmAddNewCustomerTEST]![ClusterID],
Forms![frmAddNewCustomerTEST]![NewsPprName],
Forms![frmAddNewCustomerTEST]![PagerEmail],
Forms![frmAddNewCustomerTEST]![AdminNotes]
);

[Lyle Fairfield]

MLH wrote:

The syntax below looked promising. So, I made a new TEST
form and tried the syntax below (cannot be represented in design
view, by the way. Same identical error was returned.

INSERT INTO tblAdmin

VALUES

(
Forms![frmAddNewCustomerTEST]![TowCompany],
Forms![frmAddNewCustomerTEST]![TowCoAddr],
Forms![frmAddNewCustomerTEST]![TowCoCity],
Forms![frmAddNewCustomerTEST]![TowCoState],
Forms![frmAddNewCustomerTEST]![TowCoZip],
Forms![frmAddNewCustomerTEST]![TowCoCounty],
Forms![frmAddNewCustomerTEST]![TowCoPhn],
Forms![frmAddNewCustomerTEST]![ClusterFAX],
Forms![frmAddNewCustomerTEST]![TowCoEMail],
Forms![frmAddNewCustomerTEST]![TowCoOwnerFName],
Forms![frmAddNewCustomerTEST]![TowCoOwnerLName],
Forms![frmAddNewCustomerTEST]![UserID],
Forms![frmAddNewCustomerTEST]![PetitionerType],
Forms![frmAddNewCustomerTEST]![TowCoTaxID],
Forms![frmAddNewCustomerTEST]![RUADealer],
Forms![frmAddNewCustomerTEST]![PrimaryBsns],
Forms![frmAddNewCustomerTEST]![ClusterID],
Forms![frmAddNewCustomerTEST]![NewsPprName],
Forms![frmAddNewCustomerTEST]![PagerEmail],
Forms![frmAddNewCustomerTEST]![AdminNotes]
);

TTBOMK Access/Jet treats expressions such as
Forms![frmAddNewCustomerTEST]![AdminNotes] in the SQL above as
parameters. Each parameter value has a limit of 255 characters. I guess
that the value/text of at least one record's AdminNotes, or other
control, is a string of more than 255 characters.

I almost never use the query design window; it's likely I would have
effected what I think you are trying to do with code as follows:

Dim comma As String
Dim sql As String
Dim singleQuote As String
comma = ","
sql = "INSERT INTO tblAdmin"
singleQuote = "'"

sql = sql & vbNewLine
sql = sql & "VALUES"

sql = sql & vbNewLine
sql = sql & "("

sql = sql & vbNewLine
sql = sql & singleQuote & Forms![frmAddNewCustomerTEST]![TowCompany] &
singleQuote

sql = sql & vbNewLine
sql = sql & comma & singleQuote &
Forms![frmAddNewCustomerTEST]![SomeString] & singleQuote

sql = sql & vbNewLine
sql = sql & comma & Forms![frmAddNewCustomerTEST]![SomeNonString]

etc

sql = sql & vbNewLine
sql = sql & ")"

' just to check in case we get an error
' remove when happy
Debug.Print sql

CurrentDb.Execute sql

In this way I use literal expressions which are not limited to 255
characters rather than parameters. Of course. the whole sql string is
limited to about 65536 characters, but I've never come near that.