Populate Record Counter of x of y

[fish919]

Populate Record Counter of x of y

Hello I am having a little trouble with by 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’ ”

Here is the code I put in to the children’s table under forms, events, form_Current.
The output is displeased in a unbound text box.

   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

I have found two older threads that deal with this issue but I could not get the program to work. It gives me two run time-error messages. The first is run-time error ‘13’ Type mismatch. The second run time error is run-time error ‘91’ object variable or With block variable not set.
The names of these threads are: Display record number and count Display record number and count, Populate a text box or label with Record x of y.

The code for the older threads is.


Option Compare Database
Option Explicit
Dim Records As 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 have been working on this problem for a wile and I can’t fix it my self.

 

[PHV]

Replace this:
Dim Records As Recordset
with this:
Dim Records As [!]DAO.[/!]Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fish919]

I wish to make in amendment on my statement about the two older threads not working the program from the older threads works grate. Except I get the same run-time error as when I put the table in the parent table the error is “run-time error ‘3021’ ”

 

[fish919]

Thanks