Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Slow Form

Posted on 2004-04-22
13
Medium Priority
?
308 Views
Last Modified: 2011-03-07
I have a main form. On this for I have a Tab Control, with three tabs.

On each of the tab there is a sub form. All was working well until I put the last one on, it then seemed to slow the whole form down...

The form in question calculates the current usage and cost from a linked table, This table is about 80,000 lines long.

Is there anything that I can do to speed up this process? The wait is about 5 - 10 seconds

Any help welcome...

Thanks
Nev
0
Comment
Question by:NevHolland
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10887334
have you got the indexes set up on the tables/fields that u are using?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10887355
" linked table, This table is about 80,000 lines long."

If you are you loading the entire table ... 80,000 records is going to take a while. Is the underlying query only pulling the fields that are necessary for the form? Are you manipulating the data from this form? If not use a "snapshot", "read only" query.

Do you perform the calculation on all records when you load the form or only as needed in the current event?

Steve
0
 
LVL 2

Author Comment

by:NevHolland
ID: 10887417
The idea is to have the user add parts to a table.

These parts then go through a query that finds the usage and cost etc... This is the data that is then displayed on the form.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 2

Author Comment

by:NevHolland
ID: 10887423
I've indexed all tables, only one missing....

Still takes 6 seconds
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10887460
so subform 1 is where they add parts and subform 3 shows usage and cost?
how are you doing the calculations for usage and cost ... are you figuring it our for whatever part thay are currently looking at or do you calculate it for all records in a query and display the query?
0
 
LVL 2

Author Comment

by:NevHolland
ID: 10887524

PartDetail - Table
PartMaster - Link Table (80,000 Lines)
QryPartMaster - Query (grouping on partmaster to give total usage and cost)
QryPartDetail - Query using PartDetail and QryPartMaster to calculate total savings etc...

The subform is of QryPartDetail (user cannot edit detail)
To add / Edit the user opens a form that displays the parts for the project. When closing it requeries the subform...

I'm I explaining myself?
0
 
LVL 2

Author Comment

by:NevHolland
ID: 10887536
I need to run... I'll pick you comments up tomorrow...

Thanks for your help

Nev
0
 
LVL 39

Accepted Solution

by:
stevbe earned 400 total points
ID: 10891224
QryPartMaster set the query itself to be snapshot no locks, it will do this anyway but it does not hurt to set these properties.

I think you might get better response if you do not bind the QryPartMaster and QryPartDetail to the subforms as it needs to recalculate the query for the entire table each time you requery the subform. I would suggest looking at only displaying the records in the subform for the Parent record on the main form, you could pass the record ID field as a parameter to the query and then requery the subform in the current and after_update events.

Steve
0
 
LVL 2

Author Comment

by:NevHolland
ID: 10897054
Ok did as you suggested however the improvements are only small.

Each project has four states.. Two of these will require these calculations. So...

I have set-up that if one of these two states is selected then the page of the tab control becomes visible. I have also reset the RecordSource to ""

What I want to do now is reset the RS everytime the page is clicked however, I cannot find the command string that works

I'm using

Forms!frmProjectMaster![frmActualPartSavings].Form.RecordSource = "qryProjectDetailPart"
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 10897933
to call subform form mainform do this

Me.<name of subform>.Form.Recordsource = "qryProjectDetailPart"
0
 
LVL 2

Author Comment

by:NevHolland
ID: 10898219
OK I'm getting there....

The only thing that I'm finding difficult to get the recordesouce to load when I press the page button...

At the moment it loads when a combo (on the main form) meets a certain value.

Any Ideas (points increased)

Thanks
Nev
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10901587
use the change event of the tab control itself and the .Value property of the tab control tells you which page was selected ...


Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0.Value
        Case 0
            MsgBox Me.TabCtl0.Pages(0).Name
        Case 1
            MsgBox Me.TabCtl0.Pages(1).Name
    End Select
End Sub

Steve
0
 

Expert Comment

by:Eileen Murphy
ID: 35058413
I had this problem and wound up adding a main subform that was snapshot - and based on the selected record in thid continuous main subform -- had another single record subform display that record as editable. That way only 1 record was available for editing at a time. I can't unbind the form because multiple users need real-time results -- so temp tables won't work either.

I am still seeing degradation of performance intermittently, but it did improve overall performance.

Eileen
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question