Validate Year on Report

[dtaylor]

Hello All,
What I am trying to do is validate the year on opening a report.
The report contains a textbox that has function to determine the current year.

I have a form with all my reports and the user can select to view an old table or the current years. I do not want to create a report for each instance, since I have coded my form to change the report record source based on user selections.

The problem is if the user selects, say 2002, the data is from the 2002 table but the textbox is referring to the current year.

I want to validate the current year. Say the user is viewing 2002 table in 2003, textbox refers to 2003. I want to be able to change the textbox with a user selection to the correct year.

I have been playing with some code, but when I try to update the text box I get an error "Can't assign value to this object'.

Here is the code:
Private Sub Report_Open(Cancel As Integer)
If MsgBox("Is Current Year " & FiscalYear(Now()) & "?", vbYesNo) = vbYes Then
Exit Sub
Else
Me.txtFiscalYear = InputBox("What Year Then?")
End If
End Sub


I am I offbase here? How do you change an object on a report dynamically?

Thanks

Dan

 

[Corticus]

Hi dtaylor,

Firstly,
Me.txtFiscalYear = InputBox("What Year Then?")
should be just
txtfiscalyear =InputBox("What Year Then?")
no need for 'me'.

Secondly, you should have all this data in one table. There's no point in separating each year's data into its own table. That's what querries are for. Sometimes you have no choice, though, so sorry if that's the case.

HTH,

 

[dtaylor]

Thanks for the info corticus...yea i know i should use one table but the data is just too large and most of the users couldn't cope. thats why i have broken it up, it actually has helped in other reports so i will deal with it.

i tried using your suggestion and recieved the same message. any ideas? the report is not locked

thanks

dan

 

[Corticus]

Dtaylor,

txtfiscalyear =InputBox("What Year Then?")
Should work. If you can't assign a value to this textbox you've got something else going on. Check to see if the textbox is in a subform, locked, not enabled or something that is either preventing from assigning a value to it, or you are referring to its location incorrectly.

yea i know i should use one table but the data is just too large and most of the users couldn't cope. thats why i have broken it up, it actually has helped in other reports so i will deal with it.

I still say you really should put this all in one table.
If the data is too large, that's all the more reason to put it in one table. If you don't want your users to see all the years (first of all, you usually don't want users in the actual data tables, I prefer forms), simply make querries that pull a given years data, so they only see that year. Anything you're trying to do with this data should be greatly simplified if you do this.

HTH,