total number of records

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

Guest

I created the form which displays search results based on the query. I would
like to add custom navigation instead of the default navigation buttons. And
this is working fine. But in addition I would like to add the box showing
total number of results (the number behind of in the default navigation). How
I can do that. I can not figura out how to calculate it.

Thanks for advice.

Tony
 
In the control on the form where you want the total use:-
=DCount("*","MyQuery")
MyQuery is the name of the query being used to display
the form data.
 
Tony,
Try an unbound text control with a ControlSource of... (Use your own
fieldnames)

=[CurrentRecord] & " of " & Count([IDNO]) & " of " &
DCount("[IDNo]","tblAddresses")

If you had 100 records and were at the first 5th record, it would display
5 of 100 of 100
If you filtered the recordset down to 20 records and were on the 3rd
record, then it would display
3 of 20 of 100
hth
Al Camp
 
The fastest way is to read the RecordCount property of the RecordsetClone,
which has already been calculated as you move between records.

For the total count only, just set the control source of a text box to:

=[Form].[RecordsetClone].[RecordCount]

Or you can do something like this in the text box control source:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[Form].[RecordsetClone].[RecordCount])

Which will put the form's record number and total count in the text box.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin,

Thank you for your help. Works perfect, exactly as intended.

Tony

Arvin Meyer said:
The fastest way is to read the RecordCount property of the RecordsetClone,
which has already been calculated as you move between records.

For the total count only, just set the control source of a text box to:

=[Form].[RecordsetClone].[RecordCount]

Or you can do something like this in the text box control source:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[Form].[RecordsetClone].[RecordCount])

Which will put the form's record number and total count in the text box.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Tony said:
I created the form which displays search results based on the query. I would
like to add custom navigation instead of the default navigation buttons. And
this is working fine. But in addition I would like to add the box showing
total number of results (the number behind of in the default navigation). How
I can do that. I can not figura out how to calculate it.

Thanks for advice.

Tony
Click to expand...
Click to expand...
 
Hi ACG,

Thank you for your help but unfortunately I am getting error after entering
this formula into the control. I have replaced MyQuery with the name of the
query I have used but this did not help.
The solution from Arvin did work.

Tony
 
Hi AlCamp

Thank you for your help but I have received an #Error in my form. Solution
from Arvin did work.

Tony

AlCamp said:
Tony,
Try an unbound text control with a ControlSource of... (Use your own
fieldnames)

=[CurrentRecord] & " of " & Count([IDNO]) & " of " &
DCount("[IDNo]","tblAddresses")

If you had 100 records and were at the first 5th record, it would display
5 of 100 of 100
If you filtered the recordset down to 20 records and were on the 3rd
record, then it would display
3 of 20 of 100
hth
Al Camp

Tony said:
I created the form which displays search results based on the query. I
would
like to add custom navigation instead of the default navigation buttons.
And
this is working fine. But in addition I would like to add the box showing
total number of results (the number behind of in the default navigation).
How
I can do that. I can not figura out how to calculate it.

Thanks for advice.

Tony
Click to expand...
Click to expand...
 
Hi,

I am having trouble getting both of these to work
(=[Form].[RecordsetClone].[RecordCount]
&
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[Form].[RecordsetClone].[RecordCount])

I realized that there was a typo in the IF function, but I am still unable
to get either of these to work. I have tried putting the name of my form in
the [Form] brackets and also just using form in the brackets. I am
unfamiliar with recordsetclone and recordcount, so I was wondering if there
was something unique to my database that needs to go there.
When I put in a new text box, right clicked, went to properties, then the
data tab and inputed the formula into the control source, I get #NAME? in the
text box after I run the query.

If it helps, Im using Microsoft Access 2003.

Thanks.

Pat


Arvin Meyer said:
The fastest way is to read the RecordCount property of the RecordsetClone,
which has already been calculated as you move between records.

For the total count only, just set the control source of a text box to:

=[Form].[RecordsetClone].[RecordCount]

Or you can do something like this in the text box control source:

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[Form].[RecordsetClone].[RecordCount])

Which will put the form's record number and total count in the text box.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Tony said:
I created the form which displays search results based on the query. I would
like to add custom navigation instead of the default navigation buttons. And
this is working fine. But in addition I would like to add the box showing
total number of results (the number behind of in the default navigation). How
I can do that. I can not figura out how to calculate it.

Thanks for advice.

Tony
Click to expand...
Click to expand...
 
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[Form].[RecordsetClone].[RecordCount])
Click to expand...

Access doesn't recognize [Form] in this context so it throws a #Name
error .
You don't need to use it as long as the expression is used in a
control on that form.

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])

works for me.
 
I entered this exactly in the control source:
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])
and it still shows #NAME?

fredg said:
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[Form].[RecordsetClone].[RecordCount])
Click to expand...

Access doesn't recognize [Form] in this context so it throws a #Name
error .
You don't need to use it as long as the expression is used in a
control on that form.

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])

works for me.
Click to expand...
 
I entered this exactly in the control source:
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])
and it still shows #NAME?

fredg said:
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[Form].[RecordsetClone].[RecordCount])
Click to expand...

Access doesn't recognize [Form] in this context so it throws a #Name
error .
You don't need to use it as long as the expression is used in a
control on that form.

=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])

works for me.
Click to expand...
Click to expand...

I just copied your above expression and pasted it into an unbound
control on a form. It worked just fine.
New Record
when I navigated to a new record, and
Record 1 of 287
otherwise

1 Make sure the name of this control is not "NewRecord" or
"RecordCount" or "RecordsetClone".

2) If it still gives an error, then break the expression down trying
several different expressions:
NOTE: Change FormName below to whatever the actual name of your form
is.
=IIf([NewRecord],"New Record","Not a New Record")
or try
=IIf(Forms!FormName.NewRecord = True, "New Record","Not a new record")
Do either one of the above display "New Record" or "Not a new record"
appropriately?

If it does then try:
=[CurrentRecord]
or try
=Forms!FormName.[CurrentRecord]
Do either one give the form's current record number?

=[RecordsetClone].[RecordCount]
or try
=Forms!FormName.[RecordSetClone].[RecordCount]
Do either one give the total number of records?

Try re-assembling the expression using whatever of the above
expressions work.

Post back.
 
I entered this exactly in the control source:
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])
and it still shows #NAME?
Click to expand...

This would assume that you have a field named NewRecord.

If you're looking to see if the form is on the new record, try

Iif(Me.NewRecord, ...
 
I entered this exactly in the control source:
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])
and it still shows #NAME?
Click to expand...

This would assume that you have a field named NewRecord.

If you're looking to see if the form is on the new record, try

Iif(Me.NewRecord, ...
Click to expand...

John,
Pat is using this expression in a control's control source, not in
VBA.
The expression I gave him should work. It does for me.
 
I entered this exactly in the control source:
=IIf([NewRecord],"New Record","Record " & [CurrentRecord] & " of " &
[RecordsetClone].[RecordCount])
and it still shows #NAME?
Click to expand...

This would assume that you have a field named NewRecord.

If you're looking to see if the form is on the new record, try

Iif(Me.NewRecord, ...
Click to expand...

John,
Pat is using this expression in a control's control source, not in
VBA.
The expression I gave him should work. It does for me.
Click to expand...

Thanks, Fred - and my apologies! I wasn't aware of the [NewRecord] trick.
 
Both of these worked, respectively displaying not a new record and 1 when I
was on the first record in the result:
=IIf([NewRecord],"New Record","Not a New Record")
and
=[CurrentRecord]

However, I am still unable to get it to display the total number of records
in a query. Neither of these worked:
=[RecordsetClone].[RecordCount]
and
=Forms!FormName.[RecordSetClone].[RecordCount]


Thank you for all your help.



:
 
This worked for me:
="Record " & [CurrentRecord] & " of " & Count([COURT_NAME])
court_name was the primary key

Thanks.

-Pat


Pat said:
Both of these worked, respectively displaying not a new record and 1 when I
was on the first record in the result:
=IIf([NewRecord],"New Record","Not a New Record")
and
=[CurrentRecord]

However, I am still unable to get it to display the total number of records
in a query. Neither of these worked:
=[RecordsetClone].[RecordCount]
and
=Forms!FormName.[RecordSetClone].[RecordCount]


Thank you for all your help.



:

I just copied your above expression and pasted it into an unbound
control on a form. It worked just fine.
New Record
when I navigated to a new record, and
Record 1 of 287
otherwise

1 Make sure the name of this control is not "NewRecord" or
"RecordCount" or "RecordsetClone".

2) If it still gives an error, then break the expression down trying
several different expressions:
NOTE: Change FormName below to whatever the actual name of your form
is.
=IIf([NewRecord],"New Record","Not a New Record")
or try
=IIf(Forms!FormName.NewRecord = True, "New Record","Not a new record")
Do either one of the above display "New Record" or "Not a new record"
appropriately?

If it does then try:
=[CurrentRecord]
or try
=Forms!FormName.[CurrentRecord]
Do either one give the form's current record number?

=[RecordsetClone].[RecordCount]
or try
=Forms!FormName.[RecordSetClone].[RecordCount]
Do either one give the total number of records?

Try re-assembling the expression using whatever of the above
expressions work.

Post back.
Click to expand...
Click to expand...
 
Back
Top