SQL SYNTAX ERROR

[DS]

I'm trying to insert all records that meet a certain criteria and I'm
getting an error.
Whats wrong with my syntax?
Thanks
DS

Dim NewItemSQL
NewItemSQL = "INSERT * INTO tblCheckDetailsTMP " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID=Forms!frmFXTransferSelect!TxtCheckID " &
_
"AND tblCheckDetailsTMP.CDGroupID = Forms!frmFXTransferItem!TxtGroupID;"
DoCmd.RunSQL (NewItemSQL)

 

[Allen Browne]

Try concatenating the values from the text boxes into the string, like this:

Dim NewItemSQL As String

If IsNull(Me.TxtCheckID) Or IsNull(Me.TxtGroupID) Then
MsgBox "Fill in both"
Else
NewItemSQL = "INSERT * INTO tblCheckDetailsTMP " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE ((tblCheckDetailsTMP.CDCheckID = " & _
Me.TxtCheckID & _
") AND (tblCheckDetailsTMP.CDGroupID = " & _
Me.TxtGroupID & "));"

dbEngine(0)(0).Execute NewItemSQL, dbFailOnError
End If

Notes:
====
1. Testing for Null prevents a mal-formed string.

2. If CDCheckID or CDGroupID is a Text field (not a Number field), you need
extra quotes. See:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

3. Using Execute instead of RunSQL gives more info if something goes wrong.
See:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

4. If it still fails, add the line:
Debug.Print NewItemSQL
just before the Execute. You can then copy that from the Immediate Window
(Ctrl+G), paste into SQL View in a query, and see what's not right.

 

[Dirk Goldgar]

I'm trying to insert all records that meet a certain criteria and I'm
getting an error.
Whats wrong with my syntax?
Thanks
DS

Dim NewItemSQL
NewItemSQL = "INSERT * INTO tblCheckDetailsTMP " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID=Forms!frmFXTransferSelect!TxtCheckID "
& _
"AND tblCheckDetailsTMP.CDGroupID = Forms!frmFXTransferItem!TxtGroupID;"
DoCmd.RunSQL (NewItemSQL)

Why are you inserting into and selecting from the same table?

 

[DS]

Thanks Allen, I like that testing for null, that is really good.
Thanks
DS

 

[DS]

Because I need to create new records using the same info, is that bad or
incorrect to do?
Thanks
DS

 

[Dirk Goldgar]

Because I need to create new records using the same info, is that bad or
incorrect to do?

No, it's fine if that's your intention. I just didn't know if it was a
mistake or not. Note that, as it stands, there's no visible way to tell the
difference between the records you duplicated and their originals. If
that's not an issue, and there's no unique index on the table that would be
violated by the duplication, then there's nothing wrong with what you're
doing.

 

[Allen Browne]

It's not bad to do, but if you want to automatically fill in the new record
from values in the last record, here's another way to do that too:
Assign default values from the last record
at:
http://allenbrowne.com/ser-24.html

 

[DS]

Your're right I've decided to make a holding table and adjust from there,
then place the records.
Thanks
DS

 

[DS]

Very cool. Thanks Allen.
DS