Excution Very Slow... (1 Viewer)

Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Today, 06:00
Joined
Dec 5, 2017
Messages
101
As of my knowledge i written bunch of code at on load / on format events ... But its taking much time to calculate and format. What else i can do to run faster???
Thanks in advance for your suggestion.
 
Minty

Minty

AWF VIP
Local time
Today, 01:30
Joined
Jul 26, 2013
Messages
10,368
That's a very broad question.

What is the record source for your form? how many records?
How many sub forms and how many records in those sub tables?

Where is the backend data saved and how do you connect to it ?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:30
Joined
May 7, 2009
Messages
19,229
is the DB split?
what is the calculation on the Report?
 
Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Today, 06:00
Joined
Dec 5, 2017
Messages
101
yes, It is split db.
here is the code on load event
Code: 
<<
Private Sub Form_Load()
Dim warndate As Date
Dim validdate As Date
Dim trandate As Date
trandate = DLookup("dayenddate", "tbl_dayend", "dayend=true")
warndate = DLookup("paymentdate", "tbl_paid", "active=true")
validdate = DLookup("validdate", "tbl_paid", "active=true")

Dim ctlsales As String
Dim ctlreports As String
Dim ctlinventory As String
Dim ctlpurchase As String
Dim ctlstock As String
ctlsales = DLookup("sales", "logintable", "username='" & txtuser & "'")
ctlreports = DLookup("reports", "logintable", "username='" & txtuser & "'")
ctlpurchase = DLookup("purchase", "logintable", "username='" & txtuser & "'")
ctlstock = DLookup("stock", "logintable", "username='" & txtuser & "'")
ctlinventory = DLookup("inventory", "logintable", "username='" & txtuser & "'")


If ([trandate] > [warndate] And [trandate] < [validdate]) Then
DoCmd.OpenForm "Frm_amc_warn"

If (ctlsales = True) Then
TABMENU.Pages(0).Enabled = True
Else
TABMENU.Pages(0).Enabled = False
End If

If (ctlreports = True) Then
TABMENU.Pages(3).Enabled = True
Else
TABMENU.Pages(3).Enabled = False
End If

If (ctlstock = True) Then
TABMENU.Pages(1).Enabled = True
Else
TABMENU.Pages(1).Enabled = False
End If

If (ctlpurchase = True) Then
TABMENU.Pages(2).Enabled = True
Else
TABMENU.Pages(2).Enabled = False
End If

If (ctlinventory = True) Then
TABMENU.Pages(4).Enabled = True
Else
TABMENU.Pages(4).Enabled = False
End If
txtslmob.SetFocus


ElseIf ([trandate] > [validdate]) Then
MsgBox "Couldn't Open Menu...Contact Developer", vbCritical
TABMENU.Pages(0).Enabled = False
TABMENU.Pages(1).Enabled = False
TABMENU.Pages(2).Enabled = False
TABMENU.Pages(4).Enabled = False
Else


If (ctlsales = True) Then
TABMENU.Pages(0).Enabled = True
Else
TABMENU.Pages(0).Enabled = False
End If

If (ctlreports = True) Then
TABMENU.Pages(3).Enabled = True
Else
TABMENU.Pages(3).Enabled = False
End If

If (ctlstock = True) Then
TABMENU.Pages(1).Enabled = True
Else
TABMENU.Pages(1).Enabled = False
End If

If (ctlpurchase = True) Then
TABMENU.Pages(2).Enabled = True
Else
TABMENU.Pages(2).Enabled = False
End If

If (ctlinventory = True) Then
TABMENU.Pages(4).Enabled = True
Else
TABMENU.Pages(4).Enabled = False
End If
txtslmob.SetFocus

End If
Dim del10 As String
Dim del20 As String
Dim del30 As String
Dim del40 As String
del10 = "delete from temp_sales_item"
del20 = "delete from temp_payment"
del30 = "delete from temp_sale_adv_data"
del40 = "delete from temp_sale_sr_data"
DoCmd.RunSQL del10
DoCmd.RunSQL del20
DoCmd.RunSQL del30
DoCmd.RunSQL del40
Temp_Sales_Item_subform.Requery
Temp_payment_subform11.Requery
Temp_Sale_Adv_Data_subform.Requery
Temp_Sale_Sr_Data_subform.Requery

CMDSLADD.Enabled = False
CMDSLSAVE.Enabled = False

End Sub
>>

Here is the On format event on report
Code: 
<<
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim code As Variant
code = DLookup("statecode", "tbl_client")

If [StateID] = FormatNumber([code], 0) Then
Label313.ForeColor = vbBlack
Label311.ForeColor = vbBlack
Label309.ForeColor = vbWhite
Text294.ForeColor = vbBlack
Text295.ForeColor = vbBlack
Text296.ForeColor = vbWhite

Else
Text296.ForeColor = vbBlack
Label309.ForeColor = vbBlack
Label313.ForeColor = vbWhite
Label311.ForeColor = vbWhite
Text294.ForeColor = vbWhite
Text295.ForeColor = vbWhite
Label340.ForeColor = vbWhite
Label347.ForeColor = vbWhite
cgst_Label.ForeColor = vbWhite
sgst_Label.ForeColor = vbWhite
Me.cgst.ForeColor = vbWhite
Me.cgstval.ForeColor = vbWhite
Me.sgst.ForeColor = vbWhite
Me.sgstval.ForeColor = vbWhite
Label564.Caption = "IGST %"
End If
End Sub
>>
 
Last edited:
Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Today, 06:00
Joined
Dec 5, 2017
Messages
101
Minty said:
That's a very broad question.

What is the record source for your form? how many records?
How many sub forms and how many records in those sub tables?

Where is the backend data saved and how do you connect to it ?
Click to expand...
Record source is null at on open. After opening form with 4 temp Empty subforms which are accessing from same DB. And saving those filled data to another linked DB which is in another drive.. Probably records up to 5-15 for each form while saving . After successful saving report to be opened with format of some elements.
 
Minty

Minty

AWF VIP
Local time
Today, 01:30
Joined
Jul 26, 2013
Messages
10,368
There are two aspects to this, your form load code is fine, but this type of construct

Code: 
        If (ctlreports = True) Then
            TABMENU.Pages(3).Enabled = True
        Else
            TABMENU.Pages(3).Enabled = False
        End If

could be tied up to simply

Code: 
        TABMENU.Pages(3).Enabled = ctlreports
This won't noticeably speed things up but is much neater.

The slow bit is probably the delete from temp tables - who many records?
And the requery of the subforms - How many sub-records

EDIT : Where is the backend stored - and on what Access / SQL / Azure ?
 
D

Dreamweaver

Well-known member
Local time
Today, 01:30
Joined
Nov 28, 2005
Messages
2,466
Reshmi mohankumar said:
ctlsales = DLookup("sales", "logintable", "username='" & txtuser & "'")
ctlreports = DLookup("reports", "logintable", "username='" & txtuser & "'")
ctlpurchase = DLookup("purchase", "logintable", "username='" & txtuser & "'")
ctlstock = DLookup("stock", "logintable", "username='" & txtuser & "'")
ctlinventory = DLookup("inventory", "logintable", "username='" & txtuser & "'")
Click to expand...
I would use a recordset to get these values as they all use the same table that should speed it up somewhat.

Edit:
Use CurrentDb.Execute instead of below don't know if you'll get a speed bump out of it but thats the way I do it.
DoCmd.RunSQL del10
DoCmd.RunSQL del20
DoCmd.RunSQL del30
DoCmd.RunSQL del40

Edit 2
Do you really need to requery all the subforms here?
Temp_Sales_Item_subform.Requery
Temp_payment_subform11.Requery
Temp_Sale_Adv_Data_subform.Requery
Temp_Sale_Sr_Data_subform.Requery

Edit 3
code = DLookup("statecode", "tbl_client")
Do you only have 1 clent record??
 
Last edited:
Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Today, 06:00
Joined
Dec 5, 2017
Messages
101
Acce
Minty said:
There are two aspects to this, your form load code is fine, but this type of construct

Code: 
        If (ctlreports = True) Then
            TABMENU.Pages(3).Enabled = True
        Else
            TABMENU.Pages(3).Enabled = False
        End If

could be tied up to simply

Code: 
        TABMENU.Pages(3).Enabled = ctlreports
This won't noticeably speed things up but is much neater.

The slow bit is probably the delete from temp tables - who many records?
And the requery of the subforms - How many sub-records

EDIT : Where is the backend stored - and on what Access / SQL / Azure ?
Click to expand...

Access is my BackEnd

And i will try Condition method also..
 
Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Today, 06:00
Joined
Dec 5, 2017
Messages
101
MickJav said:
I would use a recordset to get these values as they all use the same table that should speed it up somewhat.

Edit:
Use CurrentDb.Execute instead of below don't know if you'll get a speed bump out of it but thats the way I do it.
DoCmd.RunSQL del10
DoCmd.RunSQL del20
DoCmd.RunSQL del30
DoCmd.RunSQL del40

Edit 2
Do you really need to requery all the subforms here?
Temp_Sales_Item_subform.Requery
Temp_payment_subform11.Requery
Temp_Sale_Adv_Data_subform.Requery
Temp_Sale_Sr_Data_subform.Requery

Edit 3
code = DLookup("statecode", "tbl_client")
Do you only have 1 clent record??
Click to expand...

Edit1: The temp tables have not much data but before it has very boost. now takes up to 8-10 seconds

edit2: Yes, I really need to requery. Bcz there will be #deleted data will apear in subforms
edit3: Yes, it has only 1 record in that table.
 
S

shadow9449

Registered User.
Local time
Yesterday, 20:30
Joined
Mar 5, 2004
Messages
1,037
- If you have a split database, do you have a persistent connection to the back end?
- Have you turned off Subdatasheets?
- Have you turned off Autocorrect?

All of these make a huge difference in speed.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:30
Joined
Feb 19, 2002
Messages
43,233
1. Every domain function runs a separate query so as MickJav suggested, running ONE query instead of FIVE to get the fields from the login table is an improvement. In my apps, I don't close the login form. I have it bound to the user's record. Once the login is correct, I hide the form and open the menu. This allows me to reference the hidden, login form from every form where I need the information. So I never have to read the data from a table again. The most expensive operation we do is to access data so you want to minimize that whenever possible.
2. I question the results of the other dLookup()'s. Are you certain they are each returning only a single row? To find out, create a query and use your dLookup() criteria to see what comes back.
3. Using temp tables leads to bloat. You might not need them at all. We'd need to have a better understanding of what you're doing to offer an alternative but in my apps, I use a template database that contains empty tables. Each time I start an import process, I delete the old copy of the template and copy in a fresh, empty copy. That completely eliminates bloat issues. It also eliminates conflict between users if they are doing the same process at the same time.
 
D

Dreamweaver

Well-known member
Local time
Today, 01:30
Joined
Nov 28, 2005
Messages
2,466
Was reading Pats replay and had a thought in your code you have

Reshmi mohankumar said:
TABMENU.Pages(0).Enabled = False
TABMENU.Pages(1).Enabled = False
TABMENU.Pages(2).Enabled = False
TABMENU.Pages(4).Enabled = False
Click to expand...

Shouldn't this TABMENU.Pages(4).Enabled = False Be This TABMENU.Pages(3).Enabled = False

And also are your suforms on these tab contols I.E. one subform per tab?
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:30
Joined
Feb 19, 2002
Messages
43,233
Always refer to form objects using "Me.". It makes the interpreting process (VBA isn't a compiled language) more efficent since it tells the interpreter in which library the object can be found.

Also, naming your pages rather than referencing them using numbers makes the code easier to understand.

Me.TABMENU.pgMain.Enabled = False
Me.TABMENU.pgClient.Enabled = False
Me.TABMENU.pgReports.Enabled = False
 
Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Today, 06:00
Joined
Dec 5, 2017
Messages
101
MickJav said:
Was reading Pats replay and had a thought in your code you have



Shouldn't this TABMENU.Pages(4).Enabled = False Be This TABMENU.Pages(3).Enabled = False

And also are your suforms on these tab contols I.E. one subform per tab?
Click to expand...
3-4 subforms per tab i have.
 
D

Dreamweaver

Well-known member
Local time
Today, 01:30
Joined
Nov 28, 2005
Messages
2,466
Are the ones you are requerying on any of the tabs not active I.E. not the current page?

And to be honest having that many subforms wil explain why your having speed issues as from my count y will have 12 subforms which is way to many

I just released a template where I used 4 and took steps to make sure it wasn't slowed down.

look at the time and billing template in my signature then look at the code behind the tab controls onchange event
 
Minty

Minty

AWF VIP
Local time
Today, 01:30
Joined
Jul 26, 2013
Messages
10,368
I'm not sure what Micks example does, but to speed things up you should only load the subforms when the tab is selected that they are on.
 
D

Dreamweaver

Well-known member
Local time
Today, 01:30
Joined
Nov 28, 2005
Messages
2,466
Minty said:
I'm not sure what Micks example does, but to speed things up you should only load the subforms when the tab is selected that they are on.
Click to expand...
Hi Minty Thats what Mine does but it takes it a step further as I didn't want to load the subform everytime the tab was clicked so checked the source object for "" as thats it's setting when form loads.

mick
 
You must log in or register to reply here.

Similar threads

P
MS Forms Tree View Slow Loading Issue
2
Replies
21
Views
652
MajP
MajP
A
Solved Error 94 (Invalid use of Null) on a line in a Function used in a database adapted from Northwind 2.0 Developer
2
Replies
26
Views
1,108
jdraw
jdraw
J
Cannot assign value to unbound control on the report footer
Replies
2
Views
254
June7
June7
N
Solved Record set too slow with SQL server Cloud & Parameters not working
Replies
15
Views
401
cheekybuddha
cheekybuddha
kstaelens
Transpose the result of a query and add it to another table
Replies
7
Views
271
kstaelens
kstaelens

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom