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

**twinnyfo** · Feb 5 '19, 06:51 PM

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** · Feb 5 '19, 07:59 PM

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?
[imgnothumb]https://bytes.com/attachments/attachment/9866d1549396351/screenshoot.png[/imgnothumb]

Attached Files

screenshoot.PNG (16.1 KB, 2787 views)

**twinnyfo** · Feb 5 '19, 08:01 PM

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

**beginneraccess** · Feb 5 '19, 09:45 PM

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

Code:

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** · Feb 5 '19, 10:21 PM

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

**beginneraccess** · Feb 5 '19, 10:23 PM

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** · Feb 5 '19, 10:25 PM

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** · Feb 5 '19, 10:27 PM

Thank you for being very helpful!

**PhilOfWalton** · Feb 6 '19, 11:48 AM

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.

Code:

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

Code:

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

Code:

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** · Feb 6 '19, 12:51 PM

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 TblJoinEmployee Course 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 frmAddEmployeeT raining
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 fsubEmployeeCou rses
RecordSource is TblJoinEmployee Course
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:

Code:

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 TblJoinEmployee Course. 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:

Code:

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** · Feb 6 '19, 01:56 PM

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

Code:

    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 frmAddEmployeeT raining form)

So add

Code:

!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** · Feb 6 '19, 02:01 PM

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

**beginneraccess** · Feb 6 '19, 05:37 PM

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 TblJoinEmployee Course? 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

Code:

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** · Feb 6 '19, 06:00 PM

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

Which control source?

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