Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Save button over-writing first record every time

Posted on 2016-07-16
Medium Priority
Last Modified: 2016-07-22
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.


Question by:Michael
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 85
ID: 41714580
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.

Author Comment

ID: 41714954
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.
LVL 85
ID: 41715687
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.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 41715699
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.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 41715723
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.

Author Comment

ID: 41715729
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.

LVL 85
ID: 41715783
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.
LVL 85
ID: 41715790
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:


Author Comment

ID: 41716990
Sorry for the mix up
I accept ID: 41715723 as the solution

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question