How to add multiple records to a table via a form?

[beginneraccess]

I am a beginner in Access as of last week, so pardon my inability to understand Access; currently I am on Access 2013. I am trying to add multiple records to a table. For example, I have multiple employees going to a training so instead of inputting them one by one, is there a way to input into all the employees that did go? I attempted in a form to make a list box, use multi select to select the employees that attended, and then add but it did not seem to work. So I have one table that is Employee and their ID # and another table that indicates which trainings employees have gone to/training name, the costs, the date, and receipt attachment. Later I am planning to make a query to get how many trainings each has attended and the sum of the amount spent on each Employee.

[twinnyfo]

beginneraccess

Welcome to Bytes!

You should also have an EmployeeTrainin g Table, which has the Training Course and the Employees who attended that training.

There many ways to add these employees to this table. It just depends on how elaborate you want to make it.

One way is to have a Form. On that form, you select the training course. Once you select the course, there would be a subform on that main form that lists all the attendees for that course, as well as a combo box that holds all the employees who have not attended that course. Select an employee from that combo box, click a command button to add the person to the course. Behind the button is VBA to add the person to the course and requery both the subform and the Combo Box. You could also use a List box as you have described to allow selecting multiple employees at once.

This is an outline of one way to do it, but not the answer itself. We can't do that for you. However, we are glad to work through details one step at a time and troubleshoot any sepcific problems you come across.

Hope this hepps!

[beginneraccess]

Dear twinnyfo,

thank you for your quick response. So far I have created a form and a subform which made it possible to select multiple employees and add to the subform. However, despite selecting multiple employees it seems to only add to one employee. May I ask if this is the first form error or the subform error?

[twinnyfo]

What is the VBA you are using to copy the records to the second table. That is our starting place.

[beginneraccess]

Sorry I did not do a VBA, so have been trying to make one right now...
I am not good with coding and did not know anything about VBA until you mentioned it, but this is what I got...
Option Compare Database
Option Explicit

Private Sub lstAll_AfterUpdate()
Dim SQL As String
    SQL = "SELECT tbStaff.[Employee ID], tbStaff.[First Name] & " " & tbStaff.[Last Name] AS EmployeeName FROM tbStaff ORDER BY tbStaff.[First Name]" & "FROM [tbStaff] INNER JOIN [tbStaff]" & "ON [trial for both].[Field Int ID]=[tbStaff].[Employee ID];"
 
    Me.delete1.Form.RecordSource = SQL
    Me.delete1.Form.Requery
 
End Sub

I'm guessing SQL = "SELECT tbStaff.[Employee ID], tbStaff.[First Name], tbStaff.[Last Name]" should be the beginning

Without the VBA, if I don't do multiselect on the list box it will let me update them one by one. But it seems like too much if more than 20 employees go to a training, plus trainings can also be a lot

[twinnyfo]

I’ll have to take a look at that tomorrow. Or, someone else may chime in....

[beginneraccess]

Thank you! I will also be trying to learn how to code slowly. Hopefully while learning I may be able to see what is wrong.

[twinnyfo]

We can go as slowly or as quickly as you are able to grasp. I can be very patient with those who eagerly seek to learn.

[beginneraccess]

Thank you for being very helpful!

[PhilOfWalton]

I think you have made a good start,and Twinny's posts certainly will put you on the right track, but setting up tables correctly and having meaningful field names is a great help.

You need 3 tables, 1 for Employees holding information solely about that person. Absolutely nothing to do with courses.

TblEmployees
    EmployeeID            AutoNumber    Primary Key
    EmpLastName                 Text
    EmpFirstName                Text
    EmpAddress1                 Text
    EmpAddress2                 Text
    EmpAddress3                 Text
    EmpTown                     Text
    EmpCounty                   Text
    EmpPostCode                 Text
    EmpEmail                    Text
    EmpPhone                    Text
    etc.
 

Note 2 points
1) There are no spaces in the field names (so you don't need to
use square brackets [] round the names
2) There should only be 1 word in each field. It is comparatively easy to combine fields together (EmpFirstName & EmpLastName to give EmpName. Splitting John Edward Smith into first & last names is much trickier.

Table of courses

TblCourses
    CourseID        AutoNumber    Primary Key
    CourseName            Text
    CourseCost            Currency
    CourseStartDate       Date
    etc

So now you have 2 independent tables. The Employees stand on their own, and could also be used for say grades achieved, and the Course table is there, regardless of whether or not there are any students attending.

Now the important table which puts the student on the course

TblJoinEmployeeCourse
    CourseID       Long Number   Joint Primary Key
    EmployeeID     Long Number   Joint Primary Key
    CourseDate     Date
    Fee            Currency
    DatePaid       Date
    Grade          Number?
    Comments       Text
    etc.

Set up relationships between the 3 tables and enforce referential integrity.

Now to your form.

On the header, you need a combo box to select on which course you are entering the Employees together with the course date.
The single select list box or Combo Box showing the Employees is fine (except I suspect nobody will know the EmployeeID Number, so just use the names sorted alphabetically.

There should be a continuous subform showing the employees selected. As each employee is selected from the list box, we add this to the subform, and remove that person from the list box. Equally, if you have a wrong employee on your subform, you delete it and add the person back to your list box.

This is only a (brief) outline. When you get stuck or have any questions, I'm sure Twinny or I will help you on your way.

As an aside, I am extremely ancient, and have difficulty in reading the small print on your form. For the sake of the ancient ones, can you use 12 point fonts. The only justification I can see for small fonts is where there are a hell of a lot of controls on your form, and space is tight.

Phil