Adding new records with VBA

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

Guest

Normally I move around through the records in a table using rec.movefirst,
rec.movenext, etc. But this does not seem to allow me to get to a new record.

I read some earlier posts where it was suggested that an sql stagetment
(Insert Into) be used. I tried that and it does work. But I had to use a
macro, docmd.runsql() to run the insert statement.

So I have two questions,

1. Is there VBA language that will allow me to run the sql statement?
2. Am I correct in assuming that there is no VBA langauge (rec.movenew,
etc.) that will get me to a place to add a new record?

Thanks
 
On Fri, 21 Jan 2005 17:29:02 -0800, Rick from Springville <Rick from
Normally I move around through the records in a table using rec.movefirst,
rec.movenext, etc. But this does not seem to allow me to get to a new record.

I read some earlier posts where it was suggested that an sql stagetment
(Insert Into) be used. I tried that and it does work. But I had to use a
macro, docmd.runsql() to run the insert statement.

So I have two questions,

1. Is there VBA language that will allow me to run the sql statement?
Click to expand...

DoCmd.RunSQL is a legal VBA operation. You don't need to run it in a
new macro. However, a more robust (because it traps errors) method is
to use the Execute method of a QueryDef object:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.QueryDefs("NameOfAnActionQuery")

<or>

strSQL = "INSERT INTO ..."
Set qd = db.CreateQueryDef("", strSQL)

qd.Execute dbFailOnError
2. Am I correct in assuming that there is no VBA langauge (rec.movenew,
etc.) that will get me to a place to add a new record?
Click to expand...

No, you are incorrect. There are several ways to do this. If you have
an updateable Recordset open, use

rec.AddNew

to open a new record; then use

rec!fieldname1 = "Some Value"
rec!anotherfield = 34
rec!datefield = #12/31/2004#
rec.Update ' to write the record to disk


John W. Vinson[MVP]
 
Back
Top