469,917 Members | 1,661 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Looping A Specified Number Of Times

Hi,

hopeing someone can help me here...

I have a deductions table and form and want to be able to add many deductions
to the table by having only a few fields on the form

form fields: deduction ID, start date, end date and amount

what i want to be able to do is keep adding a deduction each 7 days from the
start date to the end date. ie only loop the number of times (01/01/01 to
14/01/01 would add 2 records)

hope i made some sense...

thanks
Lee-Anne
Nov 13 '05 #1
2 1839
"ozgirl via AccessMonster.com" <u14396@uwe> wrote in message
news:5514041f53b76@uwe...
Hi,

hopeing someone can help me here...

I have a deductions table and form and want to be able to add many
deductions
to the table by having only a few fields on the form

form fields: deduction ID, start date, end date and amount

what i want to be able to do is keep adding a deduction each 7 days from
the
start date to the end date. ie only loop the number of times (01/01/01 to
14/01/01 would add 2 records)

hope i made some sense...

thanks
Lee-Anne



When you say "fields on the form" it is not clear if this is a bound form or
whether you simply mean textboxes on the form. You also don't say whether a
single value for [deduction id] goes in for each record as the same value or
whether this is some sort of autonumber for the table which we don't have to
worry about setting a value for.
So I'm assuming your table is called [My Table] and each time we need to put
in values for an incrementing [My Date] and a static [My Amount]. Your form
has textboxes called txtStartDate, txtEndDate and txtAmount and a button
cmdUpdate which runs the code.
The complication with this code is that because you want multiple records to
be added you must make sure you wrap it all up in a transaction, otherwise
you may add only, say 3 of 5 records if some error occurred. Better that
you add either zero records and report the error or all 5 and report
success.

Private Sub cmdUpdate_Click()

On Error GoTo Err_Handler

Dim dteStart As Date
Dim dteEnd As Date
Dim curAmount As Currency
Dim strSQL As String
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long

If IsNull(Me.txtStartDate) Then
MsgBox "Enter a start date", vbInformation
Me.txtStartDate.SetFocus
Exit Sub
Else
dteStart = CDate(Me!txtStartDate)
End If

If IsNull(Me.txtEndDate) Then
MsgBox "Enter an end date", vbInformation
Me.txtEndDate.SetFocus
Exit Sub
Else
dteEnd = CDate(Me!txtEndDate)
End If

If IsNull(Me.txtAmount) Then
MsgBox "Enter an amount", vbInformation
Me.txtAmount.SetFocus
Exit Sub
Else
curAmount = CCur(Me!txtAmount)
End If

strSQL = "SELECT * FROM [My Table]"

Set wks = DBEngine.Workspaces(0)

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)

wks.BeginTrans

While dteEnd >= dteStart
rst.AddNew
rst.Fields("My Date") = dteStart
rst.Fields("My Amount") = curAmount
rst.Update
lngCount = lngCount + 1
dteStart = DateAdd("d", 7, dteStart)
Wend

wks.CommitTrans

MsgBox CStr(lngCount) & " records(s) added", vbInformation

Exit_Handler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Set wks = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub


Nov 13 '05 #2
many thanks Brian..

much appreciated...

Lee-Anne

Brian Wilson wrote:
Hi,

[quoted text clipped - 15 lines]
thanks
Lee-Anne


When you say "fields on the form" it is not clear if this is a bound form or
whether you simply mean textboxes on the form. You also don't say whether a
single value for [deduction id] goes in for each record as the same value or
whether this is some sort of autonumber for the table which we don't have to
worry about setting a value for.
So I'm assuming your table is called [My Table] and each time we need to put
in values for an incrementing [My Date] and a static [My Amount]. Your form
has textboxes called txtStartDate, txtEndDate and txtAmount and a button
cmdUpdate which runs the code.
The complication with this code is that because you want multiple records to
be added you must make sure you wrap it all up in a transaction, otherwise
you may add only, say 3 of 5 records if some error occurred. Better that
you add either zero records and report the error or all 5 and report
success.

Private Sub cmdUpdate_Click()

On Error GoTo Err_Handler

Dim dteStart As Date
Dim dteEnd As Date
Dim curAmount As Currency
Dim strSQL As String
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long

If IsNull(Me.txtStartDate) Then
MsgBox "Enter a start date", vbInformation
Me.txtStartDate.SetFocus
Exit Sub
Else
dteStart = CDate(Me!txtStartDate)
End If

If IsNull(Me.txtEndDate) Then
MsgBox "Enter an end date", vbInformation
Me.txtEndDate.SetFocus
Exit Sub
Else
dteEnd = CDate(Me!txtEndDate)
End If

If IsNull(Me.txtAmount) Then
MsgBox "Enter an amount", vbInformation
Me.txtAmount.SetFocus
Exit Sub
Else
curAmount = CCur(Me!txtAmount)
End If

strSQL = "SELECT * FROM [My Table]"

Set wks = DBEngine.Workspaces(0)

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)

wks.BeginTrans

While dteEnd >= dteStart
rst.AddNew
rst.Fields("My Date") = dteStart
rst.Fields("My Amount") = curAmount
rst.Update
lngCount = lngCount + 1
dteStart = DateAdd("d", 7, dteStart)
Wend

wks.CommitTrans

MsgBox CStr(lngCount) & " records(s) added", vbInformation

Exit_Handler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Set wks = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by kaptain kernel | last post: by
4 posts views Thread by David | last post: by
4 posts views Thread by Paul M | last post: by
5 posts views Thread by Karen | last post: by
5 posts views Thread by Bruce Lawrence | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.