Excution Very Slow...

Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Tomorrow, 00:38
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.
 
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 ?
 
is the DB split?
what is the calculation on the Report?
 
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:
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.
 
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 ?
 
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:
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..
 
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.
 
- 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.
 
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?
 
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.
 
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
 
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.
 
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

W
Windows 11 Slowing Down Database
2
Replies
24
Views
3,545
gemma-the-husky
gemma-the-husky
Cliff67
Solved Another Run-time Error '2465'
Replies
8
Views
846
Cliff67
Cliff67
C
Solved Treeview nodelevel
Replies
16
Views
799
MajP
MajP
D
Solved Date is displaying odd behavior
Replies
14
Views
832
Ken Sheridan
Ken Sheridan
S
extremely slow when exporting PDF
Replies
7
Views
1,132
spaLOGICng
S

Users who are viewing this thread

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