"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
