Training Ttacking

[Sweetnuff38]

How would I go about (simply) making a list of classes auto populate when I add a new employee to a database for training? I have three tables, a student table with student id and a classes table with class id, and a transaction table with both to maintain relationship between the tables.

Thanks
Tracy

 

[georgedwilkinson]

It SOUNDS like you're keeping your "list" of classes in your transaction table. If that is the case, you can run something like this SQL:

insert into Transaction (StudentID, ClassID)
select StudentID, ClassID
from Student, Classes
Where
StudentID = "TheStudentIDYouJustEntered"
AND
ClassID in (list of classes you want to autopopulate with);

But then, nothing is ever really as simple as you want it to be, is it? (I really highly recommend you go with a non-simple, but correct, solution unless you want to run SQL every time somebody enters a new student).

 

[Sweetnuff38]

Ok and the non-simple solution?

 

[georgedwilkinson]

With the little bit of information I have: Create a bound form and on the After Insert event run something like the SQL I provided?

 

[Sweetnuff38]

How do I attach a file in here?

 

[georgedwilkinson]

Well, that helps a little.

In form f_Training_Requirements_Update:
First, create a hidden bound field to hold your Employee "ID" field. Name it something like "txtID"
Then in the "After Insert" event, run this SQL:

insert into t_Training_Association
([Class ID], [Employee ID])
select Me.txtID, ID
from t_Class_Name;

That will pre-populate your Association table with the employee's ID.

I'm really not familiar with macros but you should be able to do something like DoCmd.RunSQL with the above SQL as an argument.

HTH.

 

[Sweetnuff38]

Thank you very much

 

[Sweetnuff38]

Re: Training Tracking

Ok, tried this as suggested.

Private Sub Form_AfterInsert()
INSERT INTO t_Training_Association([Class ID], [Employee ID])select Me.txtID, ID from t_Class_Name;
End Sub

I am getting a compile error on t_Training_Association

Tracy

 

[georgedwilkinson]

Re: Training Tracking

Ok, tried this as suggested.

Private Sub Form_AfterInsert()
INSERT INTO t_Training_Association([Class ID], [Employee ID])select Me.txtID, ID from t_Class_Name;
End Sub

I am getting a compile error on t_Training_Association

Tracy

It would be more like (air code):

Private Sub Form_AfterInsert()
DoCmd.RunSQL "INSERT INTO t_Training_Association ([Class ID], [Employee ID]) select " & Me.txtID & ", ID from t_Class_Name;"
End Sub

Also, I don't see your link anymore and can't find the copy of your db I was looking at but the order of the arguments in the sub-query may be wrong.

Be cautious about your spaces, too. Too many spaces is generally OK (except when in a name) but not enough spaces will cause errors.

 

[Sweetnuff38]

Here is the database. It still doesnt seem to be working. Although I seem to be closer than I was..

Tracy

 

[georgedwilkinson]

What is it doing wrong? It looks like I got the order of columns wrong as I pointed out in my last post. Is there anything else wrong? Still gotta get it trusted and read/write to test it.

 

[georgedwilkinson]

Ok, I found several things. Try this:

Private Sub Form_AfterInsert()
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL "INSERT INTO t_Training_Association ([Employee ID],[Class ID]) select " & Me.txtID & ", [Class ID] from t_Class_Name;"
    DoCmd.SetWarnings (True)
End Sub

I ran it on a copy of your database and it seemingly does what you wanted done. I threw in the warning handling stuff assuming you'd want that after about the 3rd time entering an employee.

 

[Sweetnuff38]

Re: Training Tracking

Please find attached the messages I am getting.

Tracy

 

[Sweetnuff38]

Ok, so it asks for a class id now (parameter), and when I put it in, it does autopopulate the employee id in the association table, but only puts that one class primary key 18 times. What would I need to add, to have it put the employee id in as it is doing, but also fill in all 18 classes as well.

Appreciate your help.

 

[georgedwilkinson]

The information shown in the screen shots you sent is not using the code I told you to use in post 12. That code populates the junction table correctly.

If it asks for a parameter, see the above paragraph. The old code was not using the correct column names.

 

[Sweetnuff38]

The information shown in the screen shots was uploaded at the same time you posted the new code.

So it is posting the employee id 18 times, but I would also like the class list to autopopulate , 1 through 18.

I would also like to see this populate in the subform after adding a new employee. (it works now if the employee already exists)

Tracy

 

[Sweetnuff38]

anyone, help ... please lol