Re: Looping A Specified Number Of Times

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/Forums.aspx/databases-ms-access/200509/1
.

Quantcast