×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.


CODE

    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.



CODE

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.
 

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

how about

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

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

(OP)
I am sorry I don't follow.  

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

Here is a demo

CODE

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

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

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

CODE

(rs.BOF And rs.EOF) Then

but i get a  run-time error '3078'

thanks again for your help.

 

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

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

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

(OP)
Here is the code thank you for looking at the code.

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

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

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

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

(OP)
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.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close