Insert Record

[Ragnar78]

Hy,

I'm totaly new in Access VBA, and i'm trying to create a macro that inserts a Record to a table i created

Sub insertRecord()

With Application.CurrentDb.OpenRecordset("Retreived Data")
                    .AddNew
                    !NUMBERPRGN = "C05555"
                    !TH_STATUS = "New"
                    !SYSMODTIME = Now
                    !DATE_ENTERED = Now
                    .Update
                    .Bookmark = .LastModified

End With

End Sub

Retreived Data is the name of my table.

This macro is not inserting a record, can anyone help me with it?
Thanks again...

 

[steve_colman]

Easiiest way is to create a Macro, not using code!

Use the Macro to open the form or table you want the new record in.
Use the GoTo function next and set the crireria to New.

Easy as that.

I think you can also you the Goto Record function in code.

 

[mdmilner]

Try this change:
I think the 'default' operation is Dynaset, but if not, this would explictly declare it.

With Application.CurrentDb.OpenRecordset("Retreived Data", dbOpenDynaset)

Another option is to change this from working with a table to working with a recordset. This is actually my own personal preference for how to do the code.

Sub insertRecord() 
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As Sring

Set dbs = CurrentDb()

strSQL = "SELECT * FROM [Retreived Data]"
Set rs = dbs.OpenRecordSet(strSQL, dbOpenDynaset)

With rs 
     .AddNew 
     !NUMBERPRGN = "C05555" 
     !TH_STATUS = "New" 
     !SYSMODTIME = Now 
     !DATE_ENTERED = Now 
     .Update 
     .Bookmark = .LastModified 
End With 

Set rs = Nothing
Set dbs = Nothing
End Sub

The idea behind this kind of format is it standardizes your approach. Also, by putting the SQL string into a variable, it simplifies those lines in your code making them easier to read. And lastly, if you don't need to open up an entire table for your task, don't. If you only need 3 out of 10 fields, you should get a performance improvement (code) by specifying only them.

As info, yes, you could substitute your tablename directly for the full SQL string.

Mike