Sql query syntax error in Access VBA code

[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 tblOrderEntryLine(Index,CUSTNMBR,CUSTNAME) select Index,CUSTNMBR,CUSTNAME from tblOrderEntryMaster where Index = " & Forms!FrmNewInvoice!txtIndex
rstline.Open strSql, CurrentProject.Connection

 

[r937]

access wants you to provide the INTO keyword

insert into tblOrderEntryLine
( Index, CUSTNMBR, CUSTNAME)
select Index, CUSTNMBR, CUSTNAME from ...


rudy

 

[Dale3]

I tried that first, actually and the same error results. Any other ideas?

 

[tlbroadbent]

Add brackets around the keyword Index.

strSql = "insert tblOrderEntryLine([Index],CUSTNMBR,CUSTNAME) select [Index],CUSTNMBR,CUSTNAME from tblOrderEntryMaster where [Index] = " & Forms!FrmNewInvoice!txtIndex
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm