INSERT INTO

G

Guest

I am using the following code to insert a new record into a table:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq])" & _
"VALUES (Forms!FrmHRCAffectedByOthers!CLNO,
Forms!FrmHRCAffectedByOthers!Text33," & _
"Forms!FrmHRCAffectedByOthers!Text35,
Forms!FrmHRCAffectedByOthers!Text31," & _
"Forms!FrmHRCAffectedByOthers!Text37,
Forms!FrmHRCAffectedByOthers!Check38);"

Everything works fine until the text in the DESC field exceeds 127
characters. The DESC field in the table is defined as a MEMO field so there
shouldn't be any limit. If the DESC is less than 128 characters, it works
fine. Any ideas on why it would be imposing this kind of limit on a memo
field?
 
G

Guest

Hi Lanita,

Have you tried actually building your SQL string to have the values in the
string rather than directly referring to the form? eg:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq]) VALUES (" & _
Forms!FrmHRCAffectedByOthers!CLNO & ", '" & _
Forms!FrmHRCAffectedByOthers!Text33 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text35 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text31 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text37 & "', '" & _
Forms!FrmHRCAffectedByOthers!Check38 & "');"

Just to see if it makes a difference...

Damian.
 
G

Guest

When I copied and pasted your code, I get a lot of "syntax errors". It looks
to me like what you suggested was concatenating the statement. Is that
correct?

Damian S said:
Hi Lanita,

Have you tried actually building your SQL string to have the values in the
string rather than directly referring to the form? eg:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq]) VALUES (" & _
Forms!FrmHRCAffectedByOthers!CLNO & ", '" & _
Forms!FrmHRCAffectedByOthers!Text33 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text35 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text31 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text37 & "', '" & _
Forms!FrmHRCAffectedByOthers!Check38 & "');"

Just to see if it makes a difference...

Damian.


Lanita said:
I am using the following code to insert a new record into a table:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq])" & _
"VALUES (Forms!FrmHRCAffectedByOthers!CLNO,
Forms!FrmHRCAffectedByOthers!Text33," & _
"Forms!FrmHRCAffectedByOthers!Text35,
Forms!FrmHRCAffectedByOthers!Text31," & _
"Forms!FrmHRCAffectedByOthers!Text37,
Forms!FrmHRCAffectedByOthers!Check38);"

Everything works fine until the text in the DESC field exceeds 127
characters. The DESC field in the table is defined as a MEMO field so there
shouldn't be any limit. If the DESC is less than 128 characters, it works
fine. Any ideas on why it would be imposing this kind of limit on a memo
field?
Click to expand...
Click to expand...
 
G

Guest

I'm suggesting having your SQL actually pick up the values from the fields,
rather than simply passing a reference to the field. I have assumed that all
fields are text fields except the first one. If this isn't the case, you
will need to remove the single quotes where necessary.

Lanita said:
When I copied and pasted your code, I get a lot of "syntax errors". It looks
to me like what you suggested was concatenating the statement. Is that
correct?

Damian S said:
Hi Lanita,

Have you tried actually building your SQL string to have the values in the
string rather than directly referring to the form? eg:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq]) VALUES (" & _
Forms!FrmHRCAffectedByOthers!CLNO & ", '" & _
Forms!FrmHRCAffectedByOthers!Text33 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text35 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text31 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text37 & "', '" & _
Forms!FrmHRCAffectedByOthers!Check38 & "');"

Just to see if it makes a difference...

Damian.


Lanita said:
I am using the following code to insert a new record into a table:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq])" & _
"VALUES (Forms!FrmHRCAffectedByOthers!CLNO,
Forms!FrmHRCAffectedByOthers!Text33," & _
"Forms!FrmHRCAffectedByOthers!Text35,
Forms!FrmHRCAffectedByOthers!Text31," & _
"Forms!FrmHRCAffectedByOthers!Text37,
Forms!FrmHRCAffectedByOthers!Check38);"

Everything works fine until the text in the DESC field exceeds 127
characters. The DESC field in the table is defined as a MEMO field so there
shouldn't be any limit. If the DESC is less than 128 characters, it works
fine. Any ideas on why it would be imposing this kind of limit on a memo
field?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Now I am using the following code:
DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE MEASURE]," & _
"[DESC],[CAUSER],[HRC Expiration],[IsConsentReq]) VALUES (" & _
Forms!FrmHRCAffectedByOthers!CLNO & " ', '" & _
Forms!FrmHRCAffectedByOthers!Text33 & " ', '" & _
Forms!FrmHRCAffectedByOthers!Text35 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text31 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text37 & "', '" & _
Forms!FrmHRCAffectedByOthers!Check38 & ");"


With the above code I get the following error:
Syntax error (missing operator) in query expression
'7091','EMOD','Padded...','6960','5/21/2007',".

All the info in the error message is the correct information I'm trying to
insert. The last item is blank which is OK. I don't see the missing
operator. Can you help?





Damian S said:
I'm suggesting having your SQL actually pick up the values from the fields,
rather than simply passing a reference to the field. I have assumed that all
fields are text fields except the first one. If this isn't the case, you
will need to remove the single quotes where necessary.

Lanita said:
When I copied and pasted your code, I get a lot of "syntax errors". It looks
to me like what you suggested was concatenating the statement. Is that
correct?

Damian S said:
Hi Lanita,

Have you tried actually building your SQL string to have the values in the
string rather than directly referring to the form? eg:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq]) VALUES (" & _
Forms!FrmHRCAffectedByOthers!CLNO & ", '" & _
Forms!FrmHRCAffectedByOthers!Text33 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text35 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text31 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text37 & "', '" & _
Forms!FrmHRCAffectedByOthers!Check38 & "');"

Just to see if it makes a difference...

Damian.


:

I am using the following code to insert a new record into a table:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq])" & _
"VALUES (Forms!FrmHRCAffectedByOthers!CLNO,
Forms!FrmHRCAffectedByOthers!Text33," & _
"Forms!FrmHRCAffectedByOthers!Text35,
Forms!FrmHRCAffectedByOthers!Text31," & _
"Forms!FrmHRCAffectedByOthers!Text37,
Forms!FrmHRCAffectedByOthers!Check38);"

Everything works fine until the text in the DESC field exceeds 127
characters. The DESC field in the table is defined as a MEMO field so there
shouldn't be any limit. If the DESC is less than 128 characters, it works
fine. Any ideas on why it would be imposing this kind of limit on a memo
field?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I did get all the syntax errors fixed but I still get the "invalid argument"
when the text in the memo field exceeds 127 characters. Does anyone have any
ideas at all? It appears that the memo field of unlimited characters is
being limited by the "insert Into" method but I don't know why?

Lanita said:
Now I am using the following code:
DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE MEASURE]," & _
"[DESC],[CAUSER],[HRC Expiration],[IsConsentReq]) VALUES (" & _
Forms!FrmHRCAffectedByOthers!CLNO & " ', '" & _
Forms!FrmHRCAffectedByOthers!Text33 & " ', '" & _
Forms!FrmHRCAffectedByOthers!Text35 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text31 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text37 & "', '" & _
Forms!FrmHRCAffectedByOthers!Check38 & ");"


With the above code I get the following error:
Syntax error (missing operator) in query expression
'7091','EMOD','Padded...','6960','5/21/2007',".

All the info in the error message is the correct information I'm trying to
insert. The last item is blank which is OK. I don't see the missing
operator. Can you help?





Damian S said:
I'm suggesting having your SQL actually pick up the values from the fields,
rather than simply passing a reference to the field. I have assumed that all
fields are text fields except the first one. If this isn't the case, you
will need to remove the single quotes where necessary.

Lanita said:
When I copied and pasted your code, I get a lot of "syntax errors". It looks
to me like what you suggested was concatenating the statement. Is that
correct?

:

Hi Lanita,

Have you tried actually building your SQL string to have the values in the
string rather than directly referring to the form? eg:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq]) VALUES (" & _
Forms!FrmHRCAffectedByOthers!CLNO & ", '" & _
Forms!FrmHRCAffectedByOthers!Text33 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text35 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text31 & "', '" & _
Forms!FrmHRCAffectedByOthers!Text37 & "', '" & _
Forms!FrmHRCAffectedByOthers!Check38 & "');"

Just to see if it makes a difference...

Damian.


:

I am using the following code to insert a new record into a table:

DoCmd.RunSQL "INSERT INTO HRC ([CLNO],[RESTRICTIVE
MEASURE],[DESC],[CAUSER],[HRC Expiration],[IsConsentReq])" & _
"VALUES (Forms!FrmHRCAffectedByOthers!CLNO,
Forms!FrmHRCAffectedByOthers!Text33," & _
"Forms!FrmHRCAffectedByOthers!Text35,
Forms!FrmHRCAffectedByOthers!Text31," & _
"Forms!FrmHRCAffectedByOthers!Text37,
Forms!FrmHRCAffectedByOthers!Check38);"

Everything works fine until the text in the DESC field exceeds 127
characters. The DESC field in the table is defined as a MEMO field so there
shouldn't be any limit. If the DESC is less than 128 characters, it works
fine. Any ideas on why it would be imposing this kind of limit on a memo
field?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Value of Text box being inserted into a memo table field 3
INSERT INTO 6
SQL "insert into" code not working 2
Button to insert boilerplate text into field in a parent form 9
insert memo feild into a memo field truncates at 255 characters - 4
SQL code to insert data from one table into another 5
insert record action truncating memo field data 4
SQL "insert into" syntax 4

Top