DCount vs something better?

[shanesullaway via AccessMonster.com]

A2K

I have four unbound controls that I am using to give the user a summary based
off the Task List. An example of one of the controls would be, how many Tasks
matching today's date. I am doing this by using an IIF statement in the
unbound controls Control Source.

Here's the IIF statement:
Code:
------------------------------------------------------------------------------
--

=IIf([newrecord],"",DCount("TaskID","Tasks","EmployeeID=" & [EmpID] & " AND
[DueDate] = #" & Date() & "# AND IsComplete = False") & " Task(s) For Today")

------------------------------------------------------------------------------
--


I read an article today that talked about how DCount slows down a forms
performance and they recommended changing DCounts to your own functions (like
an SQL statement). If this is true I was wondering if someone would convert
the above IIF statement into an SQL statement, so I can see what it would
look like. I should be abl to figure out the other three.

If anyone disagrees with the article I read, please feel free to say so. The
thing that I have noticed is that it does take a couple of seconds for the
DCount to fill the unbound text control. I have about 15 command buttons on
this form and when I tab from one command button to the other, the unbound
controls have a flash to them. (go blank, wait about a second or two an then
fill)

TIA,
Shane

 

[Jeff Boyce]

Can you get the same value(s) with a query, using the same selection
criteria?

Why are you using IIF(), but only evaluating one "side" of the IIF()?

A form with that many command buttons is a bit unusual. Does this design
make it easier or harder for the user to decide what to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[shanesullaway via AccessMonster.com]

Hey Jeff,

Thanks for your reply. The truth on the IIF evaluating is that I copied the
code and then changed it to work for me. I'm not adding records from the
form I'm on so I really don't need the 'NewRecord' side of things.

The cmdButtons. Yes, that would be alot of buttons. They are on a form that
I'm calling frmHomePage. frmHomePage is really nothing more than an
information and navigation form. The cmdButtons themselves I am using on top
of tab images and using them to change the SourceObject of a subForm control.
This shows various lists pertaining to our business.

The four txtBoxes that I am referring to are really nothing more than
communicating to the user the status of how many tasks they have for today,
how many are in the future, how many in the past and how many unread office
mails they have. As far as getting the same value using a query. I guess I
could, but let me ask you, would that get the info faster?

Thanks for your reply,
Shane

Can you get the same value(s) with a query, using the same selection
criteria?

Why are you using IIF(), but only evaluating one "side" of the IIF()?

A form with that many command buttons is a bit unusual. Does this design
make it easier or harder for the user to decide what to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

A2K

[quoted text clipped - 38 lines]

TIA,
Shane

 

[Baz]

A2K

I have four unbound controls that I am using to give the user a summary based
off the Task List. An example of one of the controls would be, how many Tasks
matching today's date. I am doing this by using an IIF statement in the
unbound controls Control Source.

Here's the IIF statement:
Code:
-------------------------------------------------------------------------- ----
--

=IIf([newrecord],"",DCount("TaskID","Tasks","EmployeeID=" & [EmpID] & " AND
[DueDate] = #" & Date() & "# AND IsComplete = False") & " Task(s) For Today")

--------------------------------------------------------------------------

----
--


I read an article today that talked about how DCount slows down a forms
performance and they recommended changing DCounts to your own functions (like
an SQL statement). If this is true I was wondering if someone would convert
the above IIF statement into an SQL statement, so I can see what it would
look like. I should be abl to figure out the other three.

If anyone disagrees with the article I read, please feel free to say so. The
thing that I have noticed is that it does take a couple of seconds for the
DCount to fill the unbound text control. I have about 15 command buttons on
this form and when I tab from one command button to the other, the unbound
controls have a flash to them. (go blank, wait about a second or two an then
fill)

TIA,
Shane

Have a peek at this:

http://www.mvps.org/access/modules/mdl0012.htm

 

[Jeff Boyce]

Typically, a query will run faster than a DCount() function.

You could do a mix/match, with a query that does the summing, then a
DLookup() that gets the value to display in the unbound textbox. Or could
you modify the query you use to populate the form to also get the sums?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hey Jeff,

Thanks for your reply. The truth on the IIF evaluating is that I copied
the
code and then changed it to work for me. I'm not adding records from the
form I'm on so I really don't need the 'NewRecord' side of things.

The cmdButtons. Yes, that would be alot of buttons. They are on a form
that
I'm calling frmHomePage. frmHomePage is really nothing more than an
information and navigation form. The cmdButtons themselves I am using on
top
of tab images and using them to change the SourceObject of a subForm
control.
This shows various lists pertaining to our business.

The four txtBoxes that I am referring to are really nothing more than
communicating to the user the status of how many tasks they have for
today,
how many are in the future, how many in the past and how many unread
office
mails they have. As far as getting the same value using a query. I guess
I
could, but let me ask you, would that get the info faster?

Thanks for your reply,
Shane

Can you get the same value(s) with a query, using the same selection
criteria?

Why are you using IIF(), but only evaluating one "side" of the IIF()?

A form with that many command buttons is a bit unusual. Does this design
make it easier or harder for the user to decide what to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

A2K

[quoted text clipped - 38 lines]

TIA,
Shane

 

[shanesullaway via AccessMonster.com]

Hey Mr. Boyce,

I decided to go with a function and then just call it at certain times when
the user navigates around the form. This has eliminated the blinking and
also seems to keep my "communication" txtBoxes updated good enough. Below is
what I ended up with. This works and "seems" to OK but if there are any
suggestions you may have, then I'm wide open to hearing them.

Thanks for you help and willingness to lend a hand,
Shane

Private Function fTaskCounts()

Dim db As Database
Dim rs As DAO.Recordset
Dim CountOfRecords As Long
Dim SQL As String

SQL = "SELECT Sum(IIf([DueDate] < #" & Date & "#,1,0)) AS
PastTasks, " & _
"Sum(IIf([DueDate] = #" & Date & "#,1,0)) AS PresentTasks,
" & _
"Sum(IIf([DueDate] > #" & Date & "#,1,0)) AS FutureTasks "
& _
"FROM Tasks " & _
"WHERE EmployeeID=" & Forms![frmHomePage]![EmpID] & " AND
IsComplete = False"

Dim intPast As Integer
Dim intPresent As Integer
Dim intFuture As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)
rs.MoveFirst
rs.MoveLast

intPast = rs!PastTasks
intPresent = rs!PresentTasks
intFuture = rs!FutureTasks

rs.Close
Set rs = Nothing

Forms![frmHomePage]![txtTskPast] = intPast & " Task(s) In Past"
Forms![frmHomePage]![txtTaskCount] = intPresent & " Task(s) In Present"
Forms![frmHomePage]![txtTskFuture] = intFuture & " Task(s) In Future"

End Function

Typically, a query will run faster than a DCount() function.

You could do a mix/match, with a query that does the summing, then a
DLookup() that gets the value to display in the unbound textbox. Or could
you modify the query you use to populate the form to also get the sums?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hey Jeff,

[quoted text clipped - 42 lines]

 

[shanesullaway via AccessMonster.com]

Hey Baz,

Thanks for the link. I appreciate it.
Shane

A2K

[quoted text clipped - 29 lines]

TIA,
Shane

Have a peek at this:

http://www.mvps.org/access/modules/mdl0012.htm