A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Faster DCount or Query



 
 
Thread Tools Display Modes
  #1  
Old April 24th, 2010, 12:14 AM posted to microsoft.public.access
PeterM
external usenet poster
 
Posts: 208
Default Faster DCount or Query

I have a listbox on a form in AC2003 that counts the total number of entries
in a table.

Select count(*) from Medication

I execute the requery of the listbox after each record save.

My question is, is it faster/more efficient to continue using the listbox or
simply do a DCount?

Thanks in advance.
  #2  
Old April 24th, 2010, 12:39 AM posted to microsoft.public.access
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Faster DCount or Query

Peter

Remember that forms (and listboxes) are only displaying what Access is
storing underneath in tables.

When you give us "SELECT Count(*) FROM Medication", we have to assume that
"Medication" is the name of a table. Is that correct?

So why are you using DCount() or a listbox or ... to count the number of
rows in a table? What business need are you trying to satisfy with that
number?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"PeterM" wrote in message
...
I have a listbox on a form in AC2003 that counts the total number of
entries
in a table.

Select count(*) from Medication

I execute the requery of the listbox after each record save.

My question is, is it faster/more efficient to continue using the listbox
or
simply do a DCount?

Thanks in advance.



  #3  
Old April 24th, 2010, 01:31 AM posted to microsoft.public.access
PeterM
external usenet poster
 
Posts: 208
Default Faster DCount or Query



"Jeff Boyce" wrote:

Peter

Remember that forms (and listboxes) are only displaying what Access is
storing underneath in tables.

When you give us "SELECT Count(*) FROM Medication", we have to assume that
"Medication" is the name of a table. Is that correct?


yes, Medication is a table


So why are you using DCount() or a listbox or ... to count the number of
rows in a table? What business need are you trying to satisfy with that
number?


I have a listbox on the Medication Maintenance form listing all entries in
the Medication table so that users can click on an entry in the listbox and
go directly to that entry rather than nextrecord or previousrecord actions or
a search function. It's strictly a navigation tool. I use this method for
all maintenance forms where tables can have 100 - 200 records in it. This
way the user can scroll up or down to find the entry in the listbox, click on
that entry and the maintenance form populates with the info for that
Medication so the user can edit it. I use the dcount value in the label of
the listbox to show:

me.MedicationListboxLabel = DCount("*","Medication") & " Total Entries"


More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"PeterM" wrote in message
...
I have a listbox on a form in AC2003 that counts the total number of
entries
in a table.

Select count(*) from Medication

I execute the requery of the listbox after each record save.

My question is, is it faster/more efficient to continue using the listbox
or
simply do a DCount?

Thanks in advance.



.

  #4  
Old April 24th, 2010, 04:11 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Faster DCount or Query

On Fri, 23 Apr 2010 17:31:01 -0700, PeterM
wrote:

I have a listbox on the Medication Maintenance form listing all entries in
the Medication table so that users can click on an entry in the listbox and
go directly to that entry rather than nextrecord or previousrecord actions or
a search function. It's strictly a navigation tool. I use this method for
all maintenance forms where tables can have 100 - 200 records in it. This
way the user can scroll up or down to find the entry in the listbox, click on
that entry and the maintenance form populates with the info for that
Medication so the user can edit it. I use the dcount value in the label of
the listbox to show:

me.MedicationListboxLabel = DCount("*","Medication") & " Total Entries"


Well, the count will have to do a full scan of the table every time, so it
will indeed take time.

One question: why a Listbox which must be scrolled, rather than a Combo Box
with the Autocomplete feature turned on? The user could tab into it, type
"lov", and it would jump right to Lovastatin ('scuse me, I need to go take
mine...)
--

John W. Vinson [MVP]
  #5  
Old April 24th, 2010, 05:16 AM posted to microsoft.public.access
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Faster DCount or Query

Why not just set the control source of your text box to:
=[lboMedications].[ListCount] & " Total Entries"


--
Duane Hookom
MS Access MVP


"PeterM" wrote in message
...


"Jeff Boyce" wrote:

Peter

Remember that forms (and listboxes) are only displaying what Access is
storing underneath in tables.

When you give us "SELECT Count(*) FROM Medication", we have to assume
that
"Medication" is the name of a table. Is that correct?


yes, Medication is a table


So why are you using DCount() or a listbox or ... to count the number of
rows in a table? What business need are you trying to satisfy with that
number?


I have a listbox on the Medication Maintenance form listing all entries in
the Medication table so that users can click on an entry in the listbox
and
go directly to that entry rather than nextrecord or previousrecord actions
or
a search function. It's strictly a navigation tool. I use this method
for
all maintenance forms where tables can have 100 - 200 records in it. This
way the user can scroll up or down to find the entry in the listbox, click
on
that entry and the maintenance form populates with the info for that
Medication so the user can edit it. I use the dcount value in the label
of
the listbox to show:

me.MedicationListboxLabel = DCount("*","Medication") & " Total Entries"


More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"PeterM" wrote in message
...
I have a listbox on a form in AC2003 that counts the total number of
entries
in a table.

Select count(*) from Medication

I execute the requery of the listbox after each record save.

My question is, is it faster/more efficient to continue using the
listbox
or
simply do a DCount?

Thanks in advance.



.

  #6  
Old April 24th, 2010, 01:00 PM posted to microsoft.public.access
Bob Quintal
external usenet poster
 
Posts: 939
Default Faster DCount or Query

=?Utf-8?B?UGV0ZXJN?= wrote in
:



"Jeff Boyce" wrote:

Peter

Remember that forms (and listboxes) are only displaying what
Access is storing underneath in tables.

When you give us "SELECT Count(*) FROM Medication", we have to
assume that "Medication" is the name of a table. Is that
correct?


yes, Medication is a table


So why are you using DCount() or a listbox or ... to count the
number of rows in a table? What business need are you trying to
satisfy with that number?


I have a listbox on the Medication Maintenance form listing all
entries in the Medication table so that users can click on an
entry in the listbox and go directly to that entry rather than
nextrecord or previousrecord actions or a search function. It's
strictly a navigation tool. I use this method for all maintenance
forms where tables can have 100 - 200 records in it. This way the
user can scroll up or down to find the entry in the listbox, click
on that entry and the maintenance form populates with the info for
that Medication so the user can edit it. I use the dcount value
in the label of the listbox to show:

me.MedicationListboxLabel = DCount("*","Medication") & " Total
Entries"

Consider replacing the listbox with a continuous subform. You can
format it to look like your listbox and you have the the row x of y
at the bottom.if you leave navigation on, use
me.recordsetclone.reccount to return your total rows.





More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned in this post. Mention and/or description of a product
or service herein does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is",
with no guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"PeterM" wrote in message
...
I have a listbox on a form in AC2003 that counts the total
number of entries
in a table.

Select count(*) from Medication

I execute the requery of the listbox after each record save.

My question is, is it faster/more efficient to continue using
the listbox or
simply do a DCount?

Thanks in advance.



.


  #7  
Old April 26th, 2010, 04:17 PM posted to microsoft.public.access
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Faster DCount or Query

I vote with John's solution. A combobox takes up less room on the screen,
and does NOT require the user to scroll through a list of up to 200 items.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"PeterM" wrote in message
...


"Jeff Boyce" wrote:

Peter

Remember that forms (and listboxes) are only displaying what Access is
storing underneath in tables.

When you give us "SELECT Count(*) FROM Medication", we have to assume
that
"Medication" is the name of a table. Is that correct?


yes, Medication is a table


So why are you using DCount() or a listbox or ... to count the number of
rows in a table? What business need are you trying to satisfy with that
number?


I have a listbox on the Medication Maintenance form listing all entries in
the Medication table so that users can click on an entry in the listbox
and
go directly to that entry rather than nextrecord or previousrecord actions
or
a search function. It's strictly a navigation tool. I use this method
for
all maintenance forms where tables can have 100 - 200 records in it. This
way the user can scroll up or down to find the entry in the listbox, click
on
that entry and the maintenance form populates with the info for that
Medication so the user can edit it. I use the dcount value in the label
of
the listbox to show:

me.MedicationListboxLabel = DCount("*","Medication") & " Total Entries"


More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"PeterM" wrote in message
...
I have a listbox on a form in AC2003 that counts the total number of
entries
in a table.

Select count(*) from Medication

I execute the requery of the listbox after each record save.

My question is, is it faster/more efficient to continue using the
listbox
or
simply do a DCount?

Thanks in advance.



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.