Results 1 to 9 of 9
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Lagging and halting

    Some queries may have one expression or calculation and lagging may occur. Other queries have many expressions and lots of records, but no lagging. Mostly the more expressions the more challenges.



    Those challenges were solved many moons ago. For the 5 main documents in our system we created a GENERATE FORM(mirror image of the original document, i.e. Sales Invoice)which do multiple calculations and expressions. The generate table and queries never store more than one record. As soon as the user completes the document and approve of it he clicks on a save button. The button uses an append query to save the Invoice to an invoice table. Absolutely no calculations or expressions are found in those queries. No lagging or halting no matter the quantity of records.

    The "Generate form" uses its own queries because it is filled with expressions. The need to edit existing records means that there is an exact same form we call the "Invoice Edit form" or "Payment Edit Form" etc. The Edit forms use the queries with no expressions or calculations.

    Users does not realize there are two different forms and it works perfect. This choice did bring a few other challenges and we would prefer to change back to having one Invoice form for generating and Editing historical invoices.

    My question. If we have one Invoice form, and only VBA does the calculations, would lagging and halting still be a problem. What other options are used. I assume few programmers use two form like we do.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Are you saving any calculated data?

    Maybe do calculations in textboxes instead of query or VBA?

    Use 1 form and code changes the RecordSource sql depending on the mode - "Edit" or "Generate"?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    No, I don't save calculations, but I do append data that was calculated in the "Generate queries" to a table where there is no chance of any form of calculation or expression. Hope we understand one another correct.

    Your second line already helped me to see an option I haven't thought of yet. If a calculated field causes real trouble in the query(I want to stay paranoid about using no expressions or calculations in the queries that store 1000's of records), rather calculate in a textbox on the form. It is not stored in the query though and that field may be needed for reports, but then calculate it again on the report.

    It is our clear experience that calculations and expressions cause lagging and halting in queries, we stay away from that. As said, the current process works perfect. But I want to learn if possible there is an option not using two forms but one. Not using the "Generate invoice" form. The "Edit Invoice" also generates the invoice.

    The clear question I try to ask. Am I right if I assume that VBA can be used in a way that it do the calculations for only the record that is on the form at that moment. The VBA does not requery a query with 10's of thousands of records and then cause lagging and hanging. Talking about requery, is requery again going to increase lagging if we have one form. If you understood my first explanation. Does that seem a good solution to you?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What are you doing with these VBA calcs if not saving to table?

    I think you are asking if form is set for Single, do all records get calculated for VBA or textbox expressions or just the current record? Keep in mind, if form has a RecordSource, all records will reflect the same values for the UNBOUND textboxes. This can be seen when form is set for Datasheet or Continuous. Expressions in textboxes that utilize domain aggregate functions on large datasets can show definite delay. Suggest you do a test and see if there is lag with your expressions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    You stay in "The Great Land"? Here in South Africa it is 09H14 now. Ruben came here this morning and we chat about it. We feel that we got it now. Still a little unsure.
    Replying to you.
    a. We have not used VBA calculations yet. For other purposes, but not on our 5 main documents what this post is about.
    b. Our 5 documents each use 4 tables, 4 queries, 4 forms, 4 reports=80 objects. Will half that to 40 when fixed.
    c. We use append queries. Transactions are created in Generate forms. Generate forms have their own "generate queries" and tables. Opening the generate form it clears the generate queries and tables, only ever one record in the generate objects. As you know, one can append from a calculated field but not to a calculated field.
    d. We strongly decided not to come close to expressions or calculations in the edit tables or queries, and it work well, no delay.
    e. One downside here is when a document needs to be adjusted or corrected, it must be done manually on the Edit form and we forfeit accuracy and complicate it a little for the user. Don't want that.

    After conversing with you, we may settle for the following.
    i. Reduce the 80 objects to 40.
    ii. No Generate tables, queries or forms. Just one each as "normal". No calculations or expressions in the queries or tables.
    iii. Multiple calculations will be needed, but we will do them in text boxes on forms and reports.
    iv. I say Unsure, because we doubt if one calculation in a textbox on a form may cause an unwanted delay. Maybe not, have to experience it, or your experience can tell me. I assume right now and hope that if we go for choice of using VBA only, there wont be delays, if that is true we thought it to be the best in any case. To have no unbound text boxes on the form. Just open fields in the queries and VBA save the calculations when the record is created.
    v. If it does cause a delay we now believe to only use VBA. VBA should recalculate when we fix or adjust previously posted transactions, and of course generate a new transaction. We have a policy of course, if the program can calculate anything, it must.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    I have read your posts several times and I am curious about a few things.

    1) Your post title is "Lagging and halting". Would you explain this?

    2) You say "Users". How many users are in the database at one time?

    3) Where is the database located? For multiple users it must be on a network?

    4) Is the database one file or is it split? If it is split, how do the users open the database?


    It is hard to envision the structure/forms from your description. Is there any chance you would post the dB?
    Make a copy, delete the data except a couple of records, change any sensitive data, do a compact and repair, then zip (compress) it. (See June7's tag line...)

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you. Our business name is Perfac Consulting. We do business consulting, accounting, programming and we soon will start selling our database on a sizable scale, only two enterprises are using our database now. My partner Ruben, my son, is 21 has college qualifications in programming. I am an accountant and for 30 years owned 3 businesses where 1000 people worked. My deceased wife was also a programmer, and my other son 26 as well, but he stays in New Zealand now. We recently joined a business with outstanding facilities. Call center, conference rooms great electronic equipment. Plans are to go big. Invite 20 or more businesses per day to 3 separate sessions, and show them what we can do for them. I believe our development is already more dynamic than all the well-known systems available. The 2 businesses using the program now are not big, but they do have a couple of workstations connected to a server. Our intentions is to keep on installing our program on local server at our customers premises and support them through other linking software. Our wish is to use a host and that customers will all be connected to the host. Until our experience is competent we will not do that.
    To answer you.
    1. Since 4 years ago when I started learning Access, the experience (many people on forums informed me as well) was that some expressions cause lagging, delaying or even halting of queries, even with few records. Some queries has multiple expressions with no problems. Subsequently that was solved as I described in previous post in this thread. The program works well right now. As our experience picked up through the months we realized that even though our program as is, works well from our users point of view, it is much more complicated than what it can be from a programmers point of view.
    2. Currently with the two businesses not more than 3 users will be in the program at the same time, but we expect that large businesses will use our system, and even a 100 users or more may want to enter the same database at the same time.
    3. The database is split.

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    We do have a version that has almost no records, the records are fake in any case. It is a little too big to post here. I will downsize it an post it soon.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perfac,
    To reduce the size of the db for posting (as June suggested):
    -do compact and repair
    -create a zip file.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-28-2018, 08:20 AM
  2. Halting script with Case Else
    By Ekhart in forum Access
    Replies: 3
    Last Post: 03-30-2017, 07:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums