How can I update one subform with information from another subform? (1 Viewer)

RbTrail

Registered User.
Local time
Today, 16:57
Joined
Mar 29, 2019
Messages
14
The purpose of my database is to track employee training. Each employee can work in multiple departments, and each department has multiple procedures. These procedures often overlap various departments, but there is no 'common core' of procedures that everyone is required to take.

I have my tables normalized and the many-to-many relationships set up with junction tables. I've even managed to create a form that updates employee information and which departments they work in.

My problem is that I can't get the employee's procedures to update based on the department(s) I assign. I can add each procedure individually, but that requires running a separate union query for each employee to get the needed procedures without duplicates. And union queries can't be used to update tables.

I've tried to run an update query that references the information I enter into the form, but it keeps saying "0 records updated" no matter what information I change on the form.

I've attached a stripped version of my DB for reference.

All of the information in the DB was imported from the Excel files that we are currently using to keep track of training. The only thing I haven't added yet are the current training dates.
 

Attachments

  • CompsTrainingDB_stripped.zip
    56.1 KB · Views: 44

Ranman256

Well-known member
Local time
Today, 17:57
Joined
Apr 9, 2015
Messages
3,924
an update query, using the key from the source subform.
like:

update tbl2 set tbl2.field = tbl1.value where tbl1.key = forms!fMasterFrm!subform1.form.key1
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:57
Joined
May 21, 2018
Messages
5,404
Run the below query whenever you add a new dept to an employee. Run it through code to avoid the error message using currentdb.execut "queryName"
No need to worry about duplicates because the fields are indexed and will get dropped if duplicate
Code:
INSERT INTO tblemppro 
            (empid, 
             proid) 
SELECT DISTINCT tblempdept.empid, 
                tbldeptpro.proid 
FROM   tblempdept 
       INNER JOIN tbldeptpro 
               ON tblempdept.deptid = tbldeptpro.deptid 
ORDER  BY tbldeptpro.proid;

Make query for employee required procedures per department

Code:
SELECT tblempdept.empid, 
       tblempdept.deptid, 
       tbldeptpro.proid 
FROM   tblempdept 
       INNER JOIN tbldeptpro 
               ON tblempdept.deptid = tbldeptpro.deptid;

If you remove a dept from an employee then remove those procedures only related to that department

Code:
DELETE 
  tblemppro.empid, 
  tblemppro.proid 
FROM tblemppro 
WHERE  (( ( tblemppro.proid ) NOT IN (SELECT proid 
                                      FROM   [qryemprequiredprocedures] AS B 
                                      WHERE  b.empid = tblemppro.[empid]) ));

No reason to even check what employee you are working on at the time. Just update all the records. It is so efficient would not make any difference and only add complexity to add a filter on the employee id.
 

RbTrail

Registered User.
Local time
Today, 16:57
Joined
Mar 29, 2019
Messages
14
RanMan256:
Thank you, but the way my tables are related makes a simple update query useless.
 
Last edited:

RbTrail

Registered User.
Local time
Today, 16:57
Joined
Mar 29, 2019
Messages
14
MajP:
Thank you! This works perfectly. How do I stop the warning messages that happen when the action queries run?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:57
Joined
May 21, 2018
Messages
5,404
if you run an update query in code
docmd.runsql queryNameOrSqlStatement ' alerts appear
CurrentDb.Execute queryNameOrSqlStatement ' alerts are not triggered

you can still do the first by
docmd.setwarnings False
docmd.runsql "query1"
docmd.setwarnings true

often in development i write code to get the alerts (make sure things are correct) then change it to no alerts.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:57
Joined
May 21, 2018
Messages
5,404
if you run an update query in code
That should read any action query (insert, update, delete..)
 

RbTrail

Registered User.
Local time
Today, 16:57
Joined
Mar 29, 2019
Messages
14
Okay, I entered the following:

Code:
Private Sub Form_AfterUpdate()
'CompsTrainingDB stripped'.Execute Append_tblEmpPro ' alerts not triggered
End Sub


There are no alerts, but it doesn't update tblEmpPro.

I should also mention that I've never worked with code before. I've been using the macro builder.
 

Attachments

  • AfterUpdateMacro.png
    AfterUpdateMacro.png
    53.5 KB · Views: 39

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:57
Joined
May 21, 2018
Messages
5,404
I saved the update and delete queries as
qryAddPro
and
qryRemovePro

Code:
Private Sub DeptID_AfterUpdate()
  Me.Dirty = False
  AddRemovePro
End Sub

Private Sub Form_AfterUpdate()
  Me.Dirty = False
  AddRemovePro
End Sub

Private Sub AddRemovePro()
  Dim EmpID As Long
  EmpID = Me.Parent.EmpID
  CurrentDb.Execute "qryRemovePro"
  CurrentDb.Execute "qryAddPro"
  Me.Parent.Requery
  Me.Recordset.FindFirst "empID = " & EmpID
End Sub

Private Sub Form_Delete(Cancel As Integer)
  Me.Dirty = False
  AddRemovePro
End Sub

This seems to update the list fine and requeries the list. The add part is working, but I cannot figure out for the life of me why the delete part is not. The code and the query are correct, but does not execute after you delete even if you save the udpate. Need to figure out a different event. The good news is that those queries can be run at any time like before the form closes. That will ensure you are always current.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:57
Joined
May 21, 2018
Messages
5,404
qryAddPro

Code:
INSERT INTO tblEmpPro ( EmpID, ProID )
SELECT DISTINCT tblEmpDept.EmpID, tblDeptPro.ProID
FROM tblEmpDept INNER JOIN tblDeptPro ON 
tblEmpDept.DeptID = tblDeptPro.DeptID
ORDER BY tblDeptPro.ProID;
qryRemovePro

Code:
DELETE tblEmpPro.EmpID, tblEmpPro.ProID
FROM tblEmpPro
WHERE (((tblEmpPro.ProID) 
Not In (Select ProID from [qryEmpRequiredProcedures] as B where b.EmpID = tblEmpPro.[EmpID])));
To make this completely work I turned sFromTblEmpDep into a continous form instead of a datasheet view so I could put a command button next to the combo. I put a little button with an X (delete) next to the combobox so if you want to delete a department. Then the code works if you delete using the command button.

Code:
Private Sub cmdDelete_Click()
  DoCmd.RunCommand acCmdDeleteRecord
  AddRemovePro
End Sub
Private Sub DeptID_AfterUpdate()
  Me.Dirty = False
  AddRemovePro
End Sub
Private Sub Form_AfterUpdate()
  Me.Dirty = False
  AddRemovePro
End Sub
Private Sub AddRemovePro()
  Dim EmpID As Long
  EmpID = Me.Parent.EmpID
  CurrentDb.Execute "qryRemovePro"
  CurrentDb.Execute "qryAddPro"
  Me.Parent.Requery
  Me.Recordset.FindFirst "empID = " & EmpID
End Sub
 

RbTrail

Registered User.
Local time
Today, 16:57
Joined
Mar 29, 2019
Messages
14
Thank you so much for all of this!

Last questions:
Where do I enter the code? Under the Event Procedures on the Property Sheet of the subform? Or do I use Create Module on the ribbon?

What resource do you recommend for learning code, for a greenie like me?
 

RbTrail

Registered User.
Local time
Today, 16:57
Joined
Mar 29, 2019
Messages
14
Nevermind! I figured it out.

But I'd still like to know if you have a recommendation for a beginner to start learning the basics of coding.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 19, 2002
Messages
32,890
I don't believe that you are going about this the correct way. The Afterupdate event of the department subform is the place where you would want to run an append query to add the procedures associated with that department.

I also do not believe that you should delete employees. For an employee, you should keep a TerminationDate and TerminationReason because there will be a need to satisfy historical inquiries.

I also do not believe that you should delete department records although this is more problematic since during the course of his employment, an employee may move in and out of a department multiple times. Modelling this relationship will be more difficult and require adding a two-field unique index to the EmpDept table that includes DeptID and StartDT. The table should also include StopDT but that doesn't need to be part of the unique index.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:57
Joined
May 21, 2018
Messages
5,404
Not sure what you are talking about. Did you read my solution? Nothing you say is correct.

I don't believe that you are going about this the correct way. The Afterupdate event of the department subform is the place where you would want to run an append query to add the procedures associated with that department.

This occurs in the after update of the combo on the subform which is the only control, hence no difference if you look at the code since dirty = false.

I also do not believe that you should delete employees. For an employee, you should keep a TerminationDate and TerminationReason because there will be a need to satisfy historical inquiries.
No employees are deleting anywhere. What query you looking at.

I also do not believe that you should delete department records although this is more problematic since during the course of his employment, an employee may move in and out of a department multiple times. Modelling this relationship will be more difficult and require adding a two-field unique index to the EmpDept table that includes DeptID and StartDT. The table should also include StopDT but that doesn't need to be part of the unique index

No department are deleted anywhere. An employees assignment to a department is deleted.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 19, 2002
Messages
32,890
You should not be using a control level event to run the append query. If the user picks A and then changes to B, code in the control will append BOTH sets of data. That is not correct.

There is a button on the form that deletes employees. I was extrapolating regarding the department since we don't see that form in the database.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:57
Joined
May 21, 2018
Messages
5,404
If the user picks A and then changes to B, code in the control will append BOTH sets of data. That is not correct
It may not be the most efficient, but gives the user immediate feedback in the procedure subform and the incorrect records are not left in the table. If A is picked then B is picked, the code deletes A records. However, since there is a lot of overlap in procedures, likely only a subset of A records get added or deleted. Also since the forms after update also calls the procedure it is doubly ensured not to have errant records since they would be deleted.

I could have just done this in the forms after update, but then you would not immediately see the assigned procedures. You would have to move out of the form or to a new record.
 

RbTrail

Registered User.
Local time
Today, 16:57
Joined
Mar 29, 2019
Messages
14
Pat Hartman,
I understand why you would say not to delete employee records, etc. But this is not connected to our main Employee DB. I work at a sub-company who shares an HR Dept. with the main company. We don't have access to their records, but we are required to keep track of our own training. Another complication: HR refuses to accept anything from us unless it is on 'their' forms.
We don't need to retrain people unless the procedures get updated, so we aren't concerned with keeping a history beyond the employee's last training date in each procedure. I'm creating reports that match HR's paper forms so that all our supervisors have to do is print, sign, and mail to HR.
If HR ever needs a deeper history, or information on former employees, they have it somewhere in their own DB and we keep copies of all the paper we send them. Not the best, but the best compromise we could negotiate. (AND I have all correspondence in saved email files per my CYA protocols. ;) )
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 19, 2002
Messages
32,890
It may not be the most efficient,
Efficiency has nothing to do with my comment. My comment was regarding accuracy. Accuracy is not negotiable. If something is wrong, it is wrong.
 

Users who are viewing this thread

Top Bottom