Speed problem with Form_Load()

[djeeten]

Hi,

I have a serious speed problem when this form frmChangeOrders is loaded. Using this form, users can change the orders they have already made.

This is the relation between the tables:

tblCustomer tblOrders tblOrderlines tblProducts
----------- --------- ------------- -----------
CustomerId OrderId OrderlineId ProductId
CustomerId ProductId
Ordered(Yes/No)
ProductMainGroupId

This the code when the form is loaded:

--------------------------------------------------------
Private Sub Form_Load()
If (DCount("[OrderId]", "tblOrders", "Ordered = False AND Customername = Forms!frmLogon!Customername&quot = 0) Then
DoCmd.Close acForm, "frmChangeOrders"
MsgBox "There are no open orders!"
Else
'Initialize a listbox with the products
Me!cboID.RowSource = "SELECT [tblProducts].[ProductId], " & _
"[tblProducts].[NameProduct], [tblProductes].[Price], " & _
"[tblProducts].[VAT] FROM tblProductMainGroup INNER JOIN " & _
"(tblProductGroup INNER JOIN (tblAnalytical INNER JOIN " & _
"tblProducts ON tblAnalytical.[Analytical code] = " & _
"tblProducts.[Analytical code]) ON tblProductGroup.ProductGroupCode = " & _
"tblAnalytical.ProductGroupNumber) ON tblProductMainGroup.MainGroupId = " & _
"tblProductGroup.MainGroupId WHERE ([tblProductMainGroup].[MainGroupId] =" & Me!ProductMainGroupId & &quot"
End If
End Sub
---------------------------------------------------------

As you can see, there are only 2 main things happening when the form is loaded.

1) With DCount I check if there are open orders (orders that have not been confirmed yet and can still be changed)

2) I initialize a listbox to make sure it only contains the products that are allowed for this order (there are different kinds of orders)


Does anyone have an idea what is slowing down the loading of this form?

Thanks a lot in advance!

dj.

 

[JeremyNYC]

If you skip all this code (put an exit sub in as the first line), does the form load really quickly?

Next, if you comment out the if and just load the list box, how fast is it?

Chopping up your code like this is the way to narrow down which element is clogging up the cogs. Isolate it and come on back with more details

Jeremy

PS: DLookup is pretty dang slow in general. Much better would be to make a recordset and check for "rst.bof and rst.eof"



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Rick39]

Some additional tips:

http://www.fmsinc.com/tpapers/faster/#Forms

 

[djeeten]

Hi,

Skipping the code in Form_Load() does not make the form load faster. However, I started reading the additional tips and found something that was slowing it down a little bit.

As a user logs on correctly, the visible property of the logon form is set to false. That way I can retrieve the name of the user whenever needed in the program (is this the correct way to work??). Now, as a user wants to change an order, the name of the user on the logon form is used in the form's query (only those orders of the user that is logged on should be shown).
This logon form contained a background. I removed it and the loading goes a bit faster.

Still it sometimes takes 3 to 4 seconds tille the form is loaded. I think I know how to make it load faster, but I don't know how to handle it.

I need to find a way to check if the user should be able to change orders, before the form is loaded. Right now, I check this in the form_load() event, and then of course, it takes time to load.

Can anyone tell me how to do this? I'm calling this form frmChangeOrders from a switchboard. I should just be able to execute this code after pressing a button on the switchboard:

If (DCount("[OrderId]", "tblOrders", "Ordered = False AND Customername = Forms!frmLogon!Customername&quot = 0) Then

MsgBox "There are no open orders!"
Else
DoCmd.OpenForm "frmChangeOrders", acNormal, , "Ordered = False And Customername = Forms!frmLogon!Customername AND Year(Orderdate)= Year(Date())", acFormEdit

End If

Thanks a lot in advance,

dj.