Creating a form based on a querry

[lpearce227]

Hello,

I am working on a many to many database for tracking staff training hours. I believe I have the tables set up correctly, but now I am having trouble with the forms. I want to create two separate forms. One is a basic form (that I can do) to initially enter the basic staff info (name, start date, end date, initial licensures) Then I need a form to enter the trainings. What I envision is a simple form for the training title, trainor, and ceu hours with a nested form for entering which staff members attended and the date of the training.

The issue I am having is that the people entering the data need to be able to enter the staff members by name rather than the staff ID (which is my primary key that is used in the staff2training table.

So how do I set the form to have the drop down of staff names for entry that will then populate the appropriate staff ID so that the link is made.

Laura

[twinnyfo]

Hi Laura,

This is relatively simple.

If your Training Table is properly structured, all you need is a field for the Staff Member (plus any additional fields relative to the training). This field will just need to be the same data type as the Staff ID (which is probably a long integer or an AutoNumber?) Either way, on your subform for the training, create a combo box that has two columns. The first column can be hidden and that will hold the Staff ID. The second column is the Staff member name.

Your Row Source will be something like this:

SELECT StaffID, StaffMemberName 
FROM tblStaffMembers;

Make sure that the bound column of the combo box is the staff ID. This combo box should be bound to your training table, particularly to the StaffID Field.

Hope this makes sense and hope it hepps!

Let us know if you are confused.