How do I save a record using VBA?

[asjohnson]

Does anyone know how I can save a record on the click of a button? The

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

command produces an error and does not save. I'm looking for alternatives. Please help.

 

[llam]

I used to do it this way:

dim rs as object
set rs = db.openrecordset("tablename&quot
rs.AddNew
rs.field1 = Me![Field1]
rs.field2 = Me![Field2]
...
rs.update
set rs = nothing

Can someone please confirm whether it is a good way to do it? Also, in DAO, what should I dim rs as? Thx!

--llam

 

[asjohnson]

Thanks, but I don't want to go through the Recordset.Edit routine, as there are alot of fields I would have to scroll through to find what changed.

I also tried the DoCmd.RunCommand acCmdSaveRecord, but this command produces an error as well.

Any more ideas out there?

 

[llam]

Yeah, I think so too.
However, my access keep complaining about dim-ing db as database and rs as recordset. My solution at the moment is to dim them as objects, and let my access to late binding the correct object type. I thought that I am having this problem because i am just using DAO.

--llam

 

[llam]

To asjohnson:
Before I know anything about VBA, I used to open a form at adding new record. So after I finish entering all the information and close the form (or going to the next record), it add the record into the table automatically.

To have more control of it, you may want to add some codes in Forms_BeforeUpdate to make sure that the information that has been entered are input correctly.

I found this method is too buggy, so i would rather to put more time on doing VBA later on.

--llam