SQL Enter parameter value problem?

mbar · Jan 25, 2012

Hi- have been working on this for hours and cannot find the problem:

I'm trying to insert a text value into a table (from another table). I need to insert the text value, then I will insert other code to email a report (not shown here), and then loop to do the same thing until the end of recordset.

the values in the text field are ST33-1000, ST32-1015, ST32-1016, for example. When this is run, I receive a "enter parameter value" for the field ST32- which doesn't exist! ST32- is part of the actual record in the field!

Can anyone help!?!

I've only included the relevent code. I'm trying to run the code as-is to make sure it works properly:

Dim MyDB As Database
Dim MyRS As Recordset
Dim CurrentOrder As String
Dim stSql1 As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryordersemailstore")
MyRS.MoveFirst
Do Until MyRS.EOF

CurrentOrder = MyRS![ordernumb]

stSql1 = "INSERT INTO tblordnumbcurrentemail (ordnumb) SELECT tblordersemailstore.ordernumb1 FROM tblordersemailstore WHERE tblordersemailstore.ordernumb1 = " & CurrentOrder & ";"

DoCmd.RunSQL stSql1

MyRS.MoveNext
Loop
End Function

MarkK · Jan 25, 2012

In SQL we need to enclose strings in single quotes. What's happening for you here WHERE tblordersemailstore.ordernumb1 = " & CurrentOrder & ";" is the 'CurrentOrder' is replaced with the string ST32-1015 and SQL sees this as a subtraction problem, attempts to do the math, can't find a field called ST32 in any of the referenced tables, assumes it's a parameter, and prompts you for it's value.
Solve this by delimiting your string variables in SQL statements like ...

Code:

[B]WHERE tblordersemailstore.ordernumb1 = '" & CurrentOrder & "';"[/B]
[COLOR="Green"]'see the single quotes?[/COLOR]

That's one thing. Another thing is that you can probably do this insert in one go if you combine your query "qryordersemailstore" with your INSERT query. Consider this code ...

Code:

Sub InsertFromQuery
[COLOR="Green"]  'an action query can select multiple records from a source table and insert
  'them all into the target, which makes it possible for you NOT to open a recordset
  'and programmatically traverse every record
[/COLOR]  currentdb.execute _
    "INSERT INTO tblTarget " & _
      "( OrderNumber ) " & _
    "SELECT OrderNumber " & _
    "FROM qryordersemailstore;"
End Sub

Cheers,
Mark

mbar · Jan 25, 2012

Thanks Mark!

That worked. Saved me hours or frustration!

SQL Enter parameter value problem?

mbar

Registered User.

MarkK

bit cruncher

mbar

Registered User.

Similar threads

Users who are viewing this thread