Solved Duplicate record X amount of times (1 Viewer)

[oxicottin]

Hello, I have an attendance database and I want to be able to copy the current entry and its data for how ever many times specified in a text box named (txtRepeatForDays). I also have a date field named txtAbsenceDate that gives the date I selected. I just want to loop through however many days duplicating the info EXCEPT each new record I need the txtAbsenceDate to be the next days date.

Example:
txtRepeatForDays = 3
txtAbsenceDate = 7/15/20

record 1 = 7/15/20
record 2 = 7/16/20
record 3 = 7/17/20

 

[arnelgp]

just have a Button there to Do the actual appending of records:

private sub button_click()
dim i as integer, cnt as integer
cnt=CInt("0" & Me.txtRepeatForDays)
if cnt = 0 Then
    msgbox "you need to specify repeat days"
    me.txtRepeatForDays.SetFocus
    Exit sub
end if

if len(trim(me.txtAbsenseDate))=0 Then
    msgbox "need starting date of absent"
    me.txtAbseceDate.setfocus
    exit sub
end if

for i=0 to cnt-1
   currentdb.execute "insert into table (absenceDate) select #" & format(dateadd("d",i, me.txtAbsenceDate),"mm/dd/yyyy") & "#;"
next

end sub

 

[oxicottin]

I tried to run an append query and am having trouble with it. I am getting a 3075 error what am I missing in my strSql? The Debug.Print shows in the second image for the string and i

Private Sub Command14_Click()
Dim i As Integer
Dim counter As Integer
Dim dtmDate As Date
Dim strSql As String

dtmDate = Me.txtAbsenceDate
counter = Me.txtRepeatForDays - 1

For i = 1 To counter
    
    strSql = " INSERT INTO tbl_YearCalendar (AbsenceID,AbsenceDate) " _
    & " VALUES ([AbsenceID] " & Me.cboAbsenceCodeDesc & ",([AbsenceDate] & #" & dtmDate + i & "#);"
    
    Debug.Print strSql
    Debug.Print i
    
    CurrentDb.Execute strSql
    Next i
End Sub

 

[June7]

Is AbsenceID a number field?

Don't repeat fields in VALUES clause.

" VALUES (" & Me.cboAbsenceCodeDesc & ", #" & dtmDate + i & "#);"

 

[oxicottin]

June7, That worked as in getting rid of the errors and I didn't know that.... But for some reason its not entering the values captured in debug.print into my table (tbl_YearCalendar), they are correct. Is it because I have to have a value for every field in my table?

In the second image I added the remainder of fields and now it wont loop and I get an Error '3061' to few parameters

 

[June7]

Only need values for fields that are indicated as required in table design. Seems to me EmployeeID is rather important. Why would you need AbsenceReason and AbsenceID?

If field is a text type, use apostrophe delimiters, # for date/time fields.

 

[oxicottin]

June7, that worked great.... It now works. One thing though, if I didn't include all fields then I would get a to few parameters error but if I did include them like below then it works perfectly. The image is immediate print of it working, Thanks!

Private Sub Command14_Click()
Dim i As Integer
Dim counter As Integer
Dim dtmDate As Date
Dim strSql As String

dtmDate = Me.txtAbsenceDate
counter = Me.txtRepeatForDays - 1

For i = 1 To counter
    
    strSql = " INSERT INTO tbl_YearCalendar (AbsenceTime, AbsenceReason, EmployeeID, AbsenceID, AbsenceDate) " _
    & " VALUES (" & Me.txtAbsenceTime & ", '" & Me.txtAbsenceReason & "' ," & Me.txtEmployeeID & "," & Me.cboAbsenceCodeDesc & ", #" & dtmDate + i & "#);"
    
    Debug.Print strSql
    Debug.Print i
    
    CurrentDb.Execute strSql
    Next i
End Sub

 

[June7]

Very odd. I see no reason for that message if not all fields included. If fields are required, a record should simply not be added, no error message with Execute method.