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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top