SQL INSERT INTO "Enter Parameter Value" issue

[Fazered]

Having a strange error with a SQL statement in VBA. I'm trying to update an additional form via SQL and getting the dreaded "Enter Parameter Value" issue. I can't see why though especially as some variables are working without issue.

I'm using a bound form so the data is saved to a bookings table and this code should fill the order form to give defaults when the user comes to complete the order and confirm the details. The SQL code I have is this:

Dim strSQL As String

strSQL = "INSERT INTO [Completed Orders]([Order Number],[Status],[Actual Amount],[Actual Mix],[Actual Slump],[Actual Extras],[Actual Driver],[Actual Lorry],[Actual Delivery Type],[Actual Wait Time],[Actual Wait Charge]) VALUES (" & Me.txtOrderNumber & "," & Me.txtTestBox.Value & "," & Me.txtOrderAmount & "," & Me.comboRequiredMix & "," & Me.comboSlump.Text & "," & Me.comboOrderExtras & "," & Me.comboDriver.Value & "," & Me.comboLorry & "," & Me.comboDeliveryType & "," & Me.txtWaitTime & "," & Me.txtWaitCharge & ")"
'
DoCmd.RunSQL strSQL

Some work but others don't, the fields are correct because if you enter a value in the "Enter Parameter Value" box it goes to the right field. So I thought it was a data type issue because I am using a lot of list and combo boxes, so I created a simple text box and changed the field type to text. That still didn't work. I have tried just assigning a variable with data and passing that, nope. I changed the row source for the combo boxes, uh uh. I made sure they were the same in both forms, nah. Damn!

So could you see what I might be missing? I could upload some of the database if it will help but this involves a few tables and a big form so it won't be tiny.

Cheers,

Jamie

PS. There are a couple of us using this account so the other thread we have open is still live!

 

[Fazered]

Don't worry I've work a different way to do this that avoids the need for the SQL command. Would be nice to know what the issue was for the future though.

 

[namliam]

Issue #1 you have spaces in you column names... this is not the best of things to have...
For future reference, DONT use spaces in any names.

As for your actual issue.... you are passing all your values as numbers I doubt all are numbers...

Text fields should be surounded by 'field' and date/time fields by #field#

 

[ajetrumpet]

Fazered,

One thing you want to watch out for is using double quotes inside of a string that is already enclosed in double quotes from the outside. Example: this works:

strSQL = "DELETE * FROM table WHERE table.myfield = 'value'"

this results in the program reading code after it has reached the "unintended" conclusion of the string:

"DELETE * FROM table WHERE table.myfield = [color=red][b]"[/b][/color]value""

The red double quote would be read as the end of the statement by Access, and you would get an error there.

 

[namliam]

Fazered,

One thing you want to watch out for is using double quotes inside of a string that is already enclosed in double quotes from the outside. Example: this works:

strSQL = "DELETE * FROM table WHERE table.myfield = 'value'"

this results in the program reading code after it has reached the "unintended" conclusion of the string:

"DELETE * FROM table WHERE table.myfield = [color=red][b]"[/b][/color]value""

The red double quote would be read as the end of the statement by Access, and you would get an error there.

What does this have to do with his problem???

 

[ajetrumpet]

nothing. he said he wanted some info on the subject... he doesn't have a problem with this (I don't think), and it's quite possible that I've answered too many questions in the last couple of days.
If that's the case, thanks for the reminder...

 

[WayneRyan]

F,

Also,

Me.comboSlump.Text

The .Text property of a control is only valid in the OnChange event as you
enter each character.

You should either use .Value or leave it off.

Wayne

 

[namliam]

nothing. he said he wanted some info on the subject... he doesn't have a problem with this (I don't think), and it's quite possible that I've answered too many questions in the last couple of days.
If that's the case, thanks for the reminder...

No such thing as anwering to many questions... I thought it was pertaining to this problem and didnt understand your input at all.

In light of the "info on the subject" sure it is sound ...

Also instead of

strSQL = "DELETE * FROM table WHERE table.myfield = 'value'"

you have 2 other options:
Messy but it works:

strSQL = "DELETE * FROM table WHERE table.myfield = ""value"""
strSQL = "DELETE * FROM table WHERE table.myfield = " & Chr(34) & value & Chr(34)

I think it was 34 could be another number, but it replaces the " sign.