Input the Same Data into Multiple Records

Question

I'm new at access and was wondering if there is a way to enter the same data into multiple records.

We have a Employee Training worksheet that I thought might be better in access.

Sometimes 5 employees go to the same training, so I'd like to add a single training to the 5 employees with one entry.

Sometimes 50 employees to go the same training so you can understand my need.

Is it possible to select several employees and then enter the training information once and have it fill in for each employee?

Answer 1

Yes, but the REAL answer to your question goes to structure. If you have your strucutre correct this will work the way you want. 

What you have is a many to many relationship. One training course can apply to multiple employees and one employee can take multiple courses. So you need THREE tables to model this relationship.

tblCourse

CourseID (Primary Key Autonumber)

Coursename

tblEmployee

EmployeeID (PK Autonumber?)

Firstname

Lastname

etc.

tjxEmpCourse

EmpCourseID (PK Autonumber)

CourseID (Foreign Key)

EmployeeID (FK)

CourseDate

Now the way to enter the data is you have a main form bound to tblCourse On that main form have an UNBOUND control for CourseDate. Then add a continuous form subform on the main form bound to tjxEmpCourse and linked on CourseID. On the Subform are two visible controls (the control for CourseID will be hidden). One control is a textbox with a Default value of:

=Forms!mainformname!controlname

where mainformname is the name of the main form and controlname the name of the unbound control for CourseDate. The other control will be a combobox to select each employee. The Combobox will have the following relevant properties:

RowSource: SELECT EmployeeID, Lastname & ", " & Firstname AS Employee 

                     FROM tblEmployee

                     ORDER BY lastname, Firstname;

Bound column: 1

Column Count: 2

Column Widths: 0";2"

From there, you select the course in the main form. enter the date in the textbox, then select an employee in the combobox in the subform. The date should fill in, then go to the next row and select the next employee into all the employees have been selected.

Answer 2

For this sort of batch insert I'd normally use an unbound dialogue form rather than a bound subform of the type Scott describes as it enables you to select the subset of employees simultaneously from a list in one control rather than having to select each individually in a separate row in the subform.  Which approach you prefer to use is for you to decide of course.  

Firstly, as Scott said, you need to be sure the database structure is correct.  You have employees and what I'll call training events entity types so these should be modelled by tables, in broad outline, along these lines:

Employees

....EmployeeID  (PK)

....FirstName

....LastName

....etc

TrainingEvents

....TrainingEventID  (PK)

....TrainingEvent

....etc

Note that this table models events, not types of training.  If multiple events can be of the same training type then you would also need a TrainingTypes table or similar whose primary key is referenced by a foreign key column in TrainingEvents.

The assignment of employees to a training event is a many-to-many relationship type between Employees and TrainingEvents and is modelled by a table like this which resolves the relationship type into two one-to-many relationship types:

EmployeeTraining

....EmployeeID  (FK)

....TrainingEventID  (FK)

The primary key of this table is a composite one of the two foreign keys.  You can use a surrogate autonumber key if you wish, but if you do, you must include the two foreign key columns in a single unique index to define them as a candidate key; otherwise invalid duplication of data would be possible  The table might have other columns representing other attributes of an employee's participation in a training event, e.g. their successful or otherwise completion of the training.

To assign an employee to a single training event requires a row to be inserted into EmployeeTraining with the relevant EmployeeID from Employees and the relevant TrainingEventID value from  TrainingEvents.  To do this for multiple employees it can conveniently be done by means of an unbound dialogue form containing a multi-select list box of employees, and a combo box of training events.  The list box would be set up as follows:

Name:    lstEmployees

RowSource:     SELECT EmployeeID, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

MultiSelect:       Simple or Extended as preferred.

If your units of measurement are imperial rather than metric Access will automatically convert the ColumnWidths unit to inches.  The important thing is that the dimension is zero to hide the first column.  You don't need to specify a width for the second (visible) column.

The combo box would be set up as follows:

Name:    cboTraining

RowSource:     SELECT TrainingEventID, TrainingEvent FROM TrainingEvents ORDER BY TrainingEvent;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

You can now select any number of employees from the list box and a single training event in the combo box.

Add a command button to the form and put the following in its Click event procedure:

    Const MESSAGE_TEXT = "At least one employee and a training event must be selected"

    Dim varItem As Variant

    Dim strSQL As String

    Dim ctrl As Control

    Set ctrl = Me.lstEmployees

    ' ensure that at least one employee and a training event has been selected

    If ctrl.ItemsSelected.Count > 0 And Not IsNull(Me.cboTraining) Then

        ' loop through selected items in list box and build an SQL statement

        ' at each iteration of loop to insert a row into table

        For Each varItem In ctrl.ItemsSelected

            strSQL = "INSERT INTO EmployeeTraining(EmployeeID, TrainingEventID) " & _

                "VALUES(" & ctrl.ItemData(varItem) & "," & Me.cboTraining & ")"

            ' execute the SQL statement, ignoring error if employee

            ' is already assigned to the selected training event

            CurrentDb.Execute strSQL

        Next varItem

    Else

        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"

    End If

Answer 3

Thanks for your input.  I've had to put this on hold.  Work has shifted my priorities.  I'll keep your info and get back to it as soon as I can.

As always, you and everyone on this site is amazingly helpful!

Thanks.

Answer 4

Thank you Ken.  You're the 2nd person to provide such a thorough answer.  It is a great help!

Answer 5

Thank you!!!  I had not expected such a thorough reply!  It's a great help.