Save button over-writing first record every time

Posted on 2016-07-16
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
  • 5
  • 4
LVL 84
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 84
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.
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


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 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 84
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 84
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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