On Sat, 26 Jun 2004 02:18:24 GMT, Shyguy <sh****@aol.com> wrote:
I need to write consecutive dates to a table, starting with a date
input on a form. I haven't got a clue as to where to begin.
I have searcewd the web with no success. Could someone help at least
get me started?
Thanks for any help
This should get you going.
Put a textbox on the input form to store the start date and change DateInputTextboxName in the code to the name of the
textbox.
Add a command button to the form and in it's property sheet set OnClick to [Event Procedure] then click on the "..."
button at the right of the property to open the code module.
Paste the following code between the Sub and End Sub lines and change MyDateField and tblMyTable to your field / table
names as required.
When they click the button the records are written to the table.
** Untested air code so keep backup before using
'******Code Start*******
Dim strSQL as String
Dim db as Database
Dim rst as DAO.Recordset
Dim dtStartDate As Date
Dim intNoOfDates As Integer
Dim i As Integer
On Error GoTo HandleIt
If Not IsNull(Me.DateInputTextboxName) Then
strSQL = "SELECT MyDateField FROM tblMytable;
Set db = CurrentDB()
Set rst = db.OpenRecordset(strSQL,dbOpenDynaset,dbAppendOnly )
dtStartDate = Me.DateInputTextboxName
intNoOfDates = 999 'change to required number of dates
With rst
'add start date
.Addnew
!MyDateField = dtStartDate
.Update
'now add required dates
For i = 1 To intNoOfDates - 1
.AddNew
!MyDateField = DateAdd("d", i, dtStartDate)
.Update
Next i
.Close
End With
End If
MsgBox "Dates added to table", vbInformation + vbOKOnly, "Success"
OutHere:
If Not (rst Is Nothing) Then Set rst = Nothing
If Not (db Is Nothing) Then Set db = Nothing
Exit Sub
HandleIt:
MsgBox "Operation Failed", vbCritical + vbOKOnly, "Bummer"
Resume OutHere
'******Code End*******
Wayne Gillespie
Gosford NSW Australia