Analyse Performance in Database Tools

[LeonS]

I ran the Analyse Performance tool. I had selected "All Objects". There were 2 "?". Both asking to remove modules. One is behind a Report. One is behind a Form. There are no Event Procedures in either. How do I get "behind" the objects to find and delete the offending modules. Thank you.
Second part - Most of the suggestions from the analysis are "Add Open Explicit". What are the consequences of trying to do that?

[Micron]

Can't really comment on the first as it seems there ought to be at least one procedure in each, even if there is no code (based on what you've posted). Methinks it's something we'd have to look at.

As for the 2nd, you would have to manually add Option Explicit where it isn't present in existing modules. Why this is not the default is one of the great Access mysteries. To make it the default in vb editor, go to Tools>Options>editor tab & check "Require variable declaration". It has been said that if you don't use this in every module, you deserve the grief you get, to which I'd agree. When your code is compiled you won't get away with variables that are mis-spelled or just haven't been declared (maybe that's the same thing).

[LeonS]

Micron - thanks for that. I think I have solved the first part. I have tried to "find" the modules but since there are no Event Procedures in either, I did not know where to look. However, I have now spotted the "Optimise" option in the Analyse Performance tool. It seems to have worked as the problems are no longer identified.
Moving on to the Option Explicit, I was afraid you would say that!! As you know, real code is not my strong point, as although I know and recognise some, I do not fully understand it all. I suspect there are many instances in my coding where the full rules have not been applied. There is a lot of "copy and paste". I think there may be no easy option. I may bite the bullet and try with one example and go from there. Thank you for your help. Leon

[Micron]

Keep at it and take heart in the fact that when I started learning vba I had to ask how to raise a message box. Over the last 15 - 20 years, I'd say I've graduated to grade 6 or 7. One day maybe I will reach vba at the high school level.

Copy and paste is a good start for code but if you don't understand it and don't make the effort to do so I'd say it doesn't help advance your vba knowledge.
Try adding Option Explicit at the top of a module and compile it and see what you get.

[LeonS]

Micron - again, thanks. I have run the Analyse Performance again, with a little more "sophistication"! There are now no problems with Reports, Queries or Tables. Hurrah. However with Macros, it suggests converting those. I thought I did this once. And I think the conversion produced a new procedure, rather than deleting or replacing the macro. With Forms it is simply the Option Explicit requirement. Although with 1 form (Contacts) it suggests reducing the number of Controls. Sounds like lots of research!!! Leon

[Micron]

As they said in my younger days,

keep on keepin' on!

[LeonS]

Micron - trouble is that I do not have the "15-20 years"!!! I think I will do the Convert Macros first. To do that, I go into a Form which has Macros and use the control to do the conversion. I then need to check through the Event Procedures to make sure there is no duplicate of Macro and Converted Macro. True ?

[Micron]

Not sure. Except for AutoExec macros I haven't used them since Moses was a toddler. If you convert one to code but it still remains, then I'd have to agree. However, if a control or form had a macro behind it and you converted it, you'd have to tell me if the event property for the control or form then points to the new code or not. One thing I'd say about converted macros is that they often seem to contain a lot of fluff.

[moke123]

As far as Option Explicit goes, That is a definite must as you learn VBA.

Open the VBE and go to Tools, then select Options.



Make sure there is a check mark in the Require Variable Declarations box.
That will automatically add Option Expicit to all your new modules. The existing modules you will have to do manually.

[LeonS]

Moke123 - thanks for that. I would really like to include Option Explicit - but at the moment, I think it would open a can of worms that I am not, currently, capable of coping with. I am going to examine my code. I have printed off about 50 x A4 pages. I have spotted some anomalies already. Even to my unexpert eye. I will persevere! Please do not give up on me!! Leon

[Welshgasman]

Moke123 - thanks for that. I would really like to include Option Explicit - but at the moment, I think it would open a can of worms that I am not, currently, capable of coping with. I am going to examine my code. I have printed off about 50 x A4 pages. I have spotted some anomalies already. Even to my unexpert eye. I will persevere! Please do not give up on me!! Leon

Your can of worm is already open?
Best put the option explicit on ALL modules now, set for new ones as mentioned, then compile and fix any compile errors.

[LeonS]

Welshgasman - Thanks for that, I think! I have just checked the Contacts printed code with the actual Event Procedure behind each button and field. There is definitely code I can delete. Then there is a % of code that may be correct, but no Option Explicit. The rest is all over the place! Given the time it will take me to correct nearly ALL the Event Procedures, I will try one at a time. But I WILL get there!! Leon.

[Welshgasman]

You put the Option Explicit at the top of each report/form/code module just the ONCE.
If you have not misspelt anything incorrectly/used an invalid property, then you are not going to notice any difference?

[moke123]

Leon,
Dont torture yourself. Listen to what Gasman and Micron are telling you.

Second part - Most of the suggestions from the analysis are "Add Open Explicit". What are the consequences of trying to do that?

Moke123 - thanks for that. I would really like to include Option Explicit - but at the moment, I think it would open a can of worms that I am not, currently, capable of coping with. I am going to examine my code. I have printed off about 50 x A4 pages. I have spotted some anomalies already. Even to my unexpert eye. I will persevere! Please do not give up on me!! Leon

The consequences are that Option Explicit will point out your errors for you. You dont have to go looking for them.

When you write a procedure the first thing you should do is compile your code


If it wont compile, then it wont run.
If you dont have option explicit declared, the code may still run with the errors but is destined to fail at some point.
By doing it this way you will save yourself a lot of work and frustration.

[CJ_London]

One is behind a Form. There are no Event Procedures in either. How do I get "behind" the objects to find and delete the offending modules. Thank you.

IT may be you created a procedure then deleted it. Either way, if you are quite sure there is no code in the module you can remove it by changing the form/report 'has module' property to no. This can be found in the 'other' section of form properties