Save button over-writing first record every time

Hi Folks
I have two questions to help me over the current hump please.
I have put together a training database using a tabbed form with Personal Details on the first form and Training Details on the second form.
I have a number of tables that populate combo boxes, one table for Personal Details and one for Training Details.  The save button saves to both forms though it only saves to the first record.  It also does not go to new blank record in the form.

I have also used a query to calculate retraining due from the training date and the length of time required for the retraining.  I would like the Due Date in the query to populate the text box Due date in the form as the retraining period is entered, is this possible.

I plan to have a number of queries and reports available for users once I have the database working correctly.

Your assistance would be greatly appreciated.


MichaelState HSEQ ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You do not have your Save button "hooked" to the Event Procedure that is supposed to be fired. To fix that, open the form in Design view and select the button. Click the "Event" tab in the Properties dialog and select [Event Procedure] in the dropdown.

The code you're using would save the current record, which may or may not be the first. In fact, your Save button doesn't really need to explicitly call DoCmd.RunCommand acCmdSaveRecord - Access will automatically save your data for you, so if your goal with the Save button is to save the data and go to a new record, you only need this line:

DoCmd.RunCommand acCmdRecordsGoToNew

In general, it's best to include a single issue in your questions. You'll often find that many Experts will not participate in questions that include multiple unrelated issues. However:

I would like the Due Date in the query to populate the text box Due date in the form as the retraining period is entered, is this possible.
Do you mean the "Frequency" value when you say "retraining period"? If so, you can use the DateAdd feature to do this, based on your Training_Date and Frequency settings. You'd do this in the AfterUpdate event of the Frequency combo:

Me.Due_Date = DateAdd("d", Frequency, Me.Training_Date)

Note that Due_Date is unbound, so you won't save that data into your table.
MichaelState HSEQ ManagerAuthor Commented:
Hi Scott
Thanks for the reply.
I will separate my queries in future.
I have added the event procedure for the save button and it still would not work.
I then removed the save button and created a new one using the wizard and now receive the following message, attached jpg.
I have checked the On Open sections across all the forms and they are all blank so I'm at a loss for to move forward.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you mean by "it still does not work"? Does it do nothing, produce an error, etc etc ... we can't see your screen, so we don't know what you mean by this.

If your goal is to save the Subform record, then you'll have to do a little more work to handle that. The code you're running right now will save the parent form and subform record, but it won't move to a new record on the subform. If you want to do that, replace the "acNewRec" line with this:

Dim rst As DAO.Recordset
Set rst = Me.Training_Details.Form.Recordset

this would create a new record in the Training Details subform. Similar code would create a new record in your other subform.

Before getting too far along in your database I would encourage you to adopt a more standard Nameing convention. For example, your Training Details subform is named "Training Details". A better name for that would be "sfTrainingDetails" - the prefix indicates what sort of control it is, and TrainingDetails tells you what it's for (with no spaces). Look up "leszynski naming convention" for more information on this.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

MichaelState HSEQ ManagerAuthor Commented:
HI Scott
Sorry for the trouble though I am struggling with this.

I have attached the database and made some changes trying to work this out.

I have also placed your code in and still receive the error message which is in the attached jpg file.
The error message is acting on all the command buttons so is most likely something not correct in the tabbed form.  
I created two forms frmPersonal_Details and frmTraining_Details and have placed these onto the tabbed pages 1 and 2.
Many thanks for your time.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A few things:

When having troubles of this nature, you should always try to Compile your code. You do this by opening the VBA Editor and clicking Debug - Compile.

When I did this, I found a sub named "MouseWheel" that you had apparently declared, and had done so incorrectly. The correct declaration for that event is here:

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)

End Sub

Open in new window

I removed the one you had entered incorrectly, and I no longer receive the error when opening the form.

When adding Events, you should NOT enter the event stub header (the "Private Sub Form etc etc" line above) directly. Instead, open the form in Design view, select the control/section you want to add code to, and then select the Event form the "Events" tab in the Property sheet, and finally select "[Event Procedure]" in the dropdown for that Event. Access will add the properly formed Event stub, and you then add your code in that area.

You've also set the ControlSource for the Retraining Date on the Training Details subform to a calculated value. This essentially means your user cannot update it (which may be what you want), but it also means that you cannot use the Frequency combo in the same manner.

In general, with calculated values you should calculate them "on the fly". In your case, you'd do this on the Form's Current event to set the "default" or "current" value, like this:

Private Sub Form_Current()
    If Nz(Me.txtTraining_Date, 0) <> 0 Then
        Me.[txtDue_Date] = DateAdd("d", [cboFrequency], DateValue([txtTraining_Date]))
    End If
End Sub

Open in new window

If you notice, that is EXACTLY the same code as in your Frequency combo's AfterUpdate event, with an Nz test added to insure that txtTraining_Date contains a non-zero value.


It would seem that you are entering Training records for each "Personnel" record - that is, you select a Personnel record, and you then enter one or more Training records.

In cases like this you would typically have a MainForm/Subform setup where the MainForm would be the "parent" record (the Personnel record, in this case) and the Subform would contain all the related "child" or "Detail" records (the Training records).

With your current setup, you're adding Training Records, but those records are not associated with a record in the Personnel table. Assuming that's what you want, you'll either have to (a) write some convoluted code to insure the Personnel ID value is inserted into the Training table or (b) adopt a more traditional Main/Sub approach I described above.

You'll find Access much easier to work with if you don't try to work outside the box (at least until you get some experience under your belt). While it's possible to work with a form setup in the manner you have, you'll inevitably run across problems that cannot be easily resolved, and you'll find yourself constantly having to "code around" those problems. Using a Main/Sub approach, Access will handle all the dirty details for you, and you can forge ahead much more quickly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MichaelState HSEQ ManagerAuthor Commented:
HI Scott

Many thanks for your reply.
The reason I wanted to used the two forms instead of a subform as I find the process a lot easier and intended the user to only access the database through the form once completed.  Subforms tend to be in table form though I may redo the whole database if you feel the system as is will pose problems going forward.
All going well I intend to have pre-set reports/queries available through a drop down list which will allow users to query the data.  
I was planning to have the database closed up so the general user cannot make changes, only use it and add data.
Yes, I am a newby to coding though I did some around a decade ago and then changed focus.
Again, many thanks for your help, I will have a look at the attached and think about the subform.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Subforms most definitely aren't in Table form only, as is evidenced by your own design. They can essentially be in any format you desire, as long as they include the necessary components to relate them back to their parent form.

It's not that the system as-is will create problems, it's simply that every time we see a newbie going in this direction it's because of something that they don't really understand in regard to Access. From my view, your interface is somewhat confusing - do I select a Person, and then view Training records? Or do I select a Training Record? With a traditional main/sub form setup (or a "master/detail" setup) you avoid that confusion.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I also see you're new to EE, so you may not be familiar with the closing process.

You've accepted your own comment as the solution, without selecting any additional Expert comments. Here on EE, if one of the Experts provides you with help you're expected to accept those comments as your solution, and not your own.

I've objected to the process so you can try again. You might review the Closing your Question page on this EE Help page for more information on properly closing out your questions:
MichaelState HSEQ ManagerAuthor Commented:
Sorry for the mix up
I accept ID: 41715723 as the solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.