Report text field: You can't assign a value to this object

[Lori]

Hi,

I have a report that has a query as its record source. I also have a
Report Menu form, where the user enters dates (in text boxes), selects
the report to be displayed, and the report is opened. The query
selects records based on these dates.

All this has worked fine. Until now, where I have a new requirement.
I need to play with one of the dates that the user enters (by
subtracting 9 days), and use the manipulated date for my report. Since
only one report needs this manipulation, I would prefer to pass the
arguments as I always have, just as the user entered them, and do the
manipulation within my report's code.

However, no matter what I do, the query seems to be picking up the data
just as it was entered on the menu form. I attempted to put text boxes
on my report, make them "visible=false", refer to these text boxes
within the query, and within my VBA, set the text box date to the
manipulated date. At this point, I got the error "You can't assign a
value to this object". I am perplexed, as the text box is unbound. I
had the code in the Report's Open event. I don't know where else I'd
put this code. Anywhere else, it would be too late, as the query would
have already selected its data. And the only place I got it to work
was in the Report Detail event (way too late).

I can always put the code back into the menu, but I really would like
to try to get it to work in the report itself (actually, I already have
gotten it to work through the menu, but it seems convoluted and also I
would like to learn how to work with the text box within the report).

Thanks in advance for any help!
Lori

 

[Duane Hookom]

MHO would be to always place code in an event of the section containing the
control. If the control has no Control Source value then it should not be
too late. I could be missing something...

 

[Lori]

Hi Duane,

ok, so I have the text fields in the Page Header section. I added this
code to the PageHeaderFormat event:
txtRptStartDate.Value = DateAdd("d", 9, gstrStartDate)

I took the VBA code to set up my dates out of the Report Menu. I
reference the txtRptStartDate field from the recordsoruce query.

However, now my report is not working (I am encountering nulls, and
assuming that the query is not reading any records). Isn't setting up
my text field in the PageHeaderFormat event is 'too late' for the
query? When is the query actually performed? I thought it was during
the Report_Open event? But I am unable put this same code there.

I hope this is clear. txtRptStartDate is the date used in my query. I
need to set it to the correct date before the query is actually
performed within my report.

Thanks!
Lori

 

[Duane Hookom]

I'm lost. Why would you not set the control source of your text box to
something like:

=DateAdd("d",9,Forms!frmYourForm!txtRptStartDate)

 

[Lori]

Duane,

Honestly, I did not think of doing that!
I tried it, as per your suggestion, and could not get it to work (the
text box was on a prior menu form, and it didn't work when I referred
to it; and the current object was a report, and referring to its text
box didn't work).

I finally got the whole thing to work, using a workaround with VBA.
Was frustrating because it seemed so simple, but, oh well, it is
working.

Thanks for your help.