How do I use the INSERT INTO Statement in VBA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Unique Name - 28 Jul 2005 22:10 GMT

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

Reply to this Message

Ofer - 28 Jul 2005 22:34 GMT

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)
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

Unique Name - 28 Jul 2005 23:11 GMT

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

> Can you please post the all insert statement and the error you getting.
>
[quoted text clipped - 10 lines]
> >
> > Thanks

Reply to this Message

Ofer - 28 Jul 2005 23:26 GMT

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
[]

> I modified the insert statement to reflect your posting but I am still
> getting an error:
[quoted text clipped - 21 lines]
> > >
> > > Thanks

Reply to this Message

Unique Name - 28 Jul 2005 23:31 GMT

That did it. Thanks a lot ,very much appreciated.

> try this
> DoCmd.RunSQL "INSERT INTO tblBatch ([Batch #],[Entry Date]) VALUES
[quoted text clipped - 29 lines]
> > > >
> > > > Thanks

Reply to this Message

David C. Holley - 28 Jul 2005 23:39 GMT

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.

> 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)
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message