Updating Access from '97 to 2003 - Getting Report #Errors

[Mune]

Hello forumers!

First post, hoping you guys might be able to help me with this problem. Been searching the internets for info but haven't found anything specific to my situation.

So anyway! I would like to update our Access database from running on Access '97 to 2003. I have done so within a test environment (not the live servers) and all is well, except some reports which worked fine in '97 are now showing #Error in the display fields. I've attached a screenshot of said report (parts blurred for confidentiality).


I'm guessing the problem is in the first field with an #Error (Order Subtotal), since the other fields that also have an #Error reference this field as well. That field contains the following code:

=[Order Details Subform].[Form]![Order Subtotal]

(And no the field name is not the same as the name of the label next to it; I came across that possibility in my searching for a solution)



I am not an Access master and I did not create this database originally, so I'm a bit lost. Looking for any clues as to what might be causing this problem. This report displays fine in '97 and gets these errors in '03, so it has to boil down to some difference between the two versions (or some problem during conversion maybe, although no errors were reported during conversion).

Thanks in advance for any help offered, I appreciate it!

[ItsMe]

Is the form named, "Order Details Subform" open when you open the report?

[Mune]

The form is not locked by other users if that's what your wondering - I'm working on it locally in an isolated test environment. However, the previous windows are open within Access that allowed me to get to that report. If I close them before opening the report, the report does not open. Am I confused as to what you mean?

[ItsMe]

Yes, it seems that you are confused by my question. I asked the question because the control in your report with the expression

=[Order Details Subform].[Form]![Order Subtotal]

depends on the form named, "Order Details Subform".

[Mune]

So as far as it being 'Open' or not, I'm not exactly sure what you mean then, but I'll explain. We have our access database setup with an initial 'switchboard' (menu of sorts) which you then use to get to the forms your looking for. Within the form called 'Orders' (of which Order Details is a subform) is where this report is being generated. So yes I do have the 'Orders' form open within my own Access when that report generates, which is the same workflow as what worked before in Access '97.

[ItsMe]

If the control that you want to reference in your report resides within a subform, you will need to use the fully qualified name. This will include the main form and the subform names.

something like
Forms!Mainform!Subform1.Form!ControlName

Your expression may look like
=Forms![Order]![Order Details Subform].Form.[Order Subtotal]

For reference
http://access.mvps.org/access/forms/frm0031.htm

[Mune]

Alright, sounds like a good lead! Thanks my friend, I will be testing this out shortly.

Sidenote, is this something that changed between the '97 and '03 versions of Access? That's where I've been focusing my research since that is the only part that has changed.

[ItsMe]

I believe the fully qualified name came first. The shortcut Me! or Me. came afterwards. Not sure when the Me shortcut was introduced. I have never seen the use of a fully qualified name work without the collection name ("Forms" in this case) and the name of each parent object within the collection.


Since you are referencing a control from outside the parent object, you will not be able to use the Me shortcut and will need to use the fully qualified name. Bottom line, not sure why your expression was written the way it is.

[Mune]

Hehe, me neither, was made long ago by a gentleman who isn't available anymore. Bottom line - Your suggestion worked! As for why it worked before the change, not sure, but alls good now so I wont complain.

Infinite thanks my friend, I appreciate you taking your time to help me out.

[ItsMe]

Not a problem. FYI, you do not have to explicitly define controls with the Me shortcut or the fully qualified name if the control resides within the parent form you are working with. Kinda wordy but, it is worth a mention.

Also, I always use the Me shortcut or the fully qualified name. It makes it easier to read the VBA. When possible, use the Me shortcut because it will process quicker than the fully qualified name.