Adding a new record to a table

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
I'm quite new at Access, so please bear with me.

I would like to create a form that adds a record to a table, but not until the user clicks a button. The buttons will be "Save and Close", "Save and Add Another", or "Close and Discard Changes". If the user clicks either of the Save options, the record should be added as the last record in the table; the discard button puts nothing in the table.

What VBA code do I need to have the new record added?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

dsdavids

New Member
Joined
Oct 5, 2006
Messages
13
A lot of people do this by using an unbound form and initiating the save like you are thinking of doing. This will negate a lot of the functions that Access forms will handle for you and leave a great deal of these things up to your own custom procedures. If you are new to VBA then I wouldn't advise it.
You might be better off sticking with the bound form but controlling the navigation, save and delete function with your own buttons. In the form's properties select no for show navigation, control box and any other properties that would allow them to circumvent your controls. Create a custom menubar as well.
The simplest code to duplicate navigation, I'd say would be the various methods of RunCommand

DoCmd.RunCommand.accmdRecordMoveNext

for example will move to the next record, saving the current record if the form is dirty. To save and close you would just type Me.Close, to undo change use the appropriate parameter of the runcommand method.
Look up the methods in help, I'm typing form memory and could be off a bit.
 

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
Some questions:

What does it mean to say that the form is "dirty"?

Is there a RunCommand method for undo? I have only found one for deleting a record, which shows a warning that I don't want to see.
 

dsdavids

New Member
Joined
Oct 5, 2006
Messages
13
docmd.runcommand(accmdUndo)
The form is dirty if the current record is new or has been changed. You can check this in your code:
If me.dirty then

save or delete here
me.dirty = false '(same as saving the changes)

End if
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,919
Members
410,712
Latest member
jhgeorge
Top