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

[fish919]

Hello 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' "

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 called txtRecordNo.

    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 I get the same run-time error message "run-time error '3021' "

here is the code of the older threads. the code is linked to 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.

 

[MajP]

how about

If not (rst.eof and rst.bof) Then ...

 

[fish919]

I am sorry I don’t follow.

 

[MajP]

Here is a demo

Public Sub test()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblTempImport")
  rs.MoveFirst
  rs.MoveLast
  MsgBox rs.RecordCount

'Everything works fine.  
'Now return a recordset without
'any records.  If I try to move first I get error 3021
'So I check to see if I have any records  

  Set rs = CurrentDb.OpenRecordset("Select * from tblTempImport where True = False")
  If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    rs.MoveLast
  End If
  MsgBox rs.RecordCount
  
End Sub

When you are in a subform there will likely be cases where no records are returned. If you try to move first or last you will get error 3021

 

[fish919]

thanks for your help but i still do not quite get it. i tryed to put in the code you suggested

(rs.BOF And rs.EOF) Then

but i get a run-time error '3078'

thanks again for your help.

 

[MajP]

Please post your whole code. The issue is to ensure you have records before issuing a move first/last command.

 

[fish919]

Here is the code thank you for looking at the code.

Private Sub Form_Current()

Dim rst As DAO.Recordset
    Dim lngCount As Long

'I named the table SLE_database_study_and_past_medical_History

    Set rst = CurrentDb.OpenRecordset("SLE_database_study_and_past_medical_History")

    With rst
        rst.MoveFirst
        rst.MoveLast
        lngCount = .RecordCount
    
  Set rst = CurrentDb.OpenRecordset("Select * from SLE_database_study_and_past_medical_History where True = False")
  If Not (rst.BOF And rs.EOF) Then
    rst.MoveFirst
    rst.MoveLast
  
End With
End If

  MsgBox rst.RecordCount
  

    Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount


End Sub

 

[MajP]

Error '3078' means it can not find your table. Double check that the name is correct. You probably have a misspelling somewhere that is one long name:
SLE_database_study_and_past_medical_History

I removed the with statements because you did not use them correctly or really use them at all.

Private Sub Form_Current()

Dim rst As DAO.Recordset
Dim lngCount As Long

'I named the table SLE_database_study_and_past_medical_History

Set rst = CurrentDb.OpenRecordset("SLE_database_study_and_past_medical_History")

if not (rst.eof and rst.bof) then
rst.MoveFirst
rst.MoveLast
lngCount = rst.RecordCount
end if

Set rst = CurrentDb.OpenRecordset("Select * from SLE_database_study_and_past_medical_History where True = False")

If Not (rst.BOF And rs.EOF) Then
rst.MoveFirst
rst.MoveLast
End If

MsgBox rst.RecordCount

Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount

End Sub

 

[fish919]

Thanks again MajP, for your help. I put the code you send and I get the same run-time error ‘3078’

and I copied the table name to the code so I know I did not spell it wrong.

I am not shore if I understood correctly the line of code about ‘tblTempImport’ I understood it to mean put the name of the table the form calls. The sub form I am working on not the parent table. Please tell me if this make cense.

Set rs = CurrentDb.OpenRecordset("Select * from tblTempImport where True = False")

But I thing I am going to cheat and use the express builder.

to count the current record I will use.

=[CurrentRecord]

to count the total records.

=Count(FieldName)

Where Field Name is any field that is shown in the form for each record.

This work in ever computer with accesses I tried so far.

Thanks for your help I really appreciate your help; maybe this thread will help some one else. With the same problem.