Access 2003 to 2007 Report Problem

Topic

New Reply

Hi there!

I am in the process of upgrading a client’s 2003 db to 2007 and whilst most of it works ok, I have an issue with a report that uses Public variables as part of the output.

2003 of course displays the values stored in the variables, but 2007 displays #Name?
The way the report works is the following code sets the variables from a form where the report is selected to run from:-

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
dtmStartOfWeek = Form_frmWeeklyFeesSelection.txtFeesDate
curAdultGym = Form_frmWeeklyFeesSelection.txtAdultGym
curGymKids = Form_frmWeeklyFeesSelection.txtGymKids
curSpecialNeeds = Form_frmWeeklyFeesSelection.txtSpecialNeeds

lblDate.Caption = Format(dtmStartOfWeek, “dd/mm/yyyy”)

DoCmd.Close acForm, “frmWeeklyFeesSelection”
End Sub

And then fields within the report reference the variables. i.e. txtAdultGym =[curAdultGym]

The lblDate.Caption field does actually display the date, but the variables prefixed “cur” display the #Name? error. The variables are also used in other fields for calculation purposes which accordingly display the same error.

Would appreciate any advice anyone has.

Cheers,

Niven

Reply | Quote

Replies

There apparently was a change in the way Public variables are handled in Access 2007. The Community Content in This MSDN Article appears to confirm it. In your case, you might try declaring the variables as Global, rather than Public and see if that works.

Reply | Quote

And then fields within the report reference the variables. i.e. txtAdultGym =[curAdultGym]

The lblDate.Caption field does actually display the date, but the variables prefixed “cur” display the #Name? error. The variables are also used in other fields for calculation purposes which accordingly display the same error.

Another option is to declare some Public functions, and use them in place of the Public/Global variables, like this:

Public Function getcurAdultGym() as variant (or whatever)
getcurAdultGym = curAdultGym
End function

Reply | Quote

Many thanks your replies Wendell and Mark.

Have gone for the function route!

Cheers,

Niven

Reply | Quote