Please help! SQL syntax error?

[mbar]

Hi- have been working on this for many 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!?!
Is this the right syntax?

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

[Robeen]

Did this ever work?

I've always used Insert statements with the keyword 'Values'.
Eg:

 
Dim StudentName, StrSQL As String
Me.cmbStudentName.SetFocus
StudentName = Me.cmbStudentName.Value
StrSQL = "INSERT INTO Table1 (StudentName) "
StrSQL = StrSQL & "VALUES (" & "'" & StudentName & "'" & "); "
MsgBox StrSQL
DoCmd.RunSQL StrSQL

I'm not certain of this - but what I would try is getting the value you need to insert into a variable as a separate operatoin - outside your Insert statement - and then using the variable in your insert statement.

Have you changed the design of your table[s] at any point?
I've got that parameter message in the past when I've modified a Table Design and forgotten that there are queries dependent on that Table design.
Then Access doesn't see a field that used to exist and throws up the parameter message.

Hope this points you in the right direction.

[mbar]

Worked!!!

I was able to simplify this and used the variable as the VALUE:

I will now include code to use "currentorder" to send individual emails with different attachments. Hopefully I won't have an more issues...

thanks again- saved me hours!!

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


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblordersemailstore")
MyRS.MoveFirst

Do Until MyRS.EOF
CurrentOrder = MyRS![ordernumb1]
stSql1 = "INSERT INTO tblordnumbcurrentemail (ordnumb) "
stSql1 = stSql1 & "VALUES (" & "'" & CurrentOrder & "'" & "); "


Debug.Print stSql1
MsgBox stSql1
DoCmd.RunSQL stSql1

MyRS.MoveNext
Loop
End Function

[Robeen]

You're welcome!!

I'm glad I could help.