Slow-opening Report (2000)

Topic

New Reply

Please select Dicembre(December) and 2003 on the startup form of the attached mdb and open the RCons report. Is there any way to shorten the time it takes(minutes) to display the report?

Reply | Quote

Replies

When I try and run the report I get a message that says that report ConsFermDez does not exist.

Reply | Quote

When I try and run the report I get a message that says that report ConsFermDez does not exist.

Reply | Quote

When the report Rcons is run it calls 14483 the function EnerOm. This function contains Dlookup function and this is a slow function (like all domain functions). If you want more speed, you’ll have to reconsider your function EnerOm.

Reply | Quote

Hi Francois, I’ve tried to cut down on the number of all those calls to the EnerOm function by substituting all those Dlookup functions in the Rcons report text boxes with several subreports, each one having a query as record source and also by pre-calculating the energy by using the EnerOm function only for the time range needed using a query but the report still opens painfully slow.
So I’ve decided to create an Energia field in the G1Dez table that gets updated only when the Lettur text box in the MG1Dez form gets updated(please see attachment).
The problem is that if you, for example, select December 30, 2003 on the calendar and modify the value in the Lettur text box, the Energia text box doesn’t reflect the change.
I thought the following code should take care of that but it doesn’t.
Private Sub LETTUR_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery “query7”
DoCmd.SetWarnings True
‘Me.Refresh
End Sub

Reply | Quote

From what I can see you are running an update query on a record you are editting, this would create a conflict as far as I know.

Why don’t you substitute the following:
Energia = EnerOm(“g1dez”, “dezg1k”, [Giorno], [ORE_MARC])
for:
DoCmd.SetWarnings False
DoCmd.OpenQuery “query7”
DoCmd.SetWarnings True
‘Me.Refresh

Reply | Quote

Hi Pat, thank you.
I’ve done what you’ve advised(please see attachment) but when you modify the value in the Lettur text box(try for example to type, say, 10677.28 for Dec 30, 2003) the Energia text box still doesn’t reflect the change.

Reply | Quote

Take out the Me.Refresh command and try it again

Reply | Quote

I’ve taken it out and typed 10677.28 for Dec 30, 2003 but the Energia text box value remains unchanged. I’d like to point out that if you keep on modifying the value in the Lettur text box, the value in the Energia text box does change but it always reflects the previous, not the current, change in the Lettur text box. That’s why I tried and used the Refresh method to see if the current change in the Lettur text box could be reflected right away in a change in the Energia text box.

Reply | Quote

Why don’t you blank the Energia field and see what happens then.

If it does not change then that’s ok as the command overrides what is in the Energia field each time you enter the AfterUpdate event of the Letture field.

Reply | Quote

Why don’t you blank the Energia field and see what happens then.

If it does not change then that’s ok as the command overrides what is in the Energia field each time you enter the AfterUpdate event of the Letture field.

Reply | Quote

Thank you. I’ve blanked it but still the Energia text box value changes to reflect the previous(not the current) change in the Lettur text box.

Reply | Quote

Giorgio,

Put a breakpoint into that AfterUpdate event so you can be sure it is firing. If you type in a value in LETTUR and don’t move off the field, the AfterUpdate won’t fire and the code won’t be executed.

Reply | Quote

Hi Charlotte
I put a breakpoint into that AfterUpdate event and it does fire. Problem is the Energia text box gets updated if you keep on modifying the value in the Lettur text box but it always reflects the previous, not the current, change in the Lettur text box.
If you select, say, Dec 30, 2003 on the calendar you can see that the Lettur text box shows 10676.28 and the Energia text box is initially empty. If you type, for example 10677.28 in the Lettur text box, the Energia text box displays 1485(which is the Energia field value in the G1Dez table corresponding to the Lettur field value for that day), if you type again the same number in the Lettur text box, the Energia text box now displays 1985, which is the output given by the EnerOm function when the Lettur value is 10677.28. If you want the Energia text box to display again the correct value(1485) for when the Lettur text box value is 10676.28, you have to type that value twice in the Lettur text box.

Reply | Quote

Hi Charlotte
I put a breakpoint into that AfterUpdate event and it does fire. Problem is the Energia text box gets updated if you keep on modifying the value in the Lettur text box but it always reflects the previous, not the current, change in the Lettur text box.
If you select, say, Dec 30, 2003 on the calendar you can see that the Lettur text box shows 10676.28 and the Energia text box is initially empty. If you type, for example 10677.28 in the Lettur text box, the Energia text box displays 1485(which is the Energia field value in the G1Dez table corresponding to the Lettur field value for that day), if you type again the same number in the Lettur text box, the Energia text box now displays 1985, which is the output given by the EnerOm function when the Lettur value is 10677.28. If you want the Energia text box to display again the correct value(1485) for when the Lettur text box value is 10676.28, you have to type that value twice in the Lettur text box.

Reply | Quote

Charlotte, I think the problem lies in the fact the EnerOm function recalls a Lettur value from the G1Dez table and not from the Lettur text box so I changed the line of code:
lett = DLookup(“LETTUR”, tab1, “Giorno=#” & Format(gio, “mm-dd-yyyy”) & “#”)
in the EnerOm function into:
lett = Parent!MG1Dez!LETTUR
but I get the VBA message: Run-time error ‘438’. Object doesn’t support this property or method .
What’s the correct syntax?

Reply | Quote

That would explain it, because the value just entered in the textbox isn’t in the table yet because the record hasn’t been saved. You don’t need a reference to Parent if you’re running this code in the afterupdate of a control on the subform. This should suffice:

lett = Me!LETTUR

Reply | Quote

I’ve tried that but it gives me the following

Reply | Quote

Where is the code running? If it’s in the subform, then Me should be valid. If it’s outside the subform, Parent is invalid. If Me is being seen as an invalid keyword, you may have a corrupted form or database or a broken reference.

Reply | Quote

Microsoft Visual Basic Help says: “The Me keyword can’t appear in a standard module because a standard module doesn’t represent an object.”
The EnerOm function is located in a standard module so I guess it’s outside the subform.
What is the syntax for referencing the Lettur text box in this case?

Reply | Quote

I thought you had this code in the AfterUpdate of the Lettur control. Didn’t you say you had changed the code to NOT use EnerOm? If you want to refer to a control in a procedure in a standard module, the easiest way is to pass the control to the routine. The routine can look at the control and get its value, plus it can also get information about the form that contains the control. If all you’re doing is using a function to return the value of a control so you can set the value of another control on the same subform, it doesn’t really make sense to do it outside the subform.

Reply | Quote

No, the AfterUpdate event of the Lettur text box just calls the EnerOm function. I just changed one line of code within the function.
Thank you so much Charlotte, passing the control to the routine is the piece of advice I was missing.

Reply | Quote

No, the AfterUpdate event of the Lettur text box just calls the EnerOm function. I just changed one line of code within the function.
Thank you so much Charlotte, passing the control to the routine is the piece of advice I was missing.

Reply | Quote

I thought you had this code in the AfterUpdate of the Lettur control. Didn’t you say you had changed the code to NOT use EnerOm? If you want to refer to a control in a procedure in a standard module, the easiest way is to pass the control to the routine. The routine can look at the control and get its value, plus it can also get information about the form that contains the control. If all you’re doing is using a function to return the value of a control so you can set the value of another control on the same subform, it doesn’t really make sense to do it outside the subform.

Reply | Quote

Microsoft Visual Basic Help says: “The Me keyword can’t appear in a standard module because a standard module doesn’t represent an object.”
The EnerOm function is located in a standard module so I guess it’s outside the subform.
What is the syntax for referencing the Lettur text box in this case?

Reply | Quote

Where is the code running? If it’s in the subform, then Me should be valid. If it’s outside the subform, Parent is invalid. If Me is being seen as an invalid keyword, you may have a corrupted form or database or a broken reference.

Reply | Quote

I’ve tried that but it gives me the following

Reply | Quote

That would explain it, because the value just entered in the textbox isn’t in the table yet because the record hasn’t been saved. You don’t need a reference to Parent if you’re running this code in the afterupdate of a control on the subform. This should suffice:

lett = Me!LETTUR

Reply | Quote

Charlotte, I think the problem lies in the fact the EnerOm function recalls a Lettur value from the G1Dez table and not from the Lettur text box so I changed the line of code:
lett = DLookup(“LETTUR”, tab1, “Giorno=#” & Format(gio, “mm-dd-yyyy”) & “#”)
in the EnerOm function into:
lett = Parent!MG1Dez!LETTUR
but I get the VBA message: Run-time error ‘438’. Object doesn’t support this property or method .
What’s the correct syntax?

Reply | Quote

Giorgio,

Put a breakpoint into that AfterUpdate event so you can be sure it is firing. If you type in a value in LETTUR and don’t move off the field, the AfterUpdate won’t fire and the code won’t be executed.

Reply | Quote

Thank you. I’ve blanked it but still the Energia text box value changes to reflect the previous(not the current) change in the Lettur text box.

Reply | Quote

I’ve taken it out and typed 10677.28 for Dec 30, 2003 but the Energia text box value remains unchanged. I’d like to point out that if you keep on modifying the value in the Lettur text box, the value in the Energia text box does change but it always reflects the previous, not the current, change in the Lettur text box. That’s why I tried and used the Refresh method to see if the current change in the Lettur text box could be reflected right away in a change in the Energia text box.

Reply | Quote

Take out the Me.Refresh command and try it again

Reply | Quote

Hi Pat, thank you.
I’ve done what you’ve advised(please see attachment) but when you modify the value in the Lettur text box(try for example to type, say, 10677.28 for Dec 30, 2003) the Energia text box still doesn’t reflect the change.

Reply | Quote

From what I can see you are running an update query on a record you are editting, this would create a conflict as far as I know.

Why don’t you substitute the following:
Energia = EnerOm(“g1dez”, “dezg1k”, [Giorno], [ORE_MARC])
for:
DoCmd.SetWarnings False
DoCmd.OpenQuery “query7”
DoCmd.SetWarnings True
‘Me.Refresh

Reply | Quote

Hi Francois, I’ve tried to cut down on the number of all those calls to the EnerOm function by substituting all those Dlookup functions in the Rcons report text boxes with several subreports, each one having a query as record source and also by pre-calculating the energy by using the EnerOm function only for the time range needed using a query but the report still opens painfully slow.
So I’ve decided to create an Energia field in the G1Dez table that gets updated only when the Lettur text box in the MG1Dez form gets updated(please see attachment).
The problem is that if you, for example, select December 30, 2003 on the calendar and modify the value in the Lettur text box, the Energia text box doesn’t reflect the change.
I thought the following code should take care of that but it doesn’t.
Private Sub LETTUR_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery “query7”
DoCmd.SetWarnings True
‘Me.Refresh
End Sub

Reply | Quote

When the report Rcons is run it calls 14483 the function EnerOm. This function contains Dlookup function and this is a slow function (like all domain functions). If you want more speed, you’ll have to reconsider your function EnerOm.

Reply | Quote

I didn’t download the report, but does it have a “Page 1 of 20 Pages” type of entry? This essentially causes the report to be run twice, the first time internally to get the total number of pages, even before it displays anything. Then it is run again for “real”. This is a killer for really big reports.

An additional thought. What about the report sequencing and criteria selection fields? If you don’t have proper indexes on you tables, the more info the report must suck-in before it can run.

Reply | Quote

Wow, every field in that report is a DLookup statement. That is going to run very very very slow. You should bind your report to a query, which pulls the values for you.

Reply | Quote

Hi Drew, thanks for stopping by.
How should that query be?

Reply | Quote

Honestly can’t tell, I must admit I’m not exactly sure what the query or report is doing, I think it’s because it’s a different language, throwing off my thought pattern!

Reply | Quote

Honestly can’t tell, I must admit I’m not exactly sure what the query or report is doing, I think it’s because it’s a different language, throwing off my thought pattern!

Reply | Quote

Hi Drew, thanks for stopping by.
How should that query be?

Reply | Quote

Wow, every field in that report is a DLookup statement. That is going to run very very very slow. You should bind your report to a query, which pulls the values for you.

Reply | Quote