By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,720 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Add records programmatically?

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
This should add 10 years worth of dates to tblDate.TheDate:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2013#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Shyguy" <sh****@aol.com> wrote in message
news:e0********************************@4ax.com...
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

Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a
On Sat, 26 Jun 2004 05:57:20 GMT, Wayne Gillespie
<be*****@NObestfitsoftwareSPAM.com.au> wrote:
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


Thank you very much. This worked perfectly. I neglected to say that
some days had to be added twice but I worked that out with a query and
the table is created with the dates I need.

Thanks again

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.