Record number

[Adam]

Hi All,

I have created a form which has a control source from a query I've
created.

Currently the query returns 26 records so when viewing the form you can
use the record selectors to move to the next record.

Is there anyway I can add a field to the form which shows that the
current record your on and out of how many?


Many Thanks

Adam

 

[BruceM]

One way is to place two hidden unbound text boxes (txtCurrent and txtTotal)
on the form, and an unbound visible text box (txtCounter). Add the
following code to the form's On Current event:

Me.txtCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
Me.txtTotal = Me.RecordsetClone.RecordCount
Me.txtCounter = "Record " & Me.txtCurrent & " of " & Me.txtTotal

You could also make the hidden text boxes visible and omit txtCounter, if
you prefer.

I have used the above code successfully (I got it from somebody here quite a
while ago), but you should be able to do the same thing without the hidden
text boxes:

strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
Me.txtCounter = "Record " & strCurrent & " of " &
Me.RecordsetClone.RecordCount

I have not done a lot of testing with this option, so I cannot vouch for it
100%.

 

[BruceM]

I did some more testing, and it seems that it can be done on one line:

Me.txtCounter = "Record " & Me.CurrentRecord & " of " &
Me.RecordsetClone.RecordCount

Again, I have not done much testing with this. I would be interested in
hearing from others with more experience with this topic.

 

[Arvin Meyer [MVP]]

I did some more testing, and it seems that it can be done on one line:

Me.txtCounter = "Record " & Me.CurrentRecord & " of " &
Me.RecordsetClone.RecordCount

Again, I have not done much testing with this. I would be interested in
hearing from others with more experience with this topic.

That works, as does AbsolutePosition. So:

If Me.NewRecord Then
Me!txtCounter = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me!txtCounter = "Record " & .AbsolutePosition + 1 & " of " &
..RecordCount
End With
End If
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[BruceM]

Thanks. When I was getting started I often took the code I was offered
verbatim. If it worked I asked no questions. Now that I am acquiring some
experience I look back on some of my earlier efforts and wondering why I did
something a particular way. I appreciate the response and the new
information.

 

[Arvin Meyer]

Thanks. When I was getting started I often took the code I was offered
verbatim. If it worked I asked no questions. Now that I am acquiring some
experience I look back on some of my earlier efforts and wondering why I did
something a particular way. I appreciate the response and the new
information.

As with much of the way Access (and all of Windows, for that matter) works,
there is often many ways to accomplish a task. I use a variation of the code
I posted in a standard module so that it can be called from all forms:

Public Function RecXofY(frmName As Form, strRecordName As String) As String

Dim rst As DAO.Recordset
Set rst = frmName.RecordsetClone
On Error Resume Next

rst.Bookmark = frmName.Bookmark

If Err Then
RecXofY = "New " & strRecordName
Else
RecXofY = strRecordName & " " & rst.AbsolutePosition + 1 & " of " &
rst.RecordCount
End If

rst.Close
Set rst = Nothing

End Function

As a slightly different method, but very similar to the one you offered,
this one can be used directly in the property sheet:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
Count(*))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[BruceM]

Thanks again. I have a few questions, if I may. I have a way to go with my
understanding of VBA, procedures, modules, subs, functions, etc. I know
that I can place a general procedure within a form and call it from
elsewhere within the form (Call MyProcedure). I take it that a standard
module works similarly, except that it is called by a different method (Call
Modules!MyModule!IndexNumber, or something like that). The thing I can't
quite figure from Help is the index number thing. I would expect to call
the procedure by name. Maybe the index number would be clear if I was to
actually create a standard module, but for now I have bounced around in Help
without getting a clear picture. I believe that a public procedure within a
form can be called from another form in that database, but I don't know if
that form needs to be open. I can see that one clear advantage of a
standard module is that it can be exported to another database. That could
save a lot of time. I could make a kind of library database.
Back to your code, I can't quite figure out where you defined strRecordName
and frmName. I see that they are mentioned along with the name of the
function. Are they part of the built-in VBA vocabulary? In the
concatenated string: RecXofY = "New " & strRecordName, what will appear in
place of strRecordName?
My best guess is that the function returns a string, and that RecXofY refers
to the function itself. Calling the function from a form calls one of the
two resulting text strings. Am I anywhere near being on track? Finally,
how does a sub differ from a function? I know this is a lot of questions.
Any guidance you have time to offer would be very much appreciated.

Thanks. When I was getting started I often took the code I was offered
verbatim. If it worked I asked no questions. Now that I am acquiring some
experience I look back on some of my earlier efforts and wondering why I did
something a particular way. I appreciate the response and the new
information.

As with much of the way Access (and all of Windows, for that matter)
works,
there is often many ways to accomplish a task. I use a variation of the
code
I posted in a standard module so that it can be called from all forms:

Public Function RecXofY(frmName As Form, strRecordName As String) As
String

Dim rst As DAO.Recordset
Set rst = frmName.RecordsetClone
On Error Resume Next

rst.Bookmark = frmName.Bookmark

If Err Then
RecXofY = "New " & strRecordName
Else
RecXofY = strRecordName & " " & rst.AbsolutePosition + 1 & " of " &
rst.RecordCount
End If

rst.Close
Set rst = Nothing

End Function

As a slightly different method, but very similar to the one you offered,
this one can be used directly in the property sheet:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
Count(*))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

strRecordName and frmName are dim'd as arguments, i.e. they're fed in the
function name, so that you can pass values to them. So, now I can use a
label to display:

Me.lblRecordXofY.Caption = RecXofY(Me, "Client")
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Thanks again. I have a few questions, if I may. I have a way to go with my
understanding of VBA, procedures, modules, subs, functions, etc. I know
that I can place a general procedure within a form and call it from
elsewhere within the form (Call MyProcedure). I take it that a standard
module works similarly, except that it is called by a different method (Call
Modules!MyModule!IndexNumber, or something like that). The thing I can't
quite figure from Help is the index number thing. I would expect to call
the procedure by name. Maybe the index number would be clear if I was to
actually create a standard module, but for now I have bounced around in Help
without getting a clear picture. I believe that a public procedure within a
form can be called from another form in that database, but I don't know if
that form needs to be open. I can see that one clear advantage of a
standard module is that it can be exported to another database. That could
save a lot of time. I could make a kind of library database.
Back to your code, I can't quite figure out where you defined strRecordName
and frmName. I see that they are mentioned along with the name of the
function. Are they part of the built-in VBA vocabulary? In the
concatenated string: RecXofY = "New " & strRecordName, what will appear in
place of strRecordName?
My best guess is that the function returns a string, and that RecXofY refers
to the function itself. Calling the function from a form calls one of the
two resulting text strings. Am I anywhere near being on track? Finally,
how does a sub differ from a function? I know this is a lot of questions.
Any guidance you have time to offer would be very much appreciated.

Thanks. When I was getting started I often took the code I was offered
verbatim. If it worked I asked no questions. Now that I am acquiring some
experience I look back on some of my earlier efforts and wondering why

I
did

something a particular way. I appreciate the response and the new
information.

As with much of the way Access (and all of Windows, for that matter)
works,
there is often many ways to accomplish a task. I use a variation of the
code
I posted in a standard module so that it can be called from all forms:

Public Function RecXofY(frmName As Form, strRecordName As String) As
String

Dim rst As DAO.Recordset
Set rst = frmName.RecordsetClone
On Error Resume Next

rst.Bookmark = frmName.Bookmark

If Err Then
RecXofY = "New " & strRecordName
Else
RecXofY = strRecordName & " " & rst.AbsolutePosition + 1 & " of " &
rst.RecordCount
End If

rst.Close
Set rst = Nothing

End Function

As a slightly different method, but very similar to the one you offered,
this one can be used directly in the property sheet:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
Count(*))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[BruceM]

Thanks again. Those parentheses after the function name are starting to
come into focus. I appreciate the help.

strRecordName and frmName are dim'd as arguments, i.e. they're fed in the
function name, so that you can pass values to them. So, now I can use a
label to display:

Me.lblRecordXofY.Caption = RecXofY(Me, "Client")
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Thanks again. I have a few questions, if I may. I have a way to go with my
understanding of VBA, procedures, modules, subs, functions, etc. I know
that I can place a general procedure within a form and call it from
elsewhere within the form (Call MyProcedure). I take it that a standard
module works similarly, except that it is called by a different method (Call
Modules!MyModule!IndexNumber, or something like that). The thing I can't
quite figure from Help is the index number thing. I would expect to call
the procedure by name. Maybe the index number would be clear if I was to
actually create a standard module, but for now I have bounced around in Help
without getting a clear picture. I believe that a public procedure
within a
form can be called from another form in that database, but I don't know
if
that form needs to be open. I can see that one clear advantage of a
standard module is that it can be exported to another database. That could
save a lot of time. I could make a kind of library database.
Back to your code, I can't quite figure out where you defined strRecordName
and frmName. I see that they are mentioned along with the name of the
function. Are they part of the built-in VBA vocabulary? In the
concatenated string: RecXofY = "New " & strRecordName, what will appear in
place of strRecordName?
My best guess is that the function returns a string, and that RecXofY refers
to the function itself. Calling the function from a form calls one of
the
two resulting text strings. Am I anywhere near being on track? Finally,
how does a sub differ from a function? I know this is a lot of
questions.
Any guidance you have time to offer would be very much appreciated.

Thanks. When I was getting started I often took the code I was
offered
verbatim. If it worked I asked no questions. Now that I am acquiring
some
experience I look back on some of my earlier efforts and wondering why I
did
something a particular way. I appreciate the response and the new
information.

As with much of the way Access (and all of Windows, for that matter)
works,
there is often many ways to accomplish a task. I use a variation of the
code
I posted in a standard module so that it can be called from all forms:

Public Function RecXofY(frmName As Form, strRecordName As String) As
String

Dim rst As DAO.Recordset
Set rst = frmName.RecordsetClone
On Error Resume Next

rst.Bookmark = frmName.Bookmark

If Err Then
RecXofY = "New " & strRecordName
Else
RecXofY = strRecordName & " " & rst.AbsolutePosition + 1 & " of
" &
rst.RecordCount
End If

rst.Close
Set rst = Nothing

End Function

As a slightly different method, but very similar to the one you
offered,
this one can be used directly in the property sheet:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
Count(*))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access