UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert, Access 2016    
 
   
mike60smart
post Oct 22 2019, 09:20 AM
Post#1


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have the following structure:-

Attached File  er.PNG ( 20.51K )Number of downloads: 4


I have been asked if I can do the following:-

On a Form have a Combobox to select a Training Module and a Multi Select List box which displays all Employees from which a number would be selected.

When the user selects the Training Module and a Number of Employees is it possible using VBA to insert data into their respective tables??

All EmployeeID's into tblEmployeesPositions

TrainingID into tblPositionTraining

I have no idea how to do this but just asking if it is at all possible?

Any help appreciated


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Oct 22 2019, 09:53 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,389
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

Creating a loop through the listbox is easy, but I have to ask about the table structure..

What your asking is much based around a classic M:M..

tblEmp >> jtblable << tblTrainingMod

You seem set on populating two table within the looping process of the listbox..
thumbup.gif iconfused.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
mike60smart
post Oct 22 2019, 09:59 AM
Post#3


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

A bit of an odd request as far as I can see.

Currently when the op selects an Employee and then selects a Position on the After Update a list of Training required for that Position is added to the tblPositionTraining.

This works just as it should.

This additional process is in the event of a new Training Module required for the specified Position.

How can it be added for all Employees who are currently on this Position?

I can't see how.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Oct 22 2019, 10:24 AM
Post#4


UA Editor + Utterly Certified
Posts: 24,389
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


HI Mike
In your structure image..

Are we seeing all the tables involved..??

Am I not seeing a table of "Training Modules"..??

If I'm understanding your last message, and not always wanting to visualize the route from form to table..

You enter/add a new TM (training mod) to the tblTM, that needs to be "linked" to a position. Then you need to search out those employees that are in that position..

Then update the table...
thumbup.gif


--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
orange999
post Oct 22 2019, 10:27 AM
Post#5



Posts: 1,999
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Mike ,

Not sure if this is relevant to your specific set up, but generally this graphic helps put things into perspective.

Attached File(s)
Attached File  EmployeePositionTraining.jpg ( 40.91K )Number of downloads: 5
 

--------------------
Good luck with your project!
Go to the top of the page
 
mike60smart
post Oct 22 2019, 10:29 AM
Post#6


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

Sorry I missed the tblTraining that is linked to tbluPositions

Attached File  er.PNG ( 25.69K )Number of downloads: 1

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Oct 22 2019, 10:58 AM
Post#7


UA Editor + Utterly Certified
Posts: 24,389
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

That image from Orange999 does show that a junction table is required between tblPosition and tblTrainingMods

tblPosition >> jtblPositionTrainingMod << tblTrainingMod

If I'm not mistaken the input of a new training module which is assigned to a position in the junction table. Would remove the need to search out employees.

So when selecting an employee that is assigned to a position, those training module's will become available to view.. (assigned to positions..)
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
mike60smart
post Oct 22 2019, 11:02 AM
Post#8


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

OK I will modify the structure to include the Junction table and see where we get to.


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 11:59 AM