weightinwildcat
Programmer
I am working on a subroutine for a start button that is used to select jobs for processing. Jobs are selected one at a time. However, the jobs chosen by a given user should all be deselected if the button is clicked on for any of them.
My problem is that, if the first record is the one that is chosen for deselection I get error 3020, Update or CancelUpdate without AddNew or Edit. The error appears when this line of code runs:
Me.ElapsedTimeBox = elBox + elTime
Here is the code for the whole subroutine:
Private Sub StartBtn_Click()
Dim db As Database
Dim rs, rs2, rscl As Recordset
Dim rCount As Long
Dim stTime As Date
Dim elTime As Long
Dim elBox As Long
Set db = CurrentDb()
If Me.RedTag = 0 Then
rCount = 0
Set rs = db.OpenRecordset("Select RedTag From CNCApprovedOrdersQuery " & _
"Where RedTag = " & UserID, dbReadOnly)
rCount = rs.RecordCount
rs.Close
Set rs = Nothing
If rCount = 0 Then
If IsNull(Me.StartTimeBox) Then
Me.StartTimeBox = Now()
'copy the value into temporary location for further calculations if PAUSE will be pressed at any time.
Me.PausePressedTime = Now()
Me.RedTag = UserID
Else
'if record had been timestamped, ask user whether he wants to make a change
'put a new timestamp if YES is chosen
Me.PausePressedTime = Now()
Me.RedTag = UserID
End If
Else
If MsgBox("Do you want to run a simultaneous job?", vbYesNo) = vbYes Then
'copy the value into temporary location for further calculations if PAUSE will be pressed at any time.
Me.PausePressedTime = Null
Me.RedTag = UserID
Else
MsgBox "Job Cancelled", vbInformation
End If
End If
Else
Me.PauseDepressedTime = Now()
Set rs = db.OpenRecordset("Select * From CNCApprovedOrdersQuery " & _
"Where RedTag = " & UserID, dbReadOnly)
rCount = rs.RecordCount
rs.FindFirst Not IsNull(rs!PausePressed)
If rs.NoMatch = False Then
stTime = rs!PausePressed
Interval = DateDiff("n", stTime, Now())
elTime = Interval / rCount
End If
rs.Close
Set rs = Nothing
Set rscl = Me.RecordsetClone
rscl.MoveLast
Do While Not rscl.BOF
If rscl!RedTag = UserID Then
Me.Bookmark = rscl.Bookmark
elBox = Me.ElapsedTimeBox
[highlight #EF2929]Me.ElapsedTimeBox = elBox + elTime[/highlight]
Me.RedTag = 0
End If
rscl.MovePrevious
Loop
rscl.Close
Set rscl = Nothing
End If
Me.Refresh
End Sub
My problem is that, if the first record is the one that is chosen for deselection I get error 3020, Update or CancelUpdate without AddNew or Edit. The error appears when this line of code runs:
Me.ElapsedTimeBox = elBox + elTime
Here is the code for the whole subroutine:
Private Sub StartBtn_Click()
Dim db As Database
Dim rs, rs2, rscl As Recordset
Dim rCount As Long
Dim stTime As Date
Dim elTime As Long
Dim elBox As Long
Set db = CurrentDb()
If Me.RedTag = 0 Then
rCount = 0
Set rs = db.OpenRecordset("Select RedTag From CNCApprovedOrdersQuery " & _
"Where RedTag = " & UserID, dbReadOnly)
rCount = rs.RecordCount
rs.Close
Set rs = Nothing
If rCount = 0 Then
If IsNull(Me.StartTimeBox) Then
Me.StartTimeBox = Now()
'copy the value into temporary location for further calculations if PAUSE will be pressed at any time.
Me.PausePressedTime = Now()
Me.RedTag = UserID
Else
'if record had been timestamped, ask user whether he wants to make a change
'put a new timestamp if YES is chosen
Me.PausePressedTime = Now()
Me.RedTag = UserID
End If
Else
If MsgBox("Do you want to run a simultaneous job?", vbYesNo) = vbYes Then
'copy the value into temporary location for further calculations if PAUSE will be pressed at any time.
Me.PausePressedTime = Null
Me.RedTag = UserID
Else
MsgBox "Job Cancelled", vbInformation
End If
End If
Else
Me.PauseDepressedTime = Now()
Set rs = db.OpenRecordset("Select * From CNCApprovedOrdersQuery " & _
"Where RedTag = " & UserID, dbReadOnly)
rCount = rs.RecordCount
rs.FindFirst Not IsNull(rs!PausePressed)
If rs.NoMatch = False Then
stTime = rs!PausePressed
Interval = DateDiff("n", stTime, Now())
elTime = Interval / rCount
End If
rs.Close
Set rs = Nothing
Set rscl = Me.RecordsetClone
rscl.MoveLast
Do While Not rscl.BOF
If rscl!RedTag = UserID Then
Me.Bookmark = rscl.Bookmark
elBox = Me.ElapsedTimeBox
[highlight #EF2929]Me.ElapsedTimeBox = elBox + elTime[/highlight]
Me.RedTag = 0
End If
rscl.MovePrevious
Loop
rscl.Close
Set rscl = Nothing
End If
Me.Refresh
End Sub