INSERT INTO Syntax error in Access VBA

[Dale3]

The following code gives me an error "Syntax error in INSERT INTO Statement. I have checked the destination table to be sure that the column headings match. I also checked the data types of the variables in the from and to tables to be sure that they match. Any other ideas of where the syntax error might be?

Thanks

Public Sub Form_Load()

Dim rstline As New ADODB.Recordset
rstline.LockType = adLockOptimistic
strSql = "insert into tblOrderEntryLine(Index,CUSTNMBR,CUSTNAME) select Index,CUSTNMBR,CUSTNAME from tblOrderEntryMaster where Index = " & Forms!FrmNewInvoice!txtIndex
rstline.Open strSql, CurrentProject.Connection

 

[FancyPrairie]

I don't think you can use the rst.Open statement to execute an insert statement. Rather it should be

Currentproject.Connection.Execute strSQL

Also, if Forms!FrmNewInvoice!txtIndex is a text, then don't forget to surround it in single quotes.

 

[Dale3]

I have done an insert using rst.open format. I did a msgbox on the strSql statement to see what it is giving me literally and it comes out as follows:

insert into tblOrderEntryLine(Index,CUSTNMBR,CUSTNAME) select Index,CUSTNMBR,CUSTNAME from tblOrderEntryMaster where Index = 25

See anything wrong with that?

 

[Dale3]

I tried the syntax you suggested "CurrentProject.Connection.Execute strSql"
and it gives me the same error, i.e. "Syntax error in INSERT INTO statement"

Good idea, though.