Error with custom navigation record count

[munkee]

I use the following code to add the number of records in to my custom navigation within my access database. A simple "Cost x of y" in my sub form that allows me to add costs to a particular activity, so users know how many have been added to that particular activity so far etc.

I also use the same code within my main form navigation to display the current record "x of y" for the activities.

I have changed the declared variables and ensured there is no conflict in other areas by simply adding a 2 to the end for example rst2 in my sub form and rst in my main form.

The problem I am having is when I load up my main form and navigate to the last record, which should then create a blank new record I get an error in the following code which is attached to my sub form navigation (subfrmCosts):

Private Sub Form_Current()
'Provide record counter for costs
Dim rst2 As DAO.Recordset
Dim lngCount2 As Long
 
Set rst2 = Me.RecordsetClone
 
With rst2
.MoveFirst
.MoveLast
lngCount2 = .RecordCount
End With
 
'Show the results of the count
 
If Me.CurrentRecord > lngCount2 Then
Me.txtCostRecNo = "New Cost"
 
Else
 
Me.txtCostRecNo = "Cost " & Me.CurrentRecord & " of " & lngCount2
End If
 
 
End Sub

Similar code to the above works fine in my main form (with the 2's removed to ensure unique names to variables etc), just not in the sub form controls.

The error is highlighted on the line .MoveLast and I get an alert showing "Run time error 3021" No current record.

I thought I would have stopped this error occuring by adding the if function so that it would display "New Cost" however this doesnt seem to be working, eventhough yesterday everything was running fine.

Any help appreciated thanks.

[munkee]

Just a quick follow up, when I disable the code within the subform the main form code works perfectly fine.

When I re-enable the code in the sub form. The Sub form numbers work correctly however if I navigate to the last record within the main form instead of displaying "New Record" I get the error. Which highlights the sub form code, not the main form code.

[munkee]

Managed to do some research and messed around with the code to get it working now:

Public Sub Form_Current()
'Provide record counter for costs
Dim rst2 As DAO.Recordset
Dim lngCount2 As Long
 
Set rst2 = Me.RecordsetClone
 
With rst2
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
 
'Show the results of the count
If Me.CurrentRecord > .RecordCount Then
 
Me.txtCostRecNo = "New Cost Record"
 
Else
 
Me.txtCostRecNo = "Cost " & Me.CurrentRecord & " of " & .RecordCount
 
End If
End With
End Sub

Used similar code for the main form also and it works fine now.