Full Version: Code to insert multiple records
UtterAccess Forums > Microsoft® Access > Access Forms
DaisySara
Hello,
Presently I have an overtime form where a user can enter a start and end date, and employee name, then hit a command button and 1 record per day for this employee will be inserted into a table. What I would like it to do is have the user enter the start and end date, and choose a "discipline", then have 1 record for each employee in this discipline entered for each date.
Here is my present code (works) and I don't even know where to begin to modifiy it:
Dim intNumDay As Integer
Dim i As Integer
Dim dateCurrDate As Date
Dim strSQL As String
intNumDay = DateDiff("d", Me![StartDate], Me![EndDate])
'MsgBox intNumDay
For i = 1 To intNumDay
dateCurrDate = DateAdd("d", i, Me![StartDate])

If ((Not (Format([dateCurrDate], "dddd")) = "Saturday" And Not (Format([dateCurrDate], "dddd")) = "Sunday")) Then

strSQL = "INSERT INTO Overtime(OTDate, Discipline, Employee, Reason, Type) "
strSQL = strSQL & "VALUES(#" & dateCurrDate & "#,'" & cboDiscipline & "', '" & cboEmployee & "', 'Administrative Granted', '002 Overtime Paid');"


CurrentDb.Execute strSQL

End If

Next i
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "OvertimeBulk" (opens new form and shows these new records, for modification)
Thanks.
dashiellx2000
What I would do is create a query in VBA to pull all the people in each disipline. Then open this up as a recordset in code. Loop through the record set and add each person.
TH.
DaisySara
Hey William, thanks for your reply.
Can you be more specific as to what the code would look like? I've never opened a recordset in code before.
Jack Cowley
I am NOT trying to steal Willams thunder, but this UNTESTED AIR CODE may help to get you started I have not added all your code so you will need to add what I have left out...
CODE
Dim rs as New ADODB.Recordset
Dim strSQl As String
strSQL = "SELECT * FROM Overtime WHERE Overtime.Disipline = " & [Forms]![FormName]![DisiplineControlName] & ";"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF
rs.AddNew
rs!OTDate = Me.dateCurrDate
rs!Discipline = Me.cboDiscipline
...etc...
rs.Update
rs.MoveNext
Loop
...your other code...
rs.Close
set rs = Nothing

Apologies to William, who may have a different approach and I am sure you would like to see it.
Jack
dashiellx2000
I do have another approch which is similar to Jack's but a little different.
I have attached a small demo db to demostrate it. I made some changes to your code to make it work the way I have the data appended to the table.
Jack, please take a look as well and tell me what you think.
Jack Cowley
William -
Why not just 'read' the SQL directly instead of making a querydef? Also, do you need to use the subquery to get the EmployeeID as the data is already there and you only need to loop through the recordset?
My feeling is if the code works then it is good code! I did not take the time to test mine so there may be a major flaw or two!!!
Jack
DaisySara
Thank you both for your help. I will play with both and see if I can make sense of it.
illiam with your code I am getting an error, user-defined type not defined...for Dim db As DAO.database???
dashiellx2000
Sara:

The error you are getting maybe the result of a missing reference. Open up the VBA editor and goto Tools--->References. Uncheck any that are listed as Missing.

Jack:
You are correct that it probably would be "better" to read the SQL directly then to create a QueryDef. Creating and Deleting QDs can cause db bloat and that should be avoided. (But since we all compact and repair all the time this shouldn't be an issue) However, I was also assuming that Employees, Disciplines and Overtime were different tables. Is there a way you can adapt my demo db to demonstrate how to do it?

Thanks.

Edited by: dashiellx2000 on Fri May 26 14:39:02 EDT 2006.
DaisySara
Hello...I did get the code to work...Only 2 things:
1. The first date in the range is not added...all records start on day 2.
2. I would like to open another form after adding the records, so the user can go in and edit amount of hours...The form opens but it is blank.
This is my code:
Private Sub cmdAddRecords_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim intNumDay As Integer
Dim i As Integer
Dim dateCurrDate As Date
Dim intDOW As Integer
Dim EmpName As String
Dim strSQL As String
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete "qryOvertimeCriteria"
On Error GoTo 0
Set qd = db.CreateQueryDef("qryOvertimeCriteria", "SELECT * FROM Employees WHERE [Discipline] = '" & Me.cboDiscipline & "'")
Set rst = db.OpenRecordset("qryOvertimeCriteria")
Do Until rst.EOF
EmpName = rst.Fields("Last")
intNumDay = DateDiff("d", Me![txtStartDate], Me![txtEndDate])
dateCurrDate = Me![txtStartDate]
Do Until dateCurrDate = Me![txtEndDate]
dateCurrDate = DateAdd("d", 1, dateCurrDate)
intDOW = Weekday(dateCurrDate, vbSaturday)
If intDOW < 3 Then
dateCurrDate = dateCurrDate + (3 - intDOW)
End If
db.Execute "INSERT INTO Overtime(Employee, OTDate) SELECT Last, #" & dateCurrDate & "# AS [OTDate] FROM qryOvertimeCriteria WHERE [Last]= '" & EmpName & "'"
Loop
rst.MoveNext
Loop
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "OvertimeBulk"
End Sub
Jack Cowley
William -
would assume that there is a table of Disciplines that is linked to an Employee table so you could create a query where you join those two table and then use an SQL statement, similar to the one I posted, but use that table to get the employeeID based on the selected discipline. Or you can do it all in one fell swoop in an SQL statement and not use a saved querydef.
HAs for your demo - you only need to use the SQL and ADO to open a recordset and that would preceed your code, but I would use code like I suggested rather then the INSERT INTO approach, though both work well.
Does that help to answer your question?
Jack
Jack Cowley
William -
I am not sure if this is what you had in mind, but I threw it together to give you an outline of what I had in mind... All code is purely fiticious, but is close...
Dim rs as New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim intNumDay As Integer
Dim dateCurrDate As Date
Dim intDOW As Integer
Dim EmpName As String
Dim strSQL As String
strSQL = "SELECT * FROM Employees WHERE [Discipline] = " & Me.cboDiscipline & ";"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs1.Open "tblOvertime", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
Do Until rst.EOF
intNumDay = DateDiff("d", Me![txtStartDate], Me![txtEndDate])
dateCurrDate = Me![txtStartDate]
Do Until dateCurrDate = Me![txtEndDate]
dateCurrDate = DateAdd("d", 1, dateCurrDate)
intDOW = Weekday(dateCurrDate, vbSaturday)
If intDOW < 3 Then
dateCurrDate = dateCurrDate + (3 - intDOW)
End If
rs1.AddNew
rs1!EmployeeID = rs.EmployeeID
rs1!OTDate = dateCurrentDate
rs1.Update
Loop
rs.MoveNext
Loop
rs.Close
set rs = Nothing
Jack
Jack Cowley
William appears to be out of the building so I will see if I can help. To find out why you are missing the first date I would suggest you put a Stop in your code before the Do Until rst.EOF. When you code editor opens step through your code and see what values are in dateCurrDate and this will tell you, as the code loops, where the first date is getting skipped.
For you second question - if you open the table the form "OvertimeBulk" is based does it have the data you expect? If you close your form with the code then open OvertimeBulk does the data show in the form? Try leaving out the Me.Dirty code and see if that makes a difference as I don't think you need it.
hth,
Jack
dashiellx2000
Daisy, You need to move the dateCurrDate = DateAdd("d", 1, dateCurrDate) line to just after the db.execute line. What the code you have is doing is adding a day to the dateCurrDate before evaluating if it is a weekend and adding it as an approved overtime day.
ack, Thanks for the outline. I wasn't questioning your method, I just wanted to see it to improve my own understanding. Access is one of those things where you can accomplish the same thing many different ways. I just want to know as many of them as I can.
I hope both of you have a great holiday weekend.
Jack Cowley
William -
Not for a moment did I think you were questioning my approach! As you say, there are many ways to skin the ol' Access cat!!!
You have a good weekend as well!! thumbup.gif
Jack
DaisySara
OK, I have made the changes you guys suggested(moving the dateCurrDate line and deleting the dirty line), and this is what I get:
. It is now adding the first day of the date range, 1 record, date and discipline alone(no employee name), whether it is a weekday or not.
2. The OvertimeBulk form is still opening empty ( I removed the dirty statement). This form is based on the following query:
SELECT Overtime.Employee, Overtime.OTDate, Overtime.Discipline, Overtime.TotalOTHours, Overtime.Reason, Overtime.Type, Overtime.Comments
FROM Overtime
WHERE (((Overtime.OTDate) Between [Forms]![Overtime - Insert Range]![txtStartDate] And [Forms]![Overtime - Insert Range]![txtEndDate]) AND ((Overtime.Discipline)=[Forms]![Overtime - Insert Range]![cboDiscipline]))
Order made the changes you guys suggested(moving the dateCurrDate line and deleting the dirty line), and this is what I get:
. It is now adding the first day of the date range, 1 record, date and discipline alone(no employee name), whether it is a weekday or not.
2. The OvertimeBulk form is still opening empty ( I removed the dirty statement). This form is based on the following query:
SELECT Overtime.Employee, Overtime.OTDate, Overtime.Discipline, Overtime.TotalOTHours, Overtime.Reason, Overtime.Type, Overtime.Comments
FROM Overtime
WHERE (((Overtime.OTDate) Between [Forms]![Overtime - Insert Range]![txtStartDate] And [Forms]![Overtime - Insert Range]![txtEndDate]) AND ((Overtime.Discipline)=[Forms]![Overtime - Insert Range]![cboDiscipline]))
ORDER BY Overtime.Employee, Overtime.OTDate;
The records are not opening in the form, however they are being added to the table. If I open the form on it's own, after closing the form with the code, and will prompt me for the dates and discipline and it does show the records.
BTW...thanks a lot for all your help and hope you had a good holiday.
Jack Cowley
DaisySara -
Could it be possible for you to post a demo of what you are doing? Sometimes it is a lot easier if we can actually see what is going on and try to solve the problem that way. Be sure there is no confidential data in the db and that you tell us what form to look at, etc. Be sure and compact and repair then zip the file and post it here. The max size that you can post here is 500k. If that presents a problem please let us know.
Jack
DaisySara
Thanks. Here is my database. The first form "Overtime - Insert Range" is where you will enter start and end dates, and the discipline, hit the button and it will add 1 record per person per day into the "Overtime" table. It should then open up the form "OvertimeBulk" to display all of these new records, so the user can edit the amount of hours. This form is based on the query "Overtime for Bulk Form".
Jack Cowley
DaisySara -
I have your db, but unfortunately I have to leave right now. I am not sure what time this afternoon I will be back, but as soon as I am back I will look into your db and see if I can sort it out. Hopefully someone else will see that you need help and can give you a hand. You might want to PM William and see if he can assist, as I am sure he is willing to help you if he knows you have posted your db. If you do not PM him he probably does not know that you have not gotten your code working yet so is unaware that you need some help.
Sorry, but I must dash and I hope you find a solution before I return!!!
Jack
DaisySara
OK, I have been thinking about this...maybe this is the problem. My query used to open the second form, OvertimeBulk, tells it to take the start and end dates entered on the first form, Overtime - Insert Range. However, this start date could be a weekend, the code hasn't checked that yet. Should the query instead be telling it to take the dateCurrDate, or something like that?
DaisySara
OK...If anybody is actually reading this and is curious about the solution...Well my query used to make the second form which displayed all the new records was taking the start and end dates, and the discipline from the first form. Well my code in the first form wasn't inserting a discipline, just the dates...so obviously when the query ran there were no records with that discipline because I wasn't inserting it. So I fixed that and it seems fine...ONly thing is it always adds 1 blank record for the first day in the range...At this point, I can live with it!
Jack Cowley
DaisySara -
I'm sorry that I did not get back to you but I was gone all day yesterday. It sounds like you have fixed your problem but now have the extra record. Is the extra record at the end or the beginning? If it is at the end your loop your loop may be one more than you need. In other words, the loop may look like this:
For i = 1 to RecordSet.Count
and it may need to look like:
For i = 1 to RecordSet.Count - 1
I am JUST guessing, but you may want to look at that possibility.
Jack
DaisySara
No problem Jack, I've been working this out. The extra record is at the beginning. It only contains the first day in the date range and the discipline. I will check out my loop.
I have 1 more part I would like to incorporate into this code. The code takes the dates and discipline entered on the form to insert new records into the overtime table. The overtime table consists of the following fields: ID, Date, Employee, hours, Type, Reason. I would like to insert values for 'type' and 'reason' into these new records. Since we are inserting records based on our querydef we opened, and these 2 fields are not in the querydef, I do not know where to go from here.
Jack Cowley
DaisySara -
If Type and Reason are items that will be the same for all records you could have them as combo boxes on your form and the user could select the Type and Reason and then you could refer to the controls on the form in your code. To tell you the truth I have a bit of trouble following the INSERT INTO code that William created because of the subquery. I would do things slightly differently so if you have a problem inserting the Type and Reason using combo boxes then let me know and post your current working code and I will see if I can get it to work.
I will tell you that I am working on another db so you may not get instantaneous responses from me...
Jack
DaisySara
Yes the querydef is confusing me too...I have never used that before.
But even if I put additional combo boxes on the form to let them pick type and reason, the INSERT INTO statement still selects from the query def(which queries the employee table), and these two fields are not in the query...
et qd = db.CreateQueryDef("qryOvertimeCriteria", "SELECT * FROM Employees WHERE [Discipline] = '" & Me.cboDiscipline & "'")
db.Execute "INSERT INTO Overtime(Employee, OTDate, Discipline) SELECT Name, #" & dateCurrDate & "# AS [OTDate], Discipline FROM qryOvertimeCriteria WHERE [Name]= '" & EmpName & "'"
My entire working code is:
Private Sub cmdAddRecords_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim intNumDay As Integer
Dim i As Integer
Dim dateCurrDate As Date
Dim intDOW As Integer
Dim EmpName As String
Dim strSQL As String
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete "qryOvertimeCriteria"
On Error GoTo 0
Set qd = db.CreateQueryDef("qryOvertimeCriteria", "SELECT * FROM Employees WHERE [Discipline] = '" & Me.cboDiscipline & "'")
Set rst = db.OpenRecordset("qryOvertimeCriteria")
Do Until rst.EOF
EmpName = rst.Fields("Name")
intNumDay = DateDiff("d", Me![txtStartDate], Me![txtEndDate])
dateCurrDate = Me![txtStartDate]
Do Until dateCurrDate = Me![txtEndDate]
intDOW = Weekday(dateCurrDate, vbSaturday)
If intDOW < 3 Then
dateCurrDate = dateCurrDate + (3 - intDOW)
End If
db.Execute "INSERT INTO Overtime(Employee, OTDate, Discipline) SELECT Name, #" & dateCurrDate & "# AS [OTDate], Discipline FROM qryOvertimeCriteria WHERE [Name]= '" & EmpName & "'"
dateCurrDate = DateAdd("d", 1, dateCurrDate)
Loop

If Me.Dirty Then Me.Dirty = False

rst.MoveNext
Loop
DoCmd.OpenForm "OvertimeBulk"
End Sub
I am not in a hurry with this project, and all your help is greatly appreciated. In the mean time I think I will try to tackle the approach you initially suggested (without the querydef).
Jack Cowley
DaisySara -
This code is close to working but I do not have time to work on it further. Hopefully this will get you closer to what you want... Add to combos to your form for Reason and Type and hopefully this will get you started...
CODE
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim intNumDay As Integer
Dim i As Integer
Dim dateCurrDate As Date
Dim intDOW As Integer
Dim EmpName As String
Dim strSQL As String
strSQL = "SELECT * FROM Employees WHERE [Discipline] = '" & Me.cboDiscipline & "'"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
rs1.Open "OverTime", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
intNumDay = DateDiff("d", Me![txtStartDate], Me![txtEndDate])
dateCurrDate = Me![txtStartDate]
    Do Until dateCurrDate = Me![txtEndDate]
        intDOW = Weekday(dateCurrDate, vbSaturday)
            If intDOW < 3 Then
                dateCurrDate = dateCurrDate + (3 - intDOW)
            End If
    rs1.AddNew
    rs1!Employee = rs!EmpName
    rs1!Discipline = Me.cboDiscipline
    rs1!OTDate = dateCurrentDate
    rs1!Reason = Me.cboReason
    rs1!Type = Me.cboType
    rs1.Update
dateCurrDate = DateAdd("d", 1, dateCurrDate)
rs.MoveNext
Loop
rs.MoveNext
Loop
DoCmd.OpenForm "OvertimeBulk"

Sorry, but I must dash...
Jack
DaisySara
Hey Jack,
Yes I did get it to work...thank you, thank you, thank you a million times for all your help...William you too. What would I do without this site?!?!
Alyssa
Jack Cowley
Alyssa -
EEHAAA!!! WELL DONE! Congratulations!!! Hooray for you and I am so pleased to hear that you have solved your problem!!! yayhandclap.gif
Thank you for being a member here and helping to make this site the wonderful site that it is!! notworthy.gif
Continued success with the project and you know where to come if you have questions!!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.