form limiting sql insert to 120 bytes ???

[skiefer]

Hi all,
I have a strange problem that I can't find listed anywhere in this forum. I have a very, very simple form that inserts a row into the database. One text field is giving me problems.

The insert works fine for data up to 121 bytes, then when I try to go longer, it gives me a '3001 invalid argument' error. The database field size is fine (it's 255), and I can update the database manually with the data I'm trying to insert. Seems to be some limit on the form ...

Any help???

Thanks for all the help this forum has been and will continue to be!!
Steph

 

[KenHigg]

Are you using code to do the insert? If so, can you post it?

ken

 

[skiefer]

Here's my code to insert into the DB. Like I said, it works fine, up to 121 characters in the instructions field.


Private Sub SaveLabel_Click()

Dim i As Long
For i = 1 To Forms!Labels!NumLabels
DoCmd.RunSQL ("INSERT INTO labels (title, servings, instructions, PrepDate, client)VALUES (Forms!Labels!Servings, Forms!Labels!Servings, Forms!Labels!Instructions,Forms!Labels!PrepDate, Forms!Labels!Client);")
Next i

DoCmd.RunSQL ("INSERT INTO recipes (title, instructions) VALUES (Forms!Labels!Title, Forms!Labels!Instructions);")

 

[KenHigg]

Just a bit off center here, but, should a field for instructions be a memo field?

???
ken

 

[skiefer]

i guess if figured memo was better if I'd need more text.

Anyway, changed it to memo, but still got the same error message

 

[KenHigg]

Did you try doing a regular append query to do this and then just call the query?

(Can you post the string you try to insert that errors?)

Ken

 

[skiefer]

ya, I started with the append query, but I couldn't get the d*(& thing to work ... the syntax of taking the info from the form was killing me. But, as you suggest, I am back to working that route now.

FYI, the text I'm trying to enter is as follows: Thaw in refrigerator overnight. Warm, covered, in 350-degree oven 15-20 minutes or microwave 1-3 minutes, checking frequently to avoid making chicken tough.

At first I thought it was the dashes causing the problem, but it craps out halfway thru the word "chicken" ... strange.

 

[KenHigg]

Did you try changing the word 'chicken' to 'fowl'?

???
ken

 

[skiefer]

last i checked "chicken" was not a reserved word

Here is a piece of the form and database that is causing the problem.

Please, any help on this would be greatly appreciated!!

I tried to upload my zipped database, but I'm 5 KB too big - can't figure how to make it any smaller.

 

[KenHigg]

I would still make it a memo. Just good db design. If you would, let's go back to the append query method. I'm pretty sure I can help get the syntax correct...

ken

 

[skiefer]

ok, will stick with the append query. I'm just getting bogged down with the syntax ... If I could get just one field to work, I could add all the others.

Here's what I have for the title field:

INSERT INTO TBlabels ( title )
VALUES (" '& me.title&' ");

i think i have to set the values of title in the VB code, do you have an example of one you've done?

 

[KenHigg]

Not sure this is where I thought we was going but here's a stab at correcting the code you posted:

INSERT INTO TBlabels ( title )
VALUES ('" & me.title & "');

???

 

[skiefer]

what do you mean, you didn't realize this is where we were going ... Why don't you tell me the right way to do this (i'm a newbie if you hadn't already guessed).

ALL i'm trying to do is take 5 fields from a form screen, that have been entered by a user, and put them into a table. Sometimes they need to print more than one of the same label, so I have a field that allows them to choose how many. Then from that table, i have another button they can press that will run a report that populates the data onto labels for them to print. After they have been printed, the table gets emptied out and is all ready for the next printing of labels.

I just created an append query with your text, and it populated " & me.title & " in the table.

 

[KenHigg]

Here it is from ground zero:

1. You have a field on a form that you want to put in a field in a table.
2. Do a new query. Make it an append query. Do not use any tables.
3. When you change it to an append query, it will ask which table to append to.
3. Set it up simular to the one in the attached .jpg. Make a column for each field you want to append

Make sense?

Q. Seems the table you wish to append to should be the record source for the form you are using?

ken

 

[skiefer]

Yes, the table i want to append to is also the source - only because normally when the user enters a new recipe/instructions, it usually looks very similar to others entered. They want to be able to pull up the previously entered record and edit it accordingly.

Thank you for the info - i am going to start from scracth with your example.

 

[skiefer]

AGHHHHH ... now it asks for me to "enter parameter vaue" for [labels]![title]. It doesn't want to take the info from the form.

Do I have to put some VB code behind the 'submit' button?

 

[KenHigg]

forms![labels]![title]

 

[skiefer]

Here is my query, just as you said. Then the next image is the error.

Also added [forms] in front, but same error.

 

[KenHigg]

Looks like you have a space in front of the word "Labels"

???

 

[skiefer]

Still Not Working!!!

Hi all,

Thanks (very much) to Ken, I have now converted my SQL insert to an append query, but unfortunately, I am still getting the same error message.

The second I try to insert more than 128 characters (not 121 as I originally thought), I get an 'invalid argument' message. The field is a memo field in my db and will accept 127 characters just fine.

Any other ideas from anyone ?????

I would like to post the db for you, but my zip file is 106 kb (6 kb bigger than the 100 allowed) so it won't let me upload to the site.