No Current Record Error (1 Viewer)

siddhusingh1968

New member
Local time
Today, 07:56
Joined
Apr 9, 2011
Messages
7
Move date value from previous record - works - no record error

I am a new member (Joined today!!). I have a database that has a basic MS Project structure i.e. for each task Plan_Start, Plan_End, Duration. When assigned to members the tasks are sequential, Plan_End is calculated based on adding Duration to Plan_Start. So now if a member adjusts the duration of one of his tasks in order to adjust dates downstream this is what I wrote: "Using Access 2007 on Home PC and Access 2003 on work PC"
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
Dim PREVDATE As String 'This will hold the value of the Plan_End of the task in a variable to carry to Plan_Start of next record
If rst.RecordCount <> 0 Then
End If

rst.MoveFirst 'moves cursor to first record

Do While Not rst.EOF
PREVDATE = rst.Fields("dtend") ' Copies Plan_End from first record
'Temporarily store this date value
rst.MoveNext ' moves cursor to next
rst.Edit
rst.Fields("Start") = PREVDATE 'Puts value held in PREVDATE variable in Plan_Start of next record
rst.Update
Loop
End Sub

The code works and updates the dates fine, but I get a No Current Record Error=3021. I cannot figure out trapping this error. Please help..
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:56
Joined
Aug 30, 2003
Messages
36,118
I suspect the problem is because the MoveNext is before the Edit. It moves the recordset to EOF at which point the Edit will error. Typically the MoveNext is the last line before the Loop line.
 

siddhusingh1968

New member
Local time
Today, 07:56
Joined
Apr 9, 2011
Messages
7
You are right, Paul, appreciate the quick response. However if I move the .movenext to right before the Loop as is normally done then the routine will not copy the previous record's end date to the start of the next record. It will then just copy the end date to the start date of the same record and that is not the intent of this exercise... I have been pulling my hair out trying to figure this out!!
Thx again
 

siddhusingh1968

New member
Local time
Today, 07:56
Joined
Apr 9, 2011
Messages
7
Re: No Current Record Error - move end date from prev rec to start dt of next

Guys I got this figured out for the benefit of others. If someone is trying to look for a loop that will hold a value of an end date from previous record and update the start date of the next record (using Access for Project Planning and automatically updating successor tasks by only updating Work in Progress task). This will work..

'Code starts here

'Code by dsiddhu
'Modified 4/9/2011 with help from Gustav and Project Today at utter 'access
'Code starts here
Private Sub Command4_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
Dim PREVDATE As String ' will hold value of end date from previous record in this variable
If rst.RecordCount <> 0 Then
End If
rst.MoveFirst 'moves cursor to first record
Do
PREVDATE = rst.Fields("dtend")
rst.MoveNext ' test
If Not rst.EOF Then
rst.Edit
rst.Fields("Start") = PREVDATE 'Puts value held in PREVDATE variable in start date of next record
rst.Update

Else
Exit Do
End If
'MsgBox "Test End Date Loop: " & Me.Dtend ' to test and step through code
Loop
rst.Close
End Sub
 
Local time
Tomorrow, 00:56
Joined
Aug 8, 2010
Messages
245
In addition to your code, when you want to copy the value from the previous record, it is always a good idea to use the Default Value property of a control.
The benefit is that you can set the default property of a control without dirtying the record and user can cancel that new record without triggering the no current record error.
To set the default property of a control, you must format the value as a string, which you have already done ( Dim PREVDATE As String).

For date values, because a machine's regional date format setting can be almost anything, you should format the datevalue to an unambiguous style to make sure Access interprets it the way you intended. E.g.
Me.Start.DefaultValue = Format(PREVDATE, "\#yyyy-m-d\#")
 
Last edited:

siddhusingh1968

New member
Local time
Today, 07:56
Joined
Apr 9, 2011
Messages
7
I agree Bob, that is redundant. Specially in this particular scenario the recordset is never going to be null. It is a planning database and always has Backlog. The Backlog may be low, but there are always records there, worse case 9-10 records, never 0. I do need the loop to start at the first record hence the .movefirst.
However in my original post this is not where my code was getting hung up. It was getting hung up right after .movenext and then pasting the prevdate value in the enddate field.
I modified the code and was able to trap the error.

Appreciate the feedback and help.

My question is why do you have this in there?

If rst.RecordCount <> 0 Then
End If



That does NOTHING. You should be going to the exit point of your procedure if the record count is 0.
Code:
If rst.RecordCount = 0 Then
   GoTo Exit_MyProcedure
End If

You can't use MoveNext if there are no records and the code you have will generate errors if there are no records because of the code I showed.
 

siddhusingh1968

New member
Local time
Today, 07:56
Joined
Apr 9, 2011
Messages
7
Thx Jeanette. I have used the default property where I wanted to carry previous record values to a New Record and hence am not using the default property. Here's the background on the app.. No new records will be generated for this exercise. I have a planning database where an algorithm assigns resources to a specific sort order and assigns to team members. Team members update their planned end dates and % complete. Everything on Backlog is assigned and there is always tasks that are work in progress. Due to issues with tasks sometimes the next tasks are reassigned to another Team member/resource based on the progress of work, priority and strategic goals. Teammembers are to only update their current work in progress tasks. This sequence has to be run to synchronize the dates so when tasks are reassigned it will automatically cascade everything that is downstream of the task that is 'Work in Progress', etc.
 

Rasha Elsayed

New member
Local time
Today, 07:56
Joined
Apr 20, 2017
Messages
1
If you sure there are records you can use: .Move First,, if you are not you can use

If rs.RecordCount = 0 Then
rs.AddNew
Else
rs.MoveFirst
End If

--------------------
because we can't use .MoveFirst while there are no records.
Good Luck
 

Users who are viewing this thread

Top Bottom