DCount

H

Hiking

Hello everyone,

I have a form on which I've removed the record controls. Now if my form
is filtered, I don't see the record count at the bottom any more (e.g.
237 of 1124).

I would like to have a text box somewhere on the form which would read
"582 records displayed out of a total of 3649", or something of the
kind. I've already got a text box displaying the total number of
records (=dcount("[ID]","tblRunningTasks"). Since this table has a ID
field done by Auto-Number, counting the ID field always gives me the
total, whether I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm filtering,
as opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
V

Van T. Dinh

Try using:

= [Form].Recorset.RecordCount

or simply:

= Recordset.RecordCount
 
J

JK

This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK
 
R

Richard

Thanks JK, but it seems I don't know where to put this code. I put the code:

=DCount("[ID]","tblRunningTasks")

in the Control Source property of the text box on my form (right-click on
the text box>Properties>Control Source), and that works fine, the total
number of records in my database shows on the form, but the code you gave me
won't work from the same location (in fact, it won't even save the code in
there).

I'm certain it needs to be inserted in the VB code, but under what/which
Private Sub, On Current maybe? As you can see, I have limited VB knowledge,
sorry, and thanks as well.

Richard


JK said:
This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK


Hiking said:
Hello everyone,

I have a form on which I've removed the record controls. Now if my form
is filtered, I don't see the record count at the bottom any more (e.g. 237
of 1124).

I would like to have a text box somewhere on the form which would read
"582 records displayed out of a total of 3649", or something of the kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field done
by Auto-Number, counting the ID field always gives me the total, whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm filtering, as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
R

Richard

Ok, sorry, my mistake...

Getting closer, when I input:

=Dcount("[ID]","tblRunningTasks",&
iif(Forms![frmRunningTasks].FilterOn,Forms![frmRunningTasks].Filter,""))

I get error:

The expression you entered contains invalid syntax. You may have entered a
comma without a preceding value or identifier.

I'll spend another hour or so playing with the syntax, reading up on the
"Syntax of iif"...

Richard

Richard said:
Thanks JK, but it seems I don't know where to put this code. I put the code:

=DCount("[ID]","tblRunningTasks")

in the Control Source property of the text box on my form (right-click on
the text box>Properties>Control Source), and that works fine, the total
number of records in my database shows on the form, but the code you gave me
won't work from the same location (in fact, it won't even save the code in
there).

I'm certain it needs to be inserted in the VB code, but under what/which
Private Sub, On Current maybe? As you can see, I have limited VB knowledge,
sorry, and thanks as well.

Richard


JK said:
This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK


Hiking said:
Hello everyone,

I have a form on which I've removed the record controls. Now if my form
is filtered, I don't see the record count at the bottom any more (e.g. 237
of 1124).

I would like to have a text box somewhere on the form which would read
"582 records displayed out of a total of 3649", or something of the kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field done
by Auto-Number, counting the ID field always gives me the total, whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm filtering, as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
J

JK

Richard,

This was only of the syntax, if you want to add it into your Text Box, add &
paste the new one *without * the = (equal sign. Make sure that there is a
space on both sides of the & sign


I certainly agree that it is a lot easier in a text box, I was not sure how
you want it.

Here is the VB Code:

Private Sub Form_Current()

Dim numTotal as Long, numVisible as Long

numTotal=Dcount("[ID]",tblRunningTasks")
If Me.FilterOn Then
numVisible=Dcount("[ID]","tblRunningTasks", Me.Filter)
Else
numVisible=numTotal
End If

Me.YrTextBoxName="Record: " & Me.Form.CurrentRecord & " of " _
& numVisible & " visible out of " & numTotal & " Total Records"
'or use your own wording

End Sub

Regards

Richard said:
Thanks JK, but it seems I don't know where to put this code. I put the
code:

=DCount("[ID]","tblRunningTasks")

in the Control Source property of the text box on my form (right-click on
the text box>Properties>Control Source), and that works fine, the total
number of records in my database shows on the form, but the code you gave
me
won't work from the same location (in fact, it won't even save the code in
there).

I'm certain it needs to be inserted in the VB code, but under what/which
Private Sub, On Current maybe? As you can see, I have limited VB
knowledge,
sorry, and thanks as well.

Richard


JK said:
This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK


Hiking said:
Hello everyone,

I have a form on which I've removed the record controls. Now if my
form
is filtered, I don't see the record count at the bottom any more (e.g.
237
of 1124).

I would like to have a text box somewhere on the form which would read
"582 records displayed out of a total of 3649", or something of the
kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field
done
by Auto-Number, counting the ID field always gives me the total,
whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm filtering,
as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
J

JK

Oops sorry,

Remove the & sign

Regards/JK


Richard said:
Ok, sorry, my mistake...

Getting closer, when I input:

=Dcount("[ID]","tblRunningTasks",&
iif(Forms![frmRunningTasks].FilterOn,Forms![frmRunningTasks].Filter,""))

I get error:

The expression you entered contains invalid syntax. You may have entered
a
comma without a preceding value or identifier.

I'll spend another hour or so playing with the syntax, reading up on the
"Syntax of iif"...

Richard

Richard said:
Thanks JK, but it seems I don't know where to put this code. I put the
code:

=DCount("[ID]","tblRunningTasks")

in the Control Source property of the text box on my form (right-click on
the text box>Properties>Control Source), and that works fine, the total
number of records in my database shows on the form, but the code you gave
me
won't work from the same location (in fact, it won't even save the code
in
there).

I'm certain it needs to be inserted in the VB code, but under what/which
Private Sub, On Current maybe? As you can see, I have limited VB
knowledge,
sorry, and thanks as well.

Richard


JK said:
This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK


Hello everyone,

I have a form on which I've removed the record controls. Now if my
form
is filtered, I don't see the record count at the bottom any more
(e.g. 237
of 1124).

I would like to have a text box somewhere on the form which would
read
"582 records displayed out of a total of 3649", or something of the
kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field
done
by Auto-Number, counting the ID field always gives me the total,
whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show
on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm
filtering, as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
J

JK

I meant to say that it is easier using VB code

JK said:
Richard,

This was only of the syntax, if you want to add it into your Text Box, add
& paste the new one *without * the = (equal sign. Make sure that there is
a space on both sides of the & sign


I certainly agree that it is a lot easier in a text box, I was not sure
how you want it.

Here is the VB Code:

Private Sub Form_Current()

Dim numTotal as Long, numVisible as Long

numTotal=Dcount("[ID]",tblRunningTasks")
If Me.FilterOn Then
numVisible=Dcount("[ID]","tblRunningTasks", Me.Filter)
Else
numVisible=numTotal
End If

Me.YrTextBoxName="Record: " & Me.Form.CurrentRecord & " of " _
& numVisible & " visible out of " & numTotal & " Total Records"
'or use your own wording

End Sub

Regards

Richard said:
Thanks JK, but it seems I don't know where to put this code. I put the
code:

=DCount("[ID]","tblRunningTasks")

in the Control Source property of the text box on my form (right-click on
the text box>Properties>Control Source), and that works fine, the total
number of records in my database shows on the form, but the code you gave
me
won't work from the same location (in fact, it won't even save the code
in
there).

I'm certain it needs to be inserted in the VB code, but under what/which
Private Sub, On Current maybe? As you can see, I have limited VB
knowledge,
sorry, and thanks as well.

Richard


JK said:
This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK


Hello everyone,

I have a form on which I've removed the record controls. Now if my
form
is filtered, I don't see the record count at the bottom any more (e.g.
237
of 1124).

I would like to have a text box somewhere on the form which would read
"582 records displayed out of a total of 3649", or something of the
kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field
done
by Auto-Number, counting the ID field always gives me the total,
whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show
on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm filtering,
as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
R

Richard

Thank you Van,

See my reply to JK below, I am not certain where to insert this code.

Ok, never mind, I got it now, thanks.

Van T. Dinh said:
Try using:

= [Form].Recorset.RecordCount

or simply:

= Recordset.RecordCount


--
HTH
Van T. Dinh
MVP (Access)



Hiking said:
Hello everyone,

I have a form on which I've removed the record controls. Now if my form
is filtered, I don't see the record count at the bottom any more (e.g. 237
of 1124).

I would like to have a text box somewhere on the form which would read
"582 records displayed out of a total of 3649", or something of the kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field done
by Auto-Number, counting the ID field always gives me the total, whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm filtering, as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
V

Van T. Dinh

I hope you worked that the expressions I posted are just straight-out
replacement for your Dcount(), i.e. to be placed in the ControlSource for
your TextBox.
 
R

Richard

Thanks all, and JK especially, I have that code working like a charm (I
modified it just a wee bit).

I tried your code, and was getting a error message telling me that "You
can't assign a value to this object" (runtime error -2147352567). When I
searched on this error, I found the following, which was a reply to someone
else's problem with the same error msg:

"Just an idea here as to why you have experienced this particular error:

You have to do this with a BOUND control on your mainform, not the
calculated textbox. - Peter Schroeder "

Though I don't understand much VB and Access code, I did suspect that this
meant that I needed the text box to reference a field in the Control Source
property of the text box. So I created a Memo field in my table (a text
field wouldn't hold the whole message I was displaying in the text box on my
form)
and set that field as the Control Source in the text box properties.

Now the code works like a charm... Thanks!

Not certain whether or not it is absolutely necessary to have the field in
my table in order for this code to work or not, maybe there might have been
another solution to solving the error problem, but in any case, it's working
now.

The actual code I'm using now is as follows:

Private Sub Form_Current()

Dim numTotal As Long, numVisible As Long

numTotal = DCount("[ID]", "tblRunningTasks", "")

If Me.FilterOn Then
numVisible = DCount("[ID]", "tblRunningTasks", Me.Filter)

Me.txtDisplayRecords = "Viewing record: " & Me.Form.CurrentRecord & " of
" _
& numVisible & " filtered records out of " & numTotal & " total records."

Me.Detail.BackColor = "8454143"
Else
numVisible = numTotal

Me.txtDisplayRecords = "Viewing record: " & Me.Form.CurrentRecord & " of
" _
& numTotal & " total records."
End If

End Sub


Great stuff! The secretary is not computer savvy, and sometimes she would
search for a site or sign number but not find it 'cause she didn't realize or
forgot that she had an active filter. This way she can easily see that she's
not searching the whole database. I was going to have the background colour
change when a filter was active, but our background is actually a bitmap. If
I go to the Format tab of my form's properties, I can see that the Picture
property says "(Bitmap)", but when I click on the ellipse button, I get a
"Insert Picture" dialogue box popping up, but I can't determine what bitmap
is actually currently in use. The above code is indeed changing the
background colour, but the bitmap is covering up the background. If I want
the background to change colour on filtering, I would need to get rid of the
bitmap altogether or is it possible to make the bitmap semi-transparent? If
I can find the bitmap, which is probably located within my Office install
directory, I could then load that bitmap into a graphics program to create a
second such bitmap in a different colour. Then I will just change the bitmap
depending on the filtering.

Or again, maybe you have an even better idea...

Thanks again JK.


JK said:
Richard,

This was only of the syntax, if you want to add it into your Text Box, add &
paste the new one *without * the = (equal sign. Make sure that there is a
space on both sides of the & sign


I certainly agree that it is a lot easier in a text box, I was not sure how
you want it.

Here is the VB Code:

Private Sub Form_Current()

Dim numTotal as Long, numVisible as Long

numTotal=Dcount("[ID]",tblRunningTasks")
If Me.FilterOn Then
numVisible=Dcount("[ID]","tblRunningTasks", Me.Filter)
Else
numVisible=numTotal
End If

Me.YrTextBoxName="Record: " & Me.Form.CurrentRecord & " of " _
& numVisible & " visible out of " & numTotal & " Total Records"
'or use your own wording

End Sub

Regards

Richard said:
Thanks JK, but it seems I don't know where to put this code. I put the
code:

=DCount("[ID]","tblRunningTasks")

in the Control Source property of the text box on my form (right-click on
the text box>Properties>Control Source), and that works fine, the total
number of records in my database shows on the form, but the code you gave
me
won't work from the same location (in fact, it won't even save the code in
there).

I'm certain it needs to be inserted in the VB code, but under what/which
Private Sub, On Current maybe? As you can see, I have limited VB
knowledge,
sorry, and thanks as well.

Richard


JK said:
This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK


Hello everyone,

I have a form on which I've removed the record controls. Now if my
form
is filtered, I don't see the record count at the bottom any more (e.g.
237
of 1124).

I would like to have a text box somewhere on the form which would read
"582 records displayed out of a total of 3649", or something of the
kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field
done
by Auto-Number, counting the ID field always gives me the total,
whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm filtering,
as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 
R

Richard

Sorry, I'm answering/posting these too fast.

Already fixed. I now have only the following code to resolve my initial
problem:



Private Sub Form_Current()

If Me.FilterOn Then

Me.Picture = "D:\Program Files\Microsoft
Office\OFFICE11\BITMAPS\STYLES\StoneYellow.jpg"

Else

Me.Picture = "D:\Program Files\Microsoft
Office\OFFICE11\BITMAPS\STYLES\STONE.BMP"

End If

End Sub


The previous code to count the records I'm going to keep handy as reference
for other purposes, and in case I do want to be counting records later, but
for now, changing the colour to a desert-sand colour ( sand filters, right
;-) ), this is good enough to indicate that the records are being filtered.

I found the bitmap where stated in the code, above, loaded the bitmap in MS
Photo PhotoDraw (included with MS Office), set a background colour of Yellow
to the loaded bitmap, and made the bitmap semi-transparent, and finally saved
it as a .JPG and referenced the two bitmaps as shown in the code above.

Now when I'm filtering, my Details screen turns a sandish-coloured yellow.
Just adding all this info for others who stumble upon this info and find it
useful, who are trying to add similar functionality to their database.

Muchas Gracias again! Vamos!
 
J

JK

Richard,
Glad to be of help.
Sorry, my fault, I did omit to tell you to clear the Text Box.

JK


Richard said:
Thanks all, and JK especially, I have that code working like a charm (I
modified it just a wee bit).

I tried your code, and was getting a error message telling me that "You
can't assign a value to this object" (runtime error -2147352567). When I
searched on this error, I found the following, which was a reply to
someone
else's problem with the same error msg:

"Just an idea here as to why you have experienced this particular error:

You have to do this with a BOUND control on your mainform, not the
calculated textbox. - Peter Schroeder "

Though I don't understand much VB and Access code, I did suspect that this
meant that I needed the text box to reference a field in the Control
Source
property of the text box. So I created a Memo field in my table (a text
field wouldn't hold the whole message I was displaying in the text box on
my
form)
and set that field as the Control Source in the text box properties.

Now the code works like a charm... Thanks!

Not certain whether or not it is absolutely necessary to have the field in
my table in order for this code to work or not, maybe there might have
been
another solution to solving the error problem, but in any case, it's
working
now.

The actual code I'm using now is as follows:

Private Sub Form_Current()

Dim numTotal As Long, numVisible As Long

numTotal = DCount("[ID]", "tblRunningTasks", "")

If Me.FilterOn Then
numVisible = DCount("[ID]", "tblRunningTasks", Me.Filter)

Me.txtDisplayRecords = "Viewing record: " & Me.Form.CurrentRecord & "
of
" _
& numVisible & " filtered records out of " & numTotal & " total
records."

Me.Detail.BackColor = "8454143"
Else
numVisible = numTotal

Me.txtDisplayRecords = "Viewing record: " & Me.Form.CurrentRecord & "
of
" _
& numTotal & " total records."
End If

End Sub


Great stuff! The secretary is not computer savvy, and sometimes she would
search for a site or sign number but not find it 'cause she didn't realize
or
forgot that she had an active filter. This way she can easily see that
she's
not searching the whole database. I was going to have the background
colour
change when a filter was active, but our background is actually a bitmap.
If
I go to the Format tab of my form's properties, I can see that the Picture
property says "(Bitmap)", but when I click on the ellipse button, I get a
"Insert Picture" dialogue box popping up, but I can't determine what
bitmap
is actually currently in use. The above code is indeed changing the
background colour, but the bitmap is covering up the background. If I
want
the background to change colour on filtering, I would need to get rid of
the
bitmap altogether or is it possible to make the bitmap semi-transparent?
If
I can find the bitmap, which is probably located within my Office install
directory, I could then load that bitmap into a graphics program to create
a
second such bitmap in a different colour. Then I will just change the
bitmap
depending on the filtering.

Or again, maybe you have an even better idea...

Thanks again JK.


JK said:
Richard,

This was only of the syntax, if you want to add it into your Text Box,
add &
paste the new one *without * the = (equal sign. Make sure that there is a
space on both sides of the & sign


I certainly agree that it is a lot easier in a text box, I was not sure
how
you want it.

Here is the VB Code:

Private Sub Form_Current()

Dim numTotal as Long, numVisible as Long

numTotal=Dcount("[ID]",tblRunningTasks")
If Me.FilterOn Then
numVisible=Dcount("[ID]","tblRunningTasks", Me.Filter)
Else
numVisible=numTotal
End If

Me.YrTextBoxName="Record: " & Me.Form.CurrentRecord & " of " _
& numVisible & " visible out of " & numTotal & " Total Records"
'or use your own wording

End Sub

Regards

Richard said:
Thanks JK, but it seems I don't know where to put this code. I put the
code:

=DCount("[ID]","tblRunningTasks")

in the Control Source property of the text box on my form (right-click
on
the text box>Properties>Control Source), and that works fine, the total
number of records in my database shows on the form, but the code you
gave
me
won't work from the same location (in fact, it won't even save the code
in
there).

I'm certain it needs to be inserted in the VB code, but under
what/which
Private Sub, On Current maybe? As you can see, I have limited VB
knowledge,
sorry, and thanks as well.

Richard


:

This will count the filtered records, if any

=Dcount("[ID]","tblRunningTasks", &
iif(Forms![YourForm].FilterOn,Forms![YourForm].Filter,""))

Regards/JK


Hello everyone,

I have a form on which I've removed the record controls. Now if my
form
is filtered, I don't see the record count at the bottom any more
(e.g.
237
of 1124).

I would like to have a text box somewhere on the form which would
read
"582 records displayed out of a total of 3649", or something of the
kind.
I've already got a text box displaying the total number of records
(=dcount("[ID]","tblRunningTasks"). Since this table has a ID field
done
by Auto-Number, counting the ID field always gives me the total,
whether
I'm filtering the data displayed on the form or not.

Now I only need to figure out how to count how many records can show
on
the form when I'm filtering.

Alternately, I might have the screen colour change when I'm
filtering,
as
opposed to the default colour when there's no filter being applied.

How would I count the number of unfiltered records?

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top