424,294 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

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

P: 15
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.
2 Weeks Ago #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1.     strRowSource = _
  2.         "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
  3.         "FROM TblEmployees " & _
  4.         "LEFT JOIN TblJoinEmployeeCourse " & _
  5.         "ON TblEmployees.EmployeeID = TblJoinEmployeeCourse.EmployeeID " & _
  6.         "WHERE TblJoinEmployeeCourse.EmployeeID Is Null "
  7.         "ORDER BY EmpLastName, EmpFirstName;"
On the second block of code, we need to add the date (from the main frmAddEmployeeTraining form)

So add
Expand|Select|Wrap|Line Numbers
  1. !CourseDate = Me!CourseDate
BeginnerAccess also needs to do a check that Course Date has been entered before the Combo Box can be used.

Phil

Share this Question
Share on Google+
22 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,894
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!
2 Weeks Ago #2

P: 15
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?

Attached Images
File Type: png screenshoot.PNG (16.1 KB, 234 views)
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 2,894
What is the VBA you are using to copy the records to the second table. That is our starting place.
2 Weeks Ago #4

P: 15
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstAll_AfterUpdate()
  2. Dim SQL As String
  3.     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];"
  4.  
  5.     Me.delete1.Form.RecordSource = SQL
  6.     Me.delete1.Form.Requery
  7.  
  8. 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
2 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 2,894
Iíll have to take a look at that tomorrow. Or, someone else may chime in....
2 Weeks Ago #6

P: 15
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.
2 Weeks Ago #7

twinnyfo
Expert Mod 2.5K+
P: 2,894
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.
2 Weeks Ago #8

P: 15
Thank you for being very helpful!
2 Weeks Ago #9

PhilOfWalton
Expert 100+
P: 1,427
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.

Expand|Select|Wrap|Line Numbers
  1. TblEmployees
  2.     EmployeeID            AutoNumber    Primary Key
  3.     EmpLastName                 Text
  4.     EmpFirstName                Text
  5.     EmpAddress1                 Text
  6.     EmpAddress2                 Text
  7.     EmpAddress3                 Text
  8.     EmpTown                     Text
  9.     EmpCounty                   Text
  10.     EmpPostCode                 Text
  11.     EmpEmail                    Text
  12.     EmpPhone                    Text
  13.     etc.
  14.  
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
Expand|Select|Wrap|Line Numbers
  1. TblCourses
  2.     CourseID        AutoNumber    Primary Key
  3.     CourseName            Text
  4.     CourseCost            Currency
  5.     CourseStartDate       Date
  6.     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
Expand|Select|Wrap|Line Numbers
  1. TblJoinEmployeeCourse
  2.     CourseID       Long Number   Joint Primary Key
  3.     EmployeeID     Long Number   Joint Primary Key
  4.     CourseDate     Date
  5.     Fee            Currency
  6.     DatePaid       Date
  7.     Grade          Number?
  8.     Comments       Text
  9.     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
2 Weeks Ago #10

twinnyfo
Expert Mod 2.5K+
P: 2,894
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cboCourse_AfterUpdate()
  5.     Dim strRowSource    As String
  6.  
  7.     With Me.fsubEmployeeCourses.Form
  8.         .Filter = "CourseID = " & Me.cboCourse
  9.         .FilterOn = True
  10.     End With
  11.  
  12.     strRowSource = _
  13.         "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
  14.         "FROM TblEmployees " & _
  15.         "LEFT JOIN TblJoinEmployeeCourse " & _
  16.         "ON TblEmployees.EmployeeID = TblJoinEmployeeCourse.EmployeeID " & _
  17.         "WHERE TblJoinEmployeeCourse.EmployeeID Is Null;"
  18.     With Me.cboEmployee
  19.         .RowSource = strRowSource
  20.         .Requery
  21.     End With
  22.  
  23. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboEmployee_AfterUpdate()
  2.     Dim db      As DAO.Database
  3.     Dim rst     As DAO.Recordset
  4.  
  5.     Set db = CurrentDb()
  6.     Set rst = db.OpenRecordset("TblJoinEmployeeCourse")
  7.     With rst
  8.         Call .AddNew
  9.         !CourseID = Me.cboCourse
  10.         !EmployeeID = Me.cboEmployee
  11.         Call .Update
  12.         Call .Close
  13.     End With
  14.     Call db.Close
  15.     Set rst = Nothing
  16.     Set db = Nothing
  17.  
  18.     Me.fsubEmployeeCourses.Form.Requery
  19.     Me.cboEmployee.Requery
  20.  
  21. 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.
2 Weeks Ago #11

PhilOfWalton
Expert 100+
P: 1,427
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
Expand|Select|Wrap|Line Numbers
  1.     strRowSource = _
  2.         "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
  3.         "FROM TblEmployees " & _
  4.         "LEFT JOIN TblJoinEmployeeCourse " & _
  5.         "ON TblEmployees.EmployeeID = TblJoinEmployeeCourse.EmployeeID " & _
  6.         "WHERE TblJoinEmployeeCourse.EmployeeID Is Null "
  7.         "ORDER BY EmpLastName, EmpFirstName;"
On the second block of code, we need to add the date (from the main frmAddEmployeeTraining form)

So add
Expand|Select|Wrap|Line Numbers
  1. !CourseDate = Me!CourseDate
BeginnerAccess also needs to do a check that Course Date has been entered before the Combo Box can be used.

Phil
2 Weeks Ago #12

twinnyfo
Expert Mod 2.5K+
P: 2,894
Great catches, Phil! I'm just flailing blindly as usual!
2 Weeks Ago #13

P: 15
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
Expand|Select|Wrap|Line Numbers
  1. 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.
2 Weeks Ago #14

twinnyfo
Expert Mod 2.5K+
P: 2,894
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.
2 Weeks Ago #15

P: 15
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...
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub cboCourse_AfterUpdate()
  4.     Dim strRowSource    As String
  5.  
  6.     With Me.fsubEmployeeCourses.Form
  7.         .Filter = "CourseID = " & Me.cboCourse
  8.         .FilterOn = True
  9.     End With
  10.  
  11.    strRowSource = _
  12.         "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
  13.         "FROM [table].[tbEmployees] " & _
  14.         "LEFT JOIN [table].[TblJoinEmployeeCourse] " & _
  15.         "ON [TblEmployees].[EmployeeID] =[TblEmployees].[EmployeeID]  " & _
  16.         "WHERE [TblEmployees].[EmployeeID] Is Null " & _
  17.         "ORDER BY EmpLastName, EmpFirstName;"
  18.     With Me.cboEmployee
  19.         .RowSource = strRowSource
  20.         .Requery
  21.     End With
  22.  
1 Week Ago #16

PhilOfWalton
Expert 100+
P: 1,427
Try removing the word "[table]. on lines 13 & 14. There is no such thing a "Table" in SQL

Phil
1 Week Ago #17

P: 15
O whoops Thank you.

It still appears as "JOIN expression not supported"
1 Week Ago #18

PhilOfWalton
Expert 100+
P: 1,427
OK I've just spotted another error on line 15.

I think this should be
Expand|Select|Wrap|Line Numbers
  1. 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
1 Week Ago #19

P: 15
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cboCourse_AfterUpdate()
  5.     Dim strRowSource    As String
  6.  
  7.     With Me.fsubEmployeeCourses.Form
  8.         .Filter = "CourseID = " & Me.cboCourse
  9.         .FilterOn = True
  10.     End With
  11.  
  12.    strRowSource = _
  13.         "SELECT EmployeeID, EmpLastName, EmpFirstName " & _
  14.         "FROM [TblEmployees] " & _
  15.         "INNER JOIN [TblJoinEmployeeCourse] " & _
  16.         "ON [TblEmployees].[EmployeeID] = [TblJoinEmployeeCourse].[EmployeeID]" & _
  17.         "WHERE [TblEmployees].[EmployeeID] Is Null " & _
  18.         "ORDER BY EmpLastName;"
  19.     With Me.cboEmployee
  20.         .RowSource = strRowSource
  21.         .Requery
  22.     End With
  23.  
  24. End Sub
  25.  
  26. Private Sub cboEmployee_AfterUpdate()
  27.  
  28.     Dim db      As DAO.Database
  29.     Dim rst     As DAO.Recordset
  30.  
  31.     Set db = CurrentDb()
  32.     Set rst = db.OpenRecordset("TblJoinEmployeeCourse")
  33.     With rst
  34.         Call .AddNew
  35.         !CourseID = Me.cboCourse
  36.         !EmployeeID = Me.cboEmployee
  37.         !CourseDate = Me.CourseDate
  38.         !CourseCost = Me.cost
  39.  
  40.         Call .Update
  41.         Call .Close
  42.     End With
  43.     Call db.Close
  44.     Set rst = Nothing
  45.     Set db = Nothing
  46.  
  47.     Me.fsubEmployeeCourses.Form.Requery
  48.     Me.cboEmployee.Requery
  49.  
  50. End Sub
  51.  
1 Week Ago #20

twinnyfo
Expert Mod 2.5K+
P: 2,894
Line 13: SELECT tblEmployees.EmployeeID ...
Line 15: This must be a LEFT JOIN ó You had it correct previously.
1 Week Ago #21

P: 15
Nevermind it WORKS! Thank you all! Sorry of all the questions
1 Week Ago #22

twinnyfo
Expert Mod 2.5K+
P: 2,894
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!
6 Days Ago #23

Post your reply

Sign in to post your reply or Sign up for a free account.