Inserting dates into table does not work

[Jerry Maiapu]

I am trying to punch into a table a bunch of dates that fall between two given dates.

When compiled,no error but when on run does not do anything..(i.e nothing in the table)

Can someone figure out what's missing?

Option Compare Database
Option Explicit
Private Sub cmdinsert_Click()
 
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim counter, numdays, mynum As Integer
  Dim mydate As Date
 
  On Error GoTo ErrorHandler
 
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("Trialdate", dbOpenDynaset, dbAppendOnly)
 
numdays = DateDiff("d", Forms!frmCriteria!txtdate1, Forms!frmCriteria!txtdate2)
 'will store an integer rep. days difference
 
counter = 0
mynum = numdays + 1
 
 
Do While counter < mynum
 
mydate = DateAdd("d", counter + 1, Forms!frmCriteria!txtdate1)
 
   rs.AddNew ' go to a new record in our temp table rs
    rs!InsertedDates = mydate
    rs.Update
Loop
MsgBox "Already Done"
 
 
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
 
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub
 

NB: txtdate1 and txtdate2 line 23 & 15 are the date ranges entered in form frmCriteria
It should inserted all dates between these dates into table Trialdate

Thanks in advance.

Jerry

[ADezii]

@Jerry Maiapu
Here is some sample and compact code that will Insert Dates 'Between' a Start and End Date (non-inclusive) into a Field named [TrialDate] in a Table named Table1. For the sake of simplicity and brevity, the code performs no Validation, and assumes that all other Requirements have been met.

Const conSTART_DATE As Date = #8/2/2010#
Const conEND_DATE As Date = #8/12/2010#
Dim intCtr As Integer
Dim intDateDiff As Integer
 
intDateDiff = DateDiff("d", conSTART_DATE, conEND_DATE)
 
For intCtr = 1 To intDateDiff - 1
  CurrentDb.Execute "INSERT INTO Table1 ([TrialDate]) VALUES(#" & _
                     DateAdd("d", intCtr, conSTART_DATE) & "#);", dbFailOnError
Next

[Jerry Maiapu]

I figure it out myself. For..Next loop is doing a good job here.

 
Private Sub cmdinsert_Click()
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim counter, numdays, mynum, gotit As Integer
  Dim mydate, date1, date2 As Date
 
date1 = Me.txtdate1
date2 = Me.txtdate2
  On Error GoTo ErrorHandler
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("Trialdate", dbOpenDynaset, dbAppendOnly)
numdays = DateDiff("d", date1, date2) 'will store an integer rep. days diffirence
mynum = numdays
For counter = 1 To mynum
mydate = DateAdd("d", counter, date1)
   rs.AddNew ' go to a new record in our temp table rs
    rs!InsertedDates = mydate
    rs.Update
Next counter
MsgBox "Already Done"
 
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
 
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

NB: Results excludes date ranges..To Include simply replace 1 by 0 in code# 15

For interested members out there

Cheers

Jerry