Serious Form Problem

[expublish]

I have a big problem with part of my staff DB.

Basically, I need to have a form where you can select an employees name from a combo and details of all training courses that they have completed is displayed below and you can also select another training course to add to their reputoire (sp?). So, for each employee there will be multiple training courses they have completed.

How do i do this?

I have a table called Employees that has employee data (ie, name) and a table called Training that has fields for details about the training course.

Bearing in mind each training course details will be entered independantly of any employees, from a separate form.

So, to summarise ...

There is a table called Employees (holds all employee data) and a table called Training (holds all training course details).

I have a simple form that has all fields for the training course, so the courses we run can be viewed and edited here.

I now need a form where I select an employee from the combo and all courses they have done shows. You can then select a course to add to their records be selecting the course name from a combo. The other details of the course do not need to be on this form, literally just a list of course titles.

I was thinking sub form, but tried it and got in a mess.

Anyone got any ideas or any samples or this kinda thing?

Thanks in advance

Scott.

 

[Hayley Baxter]

Sub form is the correct way to go about this Scott. Create your sub form using the wizard and choose all the fields you want to display in your subform(this will be all your training details) then choose the master and child fields to link your main form to your subform ie my suppliers form is linked to my contracts subform via supplierID.

HTH
Hayley

 

[ColinEssex]

I did a similar thing for nurses training.

The way I did it was to have a 2nd page on the nurse detail form called courses. You need a subform on that page to show the list of courses they've done. Then added a combo box (unbound) selecting the course from the courses table. Then I had a command button below it with a 'save data' label. This (when clicked) runs an "Are you sure" msgBox then if ok it runs code (AddNew and Update) which adds the course selected, nurse ID_No and year obtained to the courses table. Then after that code put in a DoCmd.requery "SubformName"
and 'bingo' there it is on the subform.
Hope this helps

Colin

 

[expublish]

OK, I have setup the form with subform. A couple of questions ...

1) The tables employees and training are not related (they don't have a relationship becuase there is no common field). Do I need a link table to link them together?

2) I will be setting up another combo at the top of the form to select which employee to view. When you select an employee, the details inthe main form change but the details in the subform stay the same from the last person. I feel this has something to do with the Master/Child Links.
I currently have Link Child Fields as Employee ID (on main form) and Link Master Fields as the same. Is this right?

3) In the subform it is showing course details correctly, but can i have a combo under the course name field to select a course name and all the other fields fill in auto?

Scott

 

[Hayley Baxter]

I could be wrong here Scott Am I correct in saying that you want to show the employee details on your main form then for that employee a list of courses they have completed? if that is correct they are related and you will need to link them. The idea of a subfrm is to show related records. The employeeid would be the correct link. Give it a try and let us know how it goes.

Hayley

 

[expublish]

The main form only has 2 fields from my employees table: employee id and employee name.

Do I just stick another field in the training table that looks up say employee id and then make the relationship that way?

also are the master/child links correct?

 

[Hayley Baxter]

Scott I can send you a sample of my form that I have this working on if you like which email?

Hayley

 

[expublish]

That'd be great.

margaretkombakono@hdvfoundation.org.uk address would be best.

Will get back to you how I get on

 

[expublish]

I am having a go at what you said Hayley and it is nearly working. The relevant subform is showing for the correct employee and the correct course details. However, when I try and add a record to the subform a pop up message says 'File Cannot be updated'. Why is it doing this?

I am thinking of having another form where they enter a new course the employee has done and then this sub form is just to view courses they have done.