Solved

Save button over-writing first record every time

Posted on 2016-07-16
12
19 Views
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.

Cheers

PPS-Employee-Training.accdb
0
Comment
Question by:Michael
  • 5
  • 4
12 Comments
 
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.
0
 

Author Comment

by:Michael
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.
Cheers
2.jpg
0
 
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
rst.AddNew

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.
0
 

Author Comment

by:Michael
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.
Cheers
2.jpg
PPS-Employee-Training.accdb
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 84

Accepted Solution

by:
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.

Finally:

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.
PPS-Employee-Training--1-.accdb
1
 

Author Comment

by:Michael
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.

Cheers
0
 
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.
1
 
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:

http://support.experts-exchange.com/customer/portal/articles/1089414
0
 

Author Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now