Funny behavior reading controls on Subform / Access 2010 / Just want to know.

Version:  Access 2010
I have the below on a form to see the total of the subform.
It works fine on my computer and I've used the similar on forms - reading of subform values on multiple different applications at multiple clients in the past.  The worst that has ever happened - It took 3 to 5 seconds to populate.
I have the appropriate indexes.
The current client (Hospital - can't change anything on the machine) just sits there for a full minute and it doesn't populate.

They have the exact same problem if I try to read from a query
=DSum("[Amount_Owed]","qry_Clients_Invoice","[ClientIdd] = [txt_Id_Number]")

I can solve the problem, my question is mainly has anyone seen this happening and know why?   Is it low memory?   Is it we are close to holloween?  :)

I can solve the problem
I can solve the problem by using a dao recordset - run the code and populate the field.
1) I like just reading the control on the subform because if the user is moving through 30 records, Access doesn't slow things down to figure the value.
2) I always want a better understanding of WHY something happens I didn't expect (Why the above is not populating).  It makes me a better programmer.

Just to repeat - It's a Hospital - can't change anything on the machine - Only changes can be in my database.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Are you using an Access database as the "backend"? Or something else, like SQL Server?

Is this installed on a SINGLE machine at the hospital, or multiple machines?

If multiple, does the same behavior occur on all, or just one (or some)?

What's the ControlSource of txtTot_Depositing on the Subform? Is it bound directly to a table field, or are you running calculations in the ControlSource (like =SomeFunction(), or =DSum(blah blah)).
Dale FyeCommented:
Slow network?

Is this computer hard wired to the network or or Wi-Fi?
LJGAuthor Commented:
   * Don't Know   -  Slow network
   * Yes  -  Computer hard wired
   * Yes - Access database "backend" - (Against my recommendation)
   * Yes - SINGLE machine
   * Control Source txtTot_Depositing
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Just to be clear, is the database properly split?
So in addition to having a single backendDB on a server, does each user has their own copy of the front end of their workstation?

Since you imply that it is for all users, then I would look at the network.
But may things can contribute to "slowness"
For example, if you are dealing with hundreds of thousands of records, this may never be "fast"

<Control Source txtTot_Depositing
           =(nbrRound(DSum("[Amount_Paying]","qry_Inv_Deposit_Form_Sub_Invoices"))) >
Here you are aggregating on a query, if the source query is complex, in and of itself, then the Aggregation will slow it down a bit more.

Also, please clarify if this is the only aspect of this database that is slow?
LJGAuthor Commented:
Answers - #2
   * Yes  - single backendDB on a server,
   * Yes  - user has their own copy of the front end of their workstation
   * No  - hundreds of thousands of records - The application is just starting - Less than 100 records

   * Yes  - This is the only aspect of this database that is slow.  (It's happening on two different forms - but the same concept)

   * Don't Think so  -  query is complex, - qry_Inv_Deposit....    
                - It has 3 IIf statements
                - a few field additions
                - a few places it's reading a field from a
                      form --> [Forms]![frm_Inv_Deposit]![cbo_Client_ID]
Jeffrey CoachmanMIS LiasonCommented:
Then the quick thing to try would be to run the compact repair utility...

I see no need to this one value to take that long to generate...

So lets see if the other experts have any ideas...
LJGAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.