Solved Still hoping for some help with auto entries! (1 Viewer)

dawsonrhodes

Member
Local time
Today, 19:49
Joined
Mar 8, 2020
Messages
85
Hello all,

I have been building an employee database, it has gone amazingly, just ran into something I wouldn't know how to sort out.

Basically, when we have a new training requirement come out we need to track completion. The way I currently do that is adding it in through the "Add New Training" form that I have attached as a png. But that then requires me to go into each person "profile" and create a new training log for each person and then mark it as completed.

The layout is as follows:

tblEmployees (master for all employee data)
  • ID (autonumber)
  • EmployeeID (the number I designate them)
  • Name and so on
tblEmployeeTraining
  • TrainingID
  • Employee (so their auto ID in the tblEmployees table)
  • NameofTraining
  • Category
  • and so on

What I am trying to do now is make it so that when I add a new training module I can select a box that will automatically make an entry in the query for each "person/id" that I can later go and just edit rather than having to manually add it with dropdowns.

Thanks all!
 

Attachments

  • 73501-aaf20faa7b1c69421d3fa97a54067e20.png
    73501-aaf20faa7b1c69421d3fa97a54067e20.png
    17 KB · Views: 412
  • 73502-88e708dd118ba1502a9491496a1e77c7.png
    73502-88e708dd118ba1502a9491496a1e77c7.png
    8.2 KB · Views: 212

Ranman256

Well-known member
Local time
Today, 19:49
Joined
Apr 9, 2015
Messages
4,339
1., your images are so small ,they are unreadable. do you have higher rez images?
2. the combo box should pull from a table of tTrainModules.
the combo 'source type' property , set to query ,NOT values.
just add the name to the table and they show in the combo box.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:49
Joined
May 7, 2009
Messages
19,169
i hope you can follow this sample.
it the same one i suggested on your previous post.
 

Attachments

  • training.zip
    79.7 KB · Views: 235

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:49
Joined
Feb 28, 2001
Messages
27,001
But that then requires me to go into each person "profile" and create a new training log for each person and then mark it as completed.

This tells me that you might have a design problem. Perhaps your tables are not properly normalized. Your requirements aren't clear to me so it is hard to judge.
 

dawsonrhodes

Member
Local time
Today, 19:49
Joined
Mar 8, 2020
Messages
85
This tells me that you might have a design problem. Perhaps your tables are not properly normalized. Your requirements aren't clear to me so it is hard to judge.
Thanks for your response, not sure how I would normalize it, basically I have a form that you can add training "modules" to, this is a separate table that specifically houses the training only, then I have another table that has all the employees training. So let's say that I add a new "module" in, I now manually have to use another form and make a new entry for each person, selecting from a dropdown the training module I have just made. What I would like to do is have a box that when I create a new "module" it will automatically make an entry and make it as "Incomplete", and then when I receive the sign off for 100+ employees all I have to do is go to each person's records (in a form with tabs) and mark their training as "complete". hope this makes more sense.

Thanks again.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:49
Joined
Sep 21, 2011
Messages
14,048
I would use an Append query to create the records for EmployeeTraining table with the new PK from the Training table, the Employee PK and status field as 'Incomplete' ?
 

dawsonrhodes

Member
Local time
Today, 19:49
Joined
Mar 8, 2020
Messages
85
I would use an Append query to create the records for EmployeeTraining table with the new PK from the Training table, the Employee PK and status field as 'Incomplete' ?
I appreciate your response, I have not a clue how to do that, would doing this creat records for each Employee.ID when I make a new "module" for training?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:49
Joined
Feb 19, 2002
Messages
42,981
In the AfterInsert event for your New Training form, you would run the append query to add this new trainingID to all active employee records. To build the query:
1. Open the QBE
2. Choose the training table
3. Choose the employees table
4. There is no join between these two tables
5. Select the columns you need from each table to create a row in the EmployeeTraining table
6. If you only want to add the row for "active" employees, add criteria that selects only "active" employees
7. Add criteria to select a specific trainingID by referencing the new training form:
Where TrainingID = Forms!newtrainingform!TrainingID
8. Make sure there is a control named TrainingID on your form.
9. Save everything and run the query. You should get a list of all active employes plus the TrainingID
10. Go back to the query and change it to an append query.
11. Add the code to run the query to the form's AfterInsert event so the append query will only run ONCE, AFTER a new training ID is added.

If not all training is required for everyone, you can add an If statement so that you only run the append query if this is a "required" course.
 

dawsonrhodes

Member
Local time
Today, 19:49
Joined
Mar 8, 2020
Messages
85
In the AfterInsert event for your New Training form, you would run the append query to add this new trainingID to all active employee records. To build the query:
1. Open the QBE
2. Choose the training table
3. Choose the employees table
4. There is no join between these two tables
5. Select the columns you need from each table to create a row in the EmployeeTraining table
6. If you only want to add the row for "active" employees, add criteria that selects only "active" employees
7. Add criteria to select a specific trainingID by referencing the new training form:
Where TrainingID = Forms!newtrainingform!TrainingID
8. Make sure there is a control named TrainingID on your form.
9. Save everything and run the query. You should get a list of all active employes plus the TrainingID
10. Go back to the query and change it to an append query.
11. Add the code to run the query to the form's AfterInsert event so the append query will only run ONCE, AFTER a new training ID is added.

If not all training is required for everyone, you can add an If statement so that you only run the append query if this is a "required" course.
Hey thanks for your response, I believe I have done this, a few questions... The criteria you mentioned, does that need its own collum or that criteria to another row (such as TrainingID), also I am unsure how to write the VBA code for the AfterIntsert, the resources I found online did not do the trick for me. Thanks again!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:49
Joined
Jul 9, 2003
Messages
16,245
Sounds like you need something like a checklist that automatically fills itself as in the demo which you can see on my website here:-

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:49
Joined
Feb 19, 2002
Messages
42,981
The criteria goes in the row under where you selected the column. There is no need to add a duplicate column for the same field.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:49
Joined
Sep 21, 2011
Messages
14,048
Yes,

Here is something similar I had to create in my last place of work. I had forgotten to post it earlier and even if you delete it, it comes back. Nice feature. :)
Code:
INSERT INTO tblSubmitterInvoice ( SubmitterClientID, TransferFeeID, InvoiceRate, InvoiceAmount )
SELECT qryNoSubmitterInvoice.SubmitterClientID, qryNoSubmitterInvoice.TransferFeeID, qryNoSubmitterInvoice.AppliedRate, qryNoSubmitterInvoice.InvAmt
FROM qryNoSubmitterInvoice
WHERE (((qryNoSubmitterInvoice.InvAmt)>0));
This was looking at another query to see if it had to add new records to the tblSubmitterInvoice table.

Really you are just automating what you would do manually.

Create a Select query to get the syntax/data correct, then change it to an Append query.
 

dawsonrhodes

Member
Local time
Today, 19:49
Joined
Mar 8, 2020
Messages
85
@Gasman @Pat Hartman @Uncle Gizmo

I do appreciate your support, the reality is I am learning as I go and am having a really hard time keeping up with the advice, I have tried multiple methods and have had no luck. I have uploaded a partial of my database, the training modules and employee list with no data but an example, I am hoping you would be able to take a look and see where I'm going wrong or why I am not able to make this work.

Just to clarify the structure and reasoning behind the layout:

tblEmployees is obvious;
tblEmployeeTraining is the master for the records of the employee's specific training
tblTrainingSelections is what frmTrainingDetails records to (this just is the holder of each training we do)
tblTrainingTypes is just a table that has the categories that are referenced in combo boxes for selection when adding new training modules

qryEmployeeTraining is just a query with a File As and an Employee Name field that included F/L Name and Employee #

frmTrainingDetails is the main form that it looks like I need to create this automation on (could be wrong), this is what I use to actually create new modules
frmTrainingSubform is a tab on another form that shows records for a specific employee, but is not included in this download
frmTrainingViewerw/Name is a datasheet view of the records with some conditional formatting for the tab as same as above.

Filters macro is simply that, a premade list of macros.

-----------

I hope you can help, and that isn't too big of an ask, I just am not understanding.

Thanks to you all.
 

Attachments

  • Partial Database - Training Modules.accdb
    1,004 KB · Views: 381

Gasman

Enthusiastic Amateur
Local time
Today, 23:49
Joined
Sep 21, 2011
Messages
14,048
Too recent a version for me, so I'll bow out. Sorry.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:49
Joined
May 7, 2009
Messages
19,169
please check the code.
 

Attachments

  • Partial Database - Training Modules (1).zip
    106.8 KB · Views: 409

Users who are viewing this thread

Top Bottom