Conditional formating based on user input

[RCurtin]

I have been trying to fine a solution to this all day. I have a master report called:
rptDrawingsRegisterByOrderNum
and a subreport called:
subRrtDrawings.

What I want to do is prompt the user for a date when they open the master report. Then i want to make bold all drawings listed on the subreport where the date they entered is greater than dateReceived (this is on subreport).

I've tried lots of different ways to get around this. First I created a function in a module to assign the date to a textbox in the main report. I'm having trouble referencing the control tho. I'm calling this function in the open event of the report because I only want it called once.

Me.Controls!txtLatestDate.Value = getDate

I get an error message saying I can't assign a value to this object.

In the subform I have put the following line in the conditional formatting of a textbox:

Reports![rptDrawingsRegisterByOrderNum]![txtLatestDate]>[DateReceived]

This does not seem to be working either. (I tested it by putting a static value in the text box above)

I have a feeling that I am definitely overcomplicating this? Any tips on the best way to approach it??

 

[RCurtin]

Could anyone give me any tips??

 

[Rod D]

An alternative approach...

I believe in not using code unless I have to, so...as an alternative...

Create an unbound text box on the report (InputDate for example).
Set its ControlSource to =inputbox("Enter date :")
You can hide it (after testing) by setting its Visible property to False.

That will get the field populated in the report so that you can use it for your conditional formatting.

A tip for when you get unexpected results from conditional formatting, ensure that you set a format for the opposite scenario (i.e. set a different colour for dates less than the InputDate) to see if that triggers.

As the InputBox returns a string, you will have to use the CDate([Inputdate]) function in the condition.

This approach does have the drawback that you cannot do validation or error checking - you would need code to do that.

A possible reason for the subreport not formatting is because it is not fully qualified. You need to change it to:
Reports![rptDrawingsRegisterByOrderNum]!subRptDrawings![txtLatestDate]>[DateReceived]

HTH
Regards
Rod

 

[RCurtin]

Thank you

Thanks Rod D,
This was the line I used in the condition in the end:

[DateReceived]>CDate(Reports![rptDrawingsRegisterByOrderNum]![subRptDrawings]![txtLatestDate])

The only thing was it applied the formatting to all the records - not just the ones that met the condition. Means I got it to refer to the text box on the master form propperly so thanks for that!

I've decided to show the user how to change the date as necessary in the conditional format box. It's just one user that will be doing this anyways and this user will need permissions to modify forms anyways.

Thanks for your help tho.