Records overwritten instead of saved as new (1 Viewer)

Hello.
This is either a poor programming choice or a simple fix I am sure, but I am a beginner, so please try not to yell. Lol

I created a simple database in Access that has a form where users input the days and times they will be renting out a company vehicle.
Sometimes, users will have to make the same stop, using the same vehicle, for multiple days in a row. (i.e. needing a vehicle to go to the same location from 8a-12p on monday, tuesday and wednesday). I was under the impression that the users could submit all three days in one sitting without having to fill out ALL fields three times - If they submit one record, the fields stay populated, so all they have to do is change the date and click submit again in order for it to save a new record. However, even though the submit button works, it seems to only store the last record sent, instead of all 3.

How can I set the VBA code to save a new record by altering one field on a form that has already been submitted, instead of overwriting it?

*Not sure if this matters, but the table that the record saves to has an autonumber column for every new record.

*The code currently used in the button currently is:
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Thank you! Your request has been submitted.", vbOKOnly

You need to add a line of code that moves to a new record after the cmd button is clicked. If you want to keep the entries from the previous record, try this link:

http://allenbrowne.com/ser-24.html

Good luck!

Lejano08 said:

I was under the impression that the users could submit all three days in one sitting without having to fill out ALL fields three times - If they submit one record, the fields stay populated, so all they have to do is change the date and click submit again in order for it to save a new record. However, even though the submit button works, it seems to only store the last record sent, instead of all 3.

Click to expand...

There may be a bit of a conceptual problem here in that Access doesn't ever need to "Submit" data. There is an immediate, always-on, connection to the data. Contrast that with the web, where a page in a browser is NOT connected, and data updates are required to be sent to the server for processing. So in your case, you do not actually have a submit button, you have a save button, and when you click it, it saves any pending changes in the current record only.

So NauticalGent's link showing how to set default values in a form is definitely a good option in this case, but it's worth understanding too that it's not a Submit button.

Cheers,

As a hint, you can use the command button wizard to create a command button on an empty form just for the sake of experiment. In the wizard, select "Record Operations" on the left listbox and "Add New Record" from the right listbox. Save it, then open up the event routine from the button's property sheet (by double-clicking on the "OnClick" property). This wizard will create VBA code behind the scenes to show you how to add a new record. You can use this same trick to see how some of the common actions are performed. It will give you the skeleton for what you wanted to do, after which you can flesh it out with the details, bells, whistles, etc.

Obviously, for your case you would want to copy some values to internal temporary variables in the event routine, THEN do the Add New Record operation, THEN populate the controls from the temporary variables. If done in the button-click routine, it would be nearly transparent to your users.

You would probably want another button that just saves the current record without adding a new one, perhaps prior to just closing the form.

MarkK said:

There may be a bit of a conceptual problem here in that Access doesn't ever need to "Submit" data. There is an immediate, always-on, connection to the data. Contrast that with the web, where a page in a browser is NOT connected, and data updates are required to be sent to the server for processing. So in your case, you do not actually have a submit button, you have a save button, and when you click it, it saves any pending changes in the current record only.

So NauticalGent's link showing how to set default values in a form is definitely a good option in this case, but it's worth understanding too that it's not a Submit button.

Cheers,

Click to expand...

Yes. I do understand that it is technically saving and not submitting. Poor word choice for the button and message, I suppose. To the users though, it all means the same thing. Lol

NauticalGent said:

You need to add a line of code that moves to a new record after the cmd button is clicked. If you want to keep the entries from the previous record, try this link:

http://allenbrowne.com/ser-24.html

Good luck!

Click to expand...

Thank you!
Another question though - would this require me to know what the default values are that I am carrying over?
If so, this could vary with each entry.
Here are the fields on the form: todays date, first name, last name, depart date, return date, destination city, destination state, depart time, expected return time, number of travelers, vehicle requested.

Most of the time, people are only changing depart date and return date. But sometimes, they may also need to change their destination location info or the the car type if there are multiple people. I can never really tell because diff users use the vehicles for diff purposes.
[The real issue is just that employees do not want to use the "add new" button, because it means they have to re-type some of the same things, multiple times in a row. And since the fields on the form remain populated even after they have clicked save, they assume they can edit one field, click save again, and save the time of re-entering a whole new form. Since they receive the "Thank you..." message, it is assumed that the record took.
I thought that there was an alternate line of code in VBA to allow them to do this.]

Why not combine your current method with another button that will carry over all of the variable data from the form to a new record?
That way there will be a way to make a record for each occasion without typing in everything if there is only a small change as well as a way to get a new record for something completely different.
You could add the code to move to a new record or put a lock on old records to your existing button.
As long as the users can see what is happening there should not be too much trouble.