By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

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

P: 8
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.


Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Current()
  3.  
  4. Dim rst As DAO.Recordset
  5. Dim lngCount As Long
  6.  
  7. Set rst = Me.RecordsetClone
  8.  
  9. With rst
  10. .MoveFirst
  11. .MoveLast
  12. lngCount = .RecordCount
  13. End With
  14.  
  15.  
  16. Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount
  17.  
  18. End Sub
  19.  

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Option Explicit
  4. Dim Records As DAO.Recordset
  5. Dim TotalRecords
  6.  
  7. Private Sub Form_Load()
  8. Set Records = Me.RecordsetClone
  9. Records.MoveLast
  10. TotalRecords = Records.RecordCount
  11. End Sub
  12.  
  13. Private Sub Form_BeforeInsert(Cancel As Integer)
  14. Me![RecNum].Caption = TotalRecords + 1 & " pending..."
  15. End Sub
  16.  
  17. Private Sub Form_AfterInsert()
  18. Records.MoveLast
  19. TotalRecords = Records.RecordCount
  20. End Sub
  21.  
  22. Private Sub Form_Current()
  23. If Not Me.NewRecord Then
  24. Records.Bookmark = Me.Bookmark
  25. Me![RecNum].Caption = "Record " & _
  26. Records.AbsolutePosition + 1 & " of " & _
  27. TotalRecords
  28. Else
  29. Me![RecNum].Caption = "New Record"
  30. End If
  31. End Sub 
  32.  
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.
Feb 4 '09 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
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.
Feb 5 '09 #2

P: 8
Thank you ChipR,

Is there a way to display a record counter in a text box or label that is in a sub form?
Feb 6 '09 #3

Expert 100+
P: 1,287
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.
Feb 6 '09 #4

P: 8
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.
Feb 6 '09 #5

Expert 100+
P: 1,287
Can you turn on Navigation Buttons in the subform's properties?
Feb 6 '09 #6

P: 8
Yes but I am trying to avoid that because some of the form I are very long and the id field is on top.
Feb 6 '09 #7

Expert 100+
P: 1,287
Did you try my suggestion in post #4 in the subform header?
Feb 6 '09 #8

P: 8
yes thank you Chip R post #4 works in other pc
Feb 6 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.