Full Version: Record Number Function
UtterAccess Forums > Microsoft® Access > Access Forms

I'm building a form with a query as the record source, and would like to add a textbox with a function to display the current record number out of the total in the recordset. In other words, as the person navigates through the records, I would like the textbox to say "Record X of N". It would not be appropriate to accomplish this with the built-in navigation controls, in this particular situation.

Any thoughts on what syntax would be required to build this function? As always, I appreciate any and all help offered!
A x of x is dependent on the sort so can change if the order changes. So it is almost meaningless. What value do you think it gives you to display that? Why can't you just use the Navigation Bar?
I think recordset.absoluteposition
is what you're looking for
Hello, and thanks for your responses!

The form will be used to collect survey responses, and I'd rather not display the navigation bar. Since the form displays one question at a time, I thought the user would like to be able to see their progress through the survey. The sort will be consistent for each variation of the survey, though the record count changes.

Anyway, my solution follows:

Private Sub Form_Current()
    Dim intAP As Integer, intRC As Integer
    intAP = Recordset.AbsolutePosition + 1
    intRC = Recordset.RecordCount
    Me.txtProgress = "Question " & intAP & " of " & intRC

End Sub

Again, many thanks!
Many SQL DBMSs do support the standard ROW_NUMBER() function but unfortunately Access does not. If your data source is SQL Server, Oracle or another DBMS then use ROW_NUMBER(). In Access you can imitate the same functionality using DCOUNT. Example:

SELECT DCOUNT("id","Table1","id<="&id) AS ROW_NUMBER, *
FROM Table1

where "id" in this case just means a unique column you want to order by.
I'd suggest a minor tweak.
add me. before recordset

Private Sub Form_Current()
    Dim intAP As Integer, intRC As Integer
    intAP =me.Recordset.AbsolutePosition + 1
    intRC = me.Recordset.RecordCount
    Me.txtProgress = "Question " & intAP & " of " & intRC

End Sub
Ok, so the user is taking a survey, based on which variation, you will know the number of questions. Why not just use a TempVar or one record table, to record how many questions completed, then you can calculate and display the percentage completed, which, IMHO would be a more meaningful number to the user. Or you could display a progress bar.

The others have given you answers to your specific question and I can see the value in your case. But, personally, I would go with a percentage complete or progress bar.
Using the Form_Current event and assigning a simple

= "Record " & CurrentRecord & " Of " & RecordsetClone.RecordCount & " Records"

to a Label Caption or to a Textbox Value has always worked for me. Because the Access Gnomes don't always load the entire RecordSet on loading a Form, I force this with
Private Sub Form_Load()
  DoCmd.GoToRecord , , acLast
  DoCmd.GoToRecord , , acFirst
End Sub

Knowing where you are, in a given RecordSet, as it is currently sorted, can be helpful, at times, and you could modify the above to simply give a percentage instead of a 'Nth Record of N Records.' To me it's all a matter of taste.

Linq ;0)>

In the end, I kept my record number textbox, because I thought it would help the user navigate through the survey if they decided to skip a question and return to it later. However, I added a progress bar to show the percentage of questions which have been answered. Why choose when you can have it all? cool.gif

Again, many thanks to all for your valuable assistance!

Just to add my penny...

I've used what missinglinq posted for years now.

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.