Auto adding records (trivial!)

[kalspiros]

Hi All,

Apologies for the ultra-trivial (i believe?) question but i'm completely clueless on VBA (after some google research, i suspect i cannot complete the task below with macros)
I have the following relationships already set in my db (Access 2016)

This is for a db to record training for our employees (screenshot comes from a stripped version, there are more fields on each table)
Training comes in Units, 1 to 24 (T_Units)
Each Unit has several Tasks (T_Tasks)
On table T_Record, i'm linking Tasks with Employees.
So this is an auto-record create question that will take place solely in T_Record table.
I will create a form with two drop downs, one with all employees (MSL_Name) and one with Units (Unit_ID). Upon selection, I would like T_Record to be populated with relevant Tasks and employee's name.
Please see example below:

Thanks a mil and once again, apologies if i'm recycling a multi-answered question

Kind Regards

[kalspiros]

just to spice things up a bit more, can we automatically assign today's date on Date_Assign field by the moment we are updating T_Record?

[June7]

So user will select an employee and a task from comboboxes? Why don't you simplify your life and use a form bound to T_Record? Go to New Record row and select items from comboboxes. Set Date_Assign textbox Default Value property to Date().

[kalspiros]

Cheers June7 for the reply.
The user will select an employee and a unit.
A unit could have up to 45 tasks!
(as in the example above, Unit 2 has only 3 tasks: "P40", "Zeno", "Zeb-revo", Unit 3 has just one "EML" etc.)

That would mean creating up to 45 records every time an employee is enrolled in a particular unit (fairly frequent).
I'll be looking for a way then to create these records automatically within table "T_Record"

Kind Regards

[orange]

kalspiros,

This may not be an issue with understanding vba. It may be more related to database design. I say may because I don't know the details of your set up.
Could you tell us in simple, plain terms --no database tables or forms yet -- what is involved in employee training? We often hear of courses, classes or subjects when related to training, so Task is a little new (to me anyway). An example of how an Employee is selected and "assigned to/registered to" specific training would help readers especially if you could include unit and task.

A more general comment when working with Access is to NOT have embedded spaces in field or object names. Invariably you will end up with syntax issues.

Good luck with your project.

[kd2017]

An insert into select query will do what you want. I'm making no comment on whether or not your db design is correct, or at least the best way to go about it, but this SQL query will do what you've asked in the OP:


I've made some assumptions about datatypes and form/combo box names here but hopefully this will give you the idea:

INSERT INTO T_Record ( Task, MSL_Name, Date_Assign, Complete, Date_Deadline )
SELECT T_Tasks.Task, [Forms]![MyForm]![cmbEmployees] AS EmployeeID, Now() AS AssignDate, False AS IsComplete, Date()+30 AS DueDate
FROM T_Tasks
WHERE T_Tasks.Unit_ID=[Forms]![MyForm]![cmbUnits];

This will select all the tasks that belong to a unit and add a record in T_Record for each task, assigned to the specified employee.
Save this as a query then just have a button on your form call the query.

Take a look at this study material: https://www.w3schools.com/sql/sql_in...nto_select.asp

[kalspiros]

kalspiros,

This may not be an issue with understanding vba. It may be more related to database design. I say may because I don't know the details of your set up.
Could you tell us in simple, plain terms --no database tables or forms yet -- what is involved in employee training? We often hear of courses, classes or subjects when related to training, so Task is a little new (to me anyway). An example of how an Employee is selected and "assigned to/registered to" specific training would help readers especially if you could include unit and task.

A more general comment when working with Access is to NOT have embedded spaces in field or object names. Invariably you will end up with syntax issues.

Good luck with your project.

Hi Orange,

Thanks for your message, absolutely, i'll try to outline the basics of the structure.

When employees (land surveyors to be specific) have their training review meeting, our aim is to initially assess their current skills. However, as surveying industry can be quite broad with several different disciplines, we are assessing surveyors' skills related to their Department i.e. whether they are working in mega-structures, tunnels, rivers etc. etc. Now, a tunnel surveyor, will not be assessed against his/her river surveys skills. So we are bringing forward the "tunnel surveyor" Unit and skipping any other Units (rivers, mega-structures etc.). Tunnel Surveyor unit contains several tasks (i.e. entering the tunnel, establishing underground points, placing prisms etc.) that need to be assessed. Each unit has different tasks (some have up to 45 tasks). So, when we set for the meeting, i'm envisaging selecting surveyors and the unit we will be assessing their skills and that would automatically have relevant tasks' records created for them, where further details from the assessor would be added per task.

i do value that someone might think that it'd be so much easier to tackle this with a simple spreadsheet where i'll have all surveyors listed in rows and all tasks in columns but this db contains several interlinked data and we prefer to keep them in one file (add a twist of GDPR and here we are!)

I hope that makes sense

Many thanks for your time

[kalspiros]

An insert into select query will do what you want. I'm making no comment on whether or not your db design is correct, or at least the best way to go about it, but this SQL query will do what you've asked in the OP:


I've made some assumptions about datatypes and form/combo box names here but hopefully this will give you the idea:

INSERT INTO T_Record ( Task, MSL_Name, Date_Assign, Complete, Date_Deadline )
SELECT T_Tasks.Task, [Forms]![MyForm]![cmbEmployees] AS EmployeeID, Now() AS AssignDate, False AS IsComplete, Date()+30 AS DueDate
FROM T_Tasks
WHERE T_Tasks.Unit_ID=[Forms]![MyForm]![cmbUnits];

This will select all the tasks that belong to a unit and add a record in T_Record for each task, assigned to the specified employee.
Save this as a query then just have a button on your form call the query.

Take a look at this study material: https://www.w3schools.com/sql/sql_in...nto_select.asp

THiS IS IT!!! Thanks a mil kd2017, that works great!
Agreed that the structure could be much better (you should have seen the previous version ) but i'm still kinda new to Access.
I'll be probably tweaking it the more i read!

Many thanks all!

[orange]

Here are some links that may be helpful.
This is a higher level model of departmental staff training from Barry Williams' site.
This is the "business description" that led to this model.

And this is my list of links etc to various articles on Database Planning and Design. Working through the tutorials from RogersAccessLibrary mentioned in the list is a great way to experience the design process of creating tables and relationships from a business description.

Good luck with your project.

[kalspiros]

Here are some links that may be helpful.
This is a higher level model of departmental staff training from Barry Williams' site.
This is the "business description" that led to this model.

And this is my list of links etc to various articles on Database Planning and Design. Working through the tutorials from RogersAccessLibrary mentioned in the list is a great way to experience the design process of creating tables and relationships from a business description.

Good luck with your project.

Many thanks orange. I do find it slightly challenging to understand model's full functionality but it does look neater and more structured. It's true that my T_record table might have a short expiry date because it might suffer from unneeded duplicates. As you are correctly pointing out in your list, structuring must start from a piece of paper.

I'll dive deeper to your resources!

Kind regards