Error message ‘3021’ when I use a sub form with a Record Counter x

[fish919]

Hello All,
I am having a little trouble with my database. The Database has a parent table with 5 or 6 children tables in it they are linked by a common id filed.

The problem is that the children tables use a VBA to display there Record number in a text box. The code works fine when they are displayed alone when I do not have them displayed in the main or parent table. The problem I get is "run-time error '3021' "


I am ataching two version of code:

Here is the first version of code I put in to the children's table under forms, events, form_Current.

The output is displeased in a unbound text box called txtRecordNo.

 
Private Sub Form_Current()
 
Dim rst As DAO.Recordset
Dim lngCount As Long
 
Set rst = Me.RecordsetClone
 
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
 
 
Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount
 
End Sub
 

Hear is the secend version of code it is linked by in unbound label called RecNum:

 
Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords
 
Private Sub Form_Load()
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub
 
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![RecNum].Caption = TotalRecords + 1 & " pending..."
End Sub
 
Private Sub Form_AfterInsert()
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub
 
Private Sub Form_Current()
If Not Me.NewRecord Then
Records.Bookmark = Me.Bookmark
Me![RecNum].Caption = "Record " & _
Records.AbsolutePosition + 1 & " of " & _
TotalRecords
Else
Me![RecNum].Caption = "New Record"
End If
End Sub 
 

I do not know how to deal with a run-time error '3021'. So if some one has a suggestion on how do get this code to work or a different way of displaying the records in a sub form.

[ChipR]

When working with a recordset, you need to check for records.BOF or records.EOF. If you are .BOF (before beginning or something) there may be no records at all, and if you are .EOF you are past the end of the records. Either way, there is no current record to do operations on, hence the error message.

[fish919]

Thank you ChipR,

Is there a way to display a record counter in a text box or label that is in a sub form?

[ChipR]

Do you mean a count of all the records shown in the subform? If so, you can put a text box in the subform's Form Footer and make it

=Count(FieldName)

Where FieldName is any field that is shown in the subform for each record.

[fish919]

I tried to put =[CurrentRecord]

to cont the current record and

=RecordsetClone.Recordcount
to count the total records
I put both of these 'code' in the control source of the textbox. They work on my pc but not any other pc I tried. I get a #name in the textbox. However the code in VBA works in other pc when I am not running them in a sub form.

[ChipR]

Can you turn on Navigation Buttons in the subform's properties?

[fish919]

Yes but I am trying to avoid that because some of the form I are very long and the id field is on top.

[ChipR]

Did you try my suggestion in post #4 in the subform header?

[fish919]

yes thank you Chip R post #4 works in other pc