470,830 Members | 1,652 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,830 developers. It's quick & easy.

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

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
  2. Private Sub Form_Current()
  4. Dim rst As DAO.Recordset
  5. Dim lngCount As Long
  7. Set rst = Me.RecordsetClone
  9. With rst
  10. .MoveFirst
  11. .MoveLast
  12. lngCount = .RecordCount
  13. End With
  16. Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount
  18. End Sub

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

Expand|Select|Wrap|Line Numbers
  2. Option Compare Database
  3. Option Explicit
  4. Dim Records As DAO.Recordset
  5. Dim TotalRecords
  7. Private Sub Form_Load()
  8. Set Records = Me.RecordsetClone
  9. Records.MoveLast
  10. TotalRecords = Records.RecordCount
  11. End Sub
  13. Private Sub Form_BeforeInsert(Cancel As Integer)
  14. Me![RecNum].Caption = TotalRecords + 1 & " pending..."
  15. End Sub
  17. Private Sub Form_AfterInsert()
  18. Records.MoveLast
  19. TotalRecords = Records.RecordCount
  20. End Sub
  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 
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
8 2271
1,287 Expert 1GB
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
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
1,287 Expert 1GB
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


Where FieldName is any field that is shown in the subform for each record.
Feb 6 '09 #4
I tried to put =[CurrentRecord]

to cont the current record and

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
1,287 Expert 1GB
Can you turn on Navigation Buttons in the subform's properties?
Feb 6 '09 #6
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
1,287 Expert 1GB
Did you try my suggestion in post #4 in the subform header?
Feb 6 '09 #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.

Similar topics

1 post views Thread by Colin Graham | last post: by
9 posts views Thread by Robert Wing | last post: by
3 posts views Thread by WindAndWaves | last post: by
3 posts views Thread by windandwaves | last post: by
5 posts views Thread by prakashwadhwani | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.