How do I use the INSERT INTO Statement in VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to enter data into a table from a form using the INSERT INTO
Statement in VBA forAccess 2003. I type it as shown in the assistance(below)
but keep getting errors. Is there something I am missing?

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Thanks
 
Can you please post the all insert statement and the error you getting.

The syntax should be
docmd.runsql "insert into target (Field1,Field2,Field3) values
(Forms![FormName]![Field1Name],Forms![FormName]![Field2Name],Forms![FormName]![Field3Name])"
 
I modified the insert statement to reflect your posting but I am still
getting an error:

DoCmd.RunSQL "INSERT INTO tblBatch (Batch #,Entry Date) VALUES
(Forms![frmLabourTransaction]![txtBatchUnbound],Forms![frmLabourTransaction]![TxtDateUnbound])"

The error reads:
Syntax error in INSERT INTO Statement.

Thanks

Ofer said:
Can you please post the all insert statement and the error you getting.

The syntax should be
docmd.runsql "insert into target (Field1,Field2,Field3) values
(Forms![FormName]![Field1Name],Forms![FormName]![Field2Name],Forms![FormName]![Field3Name])"

Unique Name said:
I am trying to enter data into a table from a form using the INSERT INTO
Statement in VBA forAccess 2003. I type it as shown in the assistance(below)
but keep getting errors. Is there something I am missing?

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Thanks
Click to expand...
Click to expand...
 
try this
DoCmd.RunSQL "INSERT INTO tblBatch ([Batch #],[Entry Date]) VALUES
(Forms![frmLabourTransaction]![txtBatchUnbound],Forms![frmLabourTransaction]![TxtDateUnbound])"

When the fields name contain two seperate names you need to put then in
brackets
[]

Unique Name said:
I modified the insert statement to reflect your posting but I am still
getting an error:

DoCmd.RunSQL "INSERT INTO tblBatch (Batch #,Entry Date) VALUES
(Forms![frmLabourTransaction]![txtBatchUnbound],Forms![frmLabourTransaction]![TxtDateUnbound])"

The error reads:
Syntax error in INSERT INTO Statement.

Thanks

Ofer said:
Can you please post the all insert statement and the error you getting.

The syntax should be
docmd.runsql "insert into target (Field1,Field2,Field3) values
(Forms![FormName]![Field1Name],Forms![FormName]![Field2Name],Forms![FormName]![Field3Name])"

Unique Name said:
I am trying to enter data into a table from a form using the INSERT INTO
Statement in VBA forAccess 2003. I type it as shown in the assistance(below)
but keep getting errors. Is there something I am missing?

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Thanks
Click to expand...
Click to expand...
Click to expand...
 
That did it. Thanks a lot ,very much appreciated.

Ofer said:
try this
DoCmd.RunSQL "INSERT INTO tblBatch ([Batch #],[Entry Date]) VALUES
(Forms![frmLabourTransaction]![txtBatchUnbound],Forms![frmLabourTransaction]![TxtDateUnbound])"

When the fields name contain two seperate names you need to put then in
brackets
[]

Unique Name said:
I modified the insert statement to reflect your posting but I am still
getting an error:

DoCmd.RunSQL "INSERT INTO tblBatch (Batch #,Entry Date) VALUES
(Forms![frmLabourTransaction]![txtBatchUnbound],Forms![frmLabourTransaction]![TxtDateUnbound])"

The error reads:
Syntax error in INSERT INTO Statement.

Thanks

Ofer said:
Can you please post the all insert statement and the error you getting.

The syntax should be
docmd.runsql "insert into target (Field1,Field2,Field3) values
(Forms![FormName]![Field1Name],Forms![FormName]![Field2Name],Forms![FormName]![Field3Name])"

:

I am trying to enter data into a table from a form using the INSERT INTO
Statement in VBA forAccess 2003. I type it as shown in the assistance(below)
but keep getting errors. Is there something I am missing?

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Thanks
Click to expand...
Click to expand...
Click to expand...
 
You cannot directly execute a SQL statement in VBA or via the Immediate
Window. You have to utilize one of the provided commands - DoCmd.RunSQL
or the .Execute method of a Database object (my personal new found
friend). If you need help building the SQL statement, you can use the
Query Builder to build it and test it and the just copy the SQL
statement to your VBA procedure.
 
Back
Top