Record Numbers on Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Professionals: On my form I have set the property of the navigation buttons
to "no" and have designed my own buttons to go to the first, next, previous,
and last records. The problem with this is it also eliminates the record
numbers (page x of y). My form is set to single page (one record shows on
each page). I would like my users to see page x of y so they know how many
records have been entered, etc. Typically they will not filter this form but
use it to add records and make updates. Thanks in advance.
 
Create an unbound text box on your form called txtRecordNumber. Put the
following code in the current event of your form.

Private Sub Form_Current()

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

Me.txtRecordNumber = "Record number " & Me.CurrentRecord & " of " & lngCount
& " records"

End Sub
 
Lynn Trapp: Thank you for your response.

I really do not know much about VBA at this point; I was getting a type
mismatch error. I went through the code you provided and remmed the lines out
individually.

This worked perfectly for me as long as I remmed this line out: 'Dim rst As
DAO.Recordset.

Why I am not sure but it worked perfectly!

Thank you much for your help.
 
You may like to try placing a TextBox on the Form with the ControlSource:

= [CurrentRecord] & " of " & [Recordset].[RecordCount]

I tested this in A2002 which worked fine but IIRC, the RecordCount (i.e. the
y value) may not show correctly if your computer is slow or the Form's
RecordSource is complex. The reason is that the Form's Recordset may not be
fully populated when the Form is painted.
 
Hi Lynn,
I was reading your answer you provided with below but I get errors everytime
I try and run the code.
Private Sub Form_Current() - turns yellow.
& " records" - turns red.
It says there's a compile error.
I would be grateful if you could help me out..thanks in advance.
 
You've been caught by "Word Wrap"!

The following should be on one line with a space between lngCount and the last
ampersand (&):

Me.txtRecordNumber = "Record number " & Me.CurrentRecord & " of " & lngCount
& " records"

HTH
 
Hi, I finally get to post a solution instead of a plea for help!

FYI to anyone reading this thread:

Modifying Lynn's code, I was able to put the record number in a textbox by
itself, and total records in a transparent textboxt by itself and mimic
Access' default navigation bar. I still don't know why I need to rem out the
first line, but it works!
p.s. note the two text box names.

Thanks Lynn!

Private Sub Form_Current()
' Display Current Record in Custom Navigation Bar On Current Event
' Displays Total Records in Custom Navigation Bar On Current Event
' Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

With rst
..MoveFirst
..MoveLast
lngCount = .RecordCount
End With

Me.txtRecNo = Me.CurrentRecord
Me.ctlTotalRecs = "of " & lngCount

End Sub


m-

<..^
•<
Click to expand...
 
when the "Dim rst..." line is NOT commented out, do you get a compile error
on the code? if so, you probably need to set a reference to the DAO library,
which is not set by default in Access 2000.

also, if your code compiles when the "rst" variable is not explicitly
declared, then the "Option Explicit" declaration is not included in your
module. recommend you add it to the top of all existing modules in all of
your databases, as

Option Compare Database
Option Explicit

note that you'll need to compile your code, and declare (Dim) any variable
that is not already declared in a procedure (the compile action will find
each undeclared variable for you).

then, in the VBA Editor window, click Tools | Options | Editor tab and
checkmark the box next to Require Variable Declaration. now the Option
Explicit statement will automatically appear in every new module you create
in any database on that computer with that installation of Access.

hth
 
Thanks for you info, I have used this to make each record into week number
for my maintenance schedule. Below is modified code
Private Sub Form_Current()

Dim rst As DAO.recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordNumber = "WEEK NUMBER " & Me.CurrentRecord & ""
End Sub
I would like to open form the correct week number for the current date and
or beable to type the week number before opening form.
Is this possible??
 
Back
Top