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 EmployeeTraining 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

[twinnyfo]

AS usual, Phil has you on the right track with your Tables and Relationships--I consider him Bytes's Relationship King....

There are several ways to attack adding names to your TblJoinEmployeeCourse Table (using Phil's table names jsut as an example).

If we look at what you do procedurally, you want to select a name from either a Combo Box or a List Box. Either one will work. If you want the names to be added immediately after selection, you may use either; if you want to select all names and then add them as a group, the best way would be to use a list box. If you have a small organization, I would recommend using a combo box. If you have a LARGE organization, I would build a subform with a list of employees, as this would enable you to search for names and validate identity. For now, we will go on the assumption that we are using a combo box and adding each name as you select them. If you want to use a list box and select all names and then add them en masse, let me know--that is a bit more involved, and I'd rather not "start" there, but understanding list boxes is something you wil leventually want to understand (personally I seldom use them, but that is based upon preference and needs, not pejoratively).

Assumptions:

• Form named frmAddEmployeeTraining
• This Form has a Combo Box named cboCourse

• cboCourse uses CourseID and CourseName for its values

• CourseID is the bound column (unshown)

• This Form has a Combo Box named cboEmployee

• cboEmployee uses EmployeeID, EmpLastName and EmpFirstName for its values

• EmployeeID is the bound column (unshown)

• This Form has a Sub-Form on it named fsubEmployeeCourses

• RecordSource is TblJoinEmployeeCourse

• Default filter for the Form is CourseID=0; FilterOnLoad property set to True

When you select a Course from cboCourse, you want to filter the subform. But, you also want to update the Rowsource of the Employee Combo Box, so that only the employees who have not been assigned to that course will populate the combo box:

Option Compare Database
Option Explicit
 
Private Sub cboCourse_AfterUpdate()
    Dim strRowSource    As String
 
    With Me.fsubEmployeeCourses.Form
        .Filter = "CourseID = " & Me.cboCourse
        .FilterOn = True
    End With
 
    strRowSource = _
        "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
        "FROM TblEmployees " & _
        "LEFT JOIN TblJoinEmployeeCourse " & _
        "ON TblEmployees.EmployeeID = TblJoinEmployeeCourse.EmployeeID " & _
        "WHERE TblJoinEmployeeCourse.EmployeeID Is Null;"
    With Me.cboEmployee
        .RowSource = strRowSource
        .Requery
    End With
 
End Sub

Now, all you need to do is add the employees to the training courses. Every time you select an amployee from the Combo Box, you want to add the name to TblJoinEmployeeCourse. But, in order to see the results, you need to requery the sub-form, and in order to remove the employee name from the Combo Box, you need to requery that Row Source:

Private Sub cboEmployee_AfterUpdate()
    Dim db      As DAO.Database
    Dim rst     As DAO.Recordset
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("TblJoinEmployeeCourse")
    With rst
        Call .AddNew
        !CourseID = Me.cboCourse
        !EmployeeID = Me.cboEmployee
        Call .Update
        Call .Close
    End With
    Call db.Close
    Set rst = Nothing
    Set db = Nothing
 
    Me.fsubEmployeeCourses.Form.Requery
    Me.cboEmployee.Requery
 
End Sub

I think this should get you in the right direction. No guarantees right now, since I don't have your tables, but this should work.

[PhilOfWalton]

Thank you for your kind words,Twinny. One of my DBs has 109 tables, so I have to be reasonably proficient at relationships.

One or two points.
I think that a multi select list box is a bad idea. You have to click on each employee to select them, so they might just as well do the update immediately. Having a command button to send them as a group is an additional key press and as you say, requires more complex VBA.

On the first block of code, the employees are not sorted. I think this should be

    strRowSource = _
        "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
        "FROM TblEmployees " & _
        "LEFT JOIN TblJoinEmployeeCourse " & _
        "ON TblEmployees.EmployeeID = TblJoinEmployeeCourse.EmployeeID " & _
        "WHERE TblJoinEmployeeCourse.EmployeeID Is Null "
        "ORDER BY EmpLastName, EmpFirstName;"

On the second block of code, we need to add the date (from the main frmAddEmployeeTraining form)

So add

!CourseDate = Me!CourseDate

BeginnerAccess also needs to do a check that Course Date has been entered before the Combo Box can be used.

Phil

[twinnyfo]

Great catches, Phil! I'm just flailing blindly as usual!

[beginneraccess]

Thank you guys so much for all the information! I am trying to enter the code as typed but ran into a few errors. May I ask if the control source is supposed to be EmployeeID from the tblEmployees or from the TblJoinEmployeeCourse? I toggled between both, but I don't think that was the error, but just to be safe. The error message that appears as "The specified field 'EmployeeID' could refer to more than one table listed in the FROM clause of your SQL statement" I try to add the

table.tblEmployees.EmployeeID

but that then submits another error code that says that there is a Syntax Error... reverting back to the original code it still says "Syntax Error in the FROM clause"
BUT I am being told that we will just use an old file... Thank you guys so much. I will probably have more questions again soon.

[twinnyfo]

beginneraccess:
May I ask if the control source is supposed to be EmployeeID from the tblEmployees or from the TblJoinEmployeeCourse?

Which control source?

The Main form itself may be unbound. The two combo boxes described in my "Assumptions" above are also unbound.

[beginneraccess]

Thanks Twinnyfo,
I fixed the error message stating that it was from the FROM clause by noticing that i needed to add [table].[tblemployees] now its stating "SYNTAX error in the Join Clause" and by adding the "[]" brackets. Another thing that seemed to be my fault is that the master and child field was in EmployeeID and without it, the database seems to be working better...

Option Explicit
 
Private Sub cboCourse_AfterUpdate()
    Dim strRowSource    As String
 
    With Me.fsubEmployeeCourses.Form
        .Filter = "CourseID = " & Me.cboCourse
        .FilterOn = True
    End With
 
   strRowSource = _
        "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
        "FROM [table].[tbEmployees] " & _
        "LEFT JOIN [table].[TblJoinEmployeeCourse] " & _
        "ON [TblEmployees].[EmployeeID] =[TblEmployees].[EmployeeID]  " & _
        "WHERE [TblEmployees].[EmployeeID] Is Null " & _
        "ORDER BY EmpLastName, EmpFirstName;"
    With Me.cboEmployee
        .RowSource = strRowSource
        .Requery
    End With
 

[PhilOfWalton]

Try removing the word "[table]. on lines 13 & 14. There is no such thing a "Table" in SQL

Phil

[beginneraccess]

O whoops Thank you.

It still appears as "JOIN expression not supported"

[PhilOfWalton]

OK I've just spotted another error on line 15.

I think this should be

ON [TblEmployees].[EmployeeID] = TblJoinEmployeeCourse.[EmployeeID]

I am going to be honest for once. I usually create and test a query using the query builder, then copy the SQL into the VBA making suitable adjustments by adding the inverted commas and underscores.

Phil

[beginneraccess]

Hi Phil
Thanks for the catch! So it keeps making errors... Even if I add all the [] and . and correct the code to what I see on SQL from queries... the error states that "The specified field 'EmployeeID' could not refer to more than one table listed in the FROM clause of your SQL statement." So I have tried to add table name in front of every variable but to no avail.

Option Compare Database
Option Explicit
 
Private Sub cboCourse_AfterUpdate()
    Dim strRowSource    As String
 
    With Me.fsubEmployeeCourses.Form
        .Filter = "CourseID = " & Me.cboCourse
        .FilterOn = True
    End With
 
   strRowSource = _
        "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
        "FROM [TblEmployees] " & _
        "INNER JOIN [TblJoinEmployeeCourse] " & _
        "ON [TblEmployees].[EmployeeID] = [TblJoinEmployeeCourse].[EmployeeID]" & _
        "WHERE [TblEmployees].[EmployeeID] Is Null " & _
        "ORDER BY EmpLastName;"
    With Me.cboEmployee
        .RowSource = strRowSource
        .Requery
    End With
 
End Sub
 
Private Sub cboEmployee_AfterUpdate()
 
    Dim db      As DAO.Database
    Dim rst     As DAO.Recordset
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("TblJoinEmployeeCourse")
    With rst
        Call .AddNew
        !CourseID = Me.cboCourse
        !EmployeeID = Me.cboEmployee
        !CourseDate = Me.CourseDate
        !CourseCost = Me.cost
 
        Call .Update
        Call .Close
    End With
    Call db.Close
    Set rst = Nothing
    Set db = Nothing
 
    Me.fsubEmployeeCourses.Form.Requery
    Me.cboEmployee.Requery
 
End Sub
 

[twinnyfo]

Line 13: SELECT tblEmployees.EmployeeID ...
Line 15: This must be a LEFT JOIN — You had it correct previously.

[beginneraccess]

Nevermind it WORKS! Thank you all! Sorry of all the questions

[twinnyfo]

Beginner,

Glad we could be of service! Phil did much of the heavy lifting, as I am still out of town (and will be for another week). As you can see, there are many experts here able to hepp!