How do I reference a control from an Access report

[Gerilee]

I have searched in the forums and the FAQ's for this answer and can't seem to find what I need so if I've missed something, my apologies. I have the code to calculate age using a date of birth. I want to display it on a report in a textbox named txtage. The report uses a query as it's record source. I am trying to put the code in an event for the report. I've tried the report event of On Activate and the Format event for the section in which the control exists. The control is unbound and it resides in a header section. I tried to reference it using Me.txtage but I get an error telling me that it doesn't recognize it even though it shows in the drop down list when I type Me. in the VBA code. I guess I'm not understanding the report events/controls and when the data is available to me or how to reference it. Appreciate any help. Thanks.

 

[rjoubert]

You can't reference a control on a report unless the report is already open. Another way to do this would be to pass the value via the OpenArgs parameter in the call to DoCmd.OpenReport...

DoCmd.OpenReport "myReport", , , , , CalculatedAgeGoesHere

Then, in the Open Event of your report, you can access the value passed in...

Me.txtAge.Value = OpenArgs

 

[rjoubert]

Oops...one too many commas there...

DoCmd.OpenReport "myReport", , , , CalculatedAgeGoesHere

 

[rjoubert]

Oops again...

Instead of using the Open event of your report (like I erroneously stated above), you can actually set the Control Source property of your text control (in design view of your report) to...

=[OpenArgs]

 

[Gerilee]

rjoubert,

Thanks so much for your quick response. If you would be so kind as to provide me some clarification.

I am opening the report from a command button on a form (the form is used to enter some criteria). The command that is executed when the command button is executed is --

DoCmd.OpenReport reportname, acPreview

where reportname is my report. This command was created by a wizard when I created the command button on the form. So, should I use

DoCmd.OpenReport reportname, acPreview,,,CalcAge

But I haven't calculated the age yet so I don't quite understand what I'm passing. I guess I'm looking at this like a form where I have lots of code behind the form's events and controls. Thanks again for your help.

 

[rjoubert]

Where are you calculating the age? It sounds to me like the age should be calculated before you open the report to display the age.

 

[CaptainD]

Why not place an unbound textbox in the reports detail section, call txtAge and in the Detail_format place the following code

txtAge = DateDiff("yyyy", [Birthdate], Date)

 

[dhookom]

Don't use dateDiff("yyyy",[Birthdate], Date) since it will be a year off on average. The DateDiff() function returns the number of New Years parties between two dates.

A more accurate expression is:
=DateDiff("yyyy", [Bdate], Now())+ Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CaptainD]

Why would it be (and why is it) a "year off on average"?

I ran a test after reading your post. My birth date is 10/07/1957 and my formula returned 49. I expected 48 even though I'm two + weeks out from turning 49

 

[dhookom]

All DateDiff() functions return the number of interval borders crossed. For instance:
DateDiff("yyyy",#1/1/2006#,#12/31/2007#) = 1
and
DateDiff("yyyy",#12/31/2006#,#1/1/2007#) = 1
while
DateDiff("d",#1/1/2006#,#12/31/2007#) = 729
and
DateDiff("yyyy",#12/31/2006#,#1/1/2007#) = 1






Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Gerilee]

Thanks for all of the responses. I'm not using DateDiff as I have my own code. What I am having a problem with is not the actual code but where to place the code because the controls on the report were not being recognized when I was calculating the age from the on format event of the section where the unbound control from the report resides. The source of the report is a query that finds all employees with a birthday month based on the input of the user in the form that has the command button that runs the report.

rjoubert -- I don't understand how I would calculate the age before the report is open because I don't have the dates that I need for the calculation until the query is executed when the report is open. I don't know much about OpenArgs which you suggested so I'm reading about that now before I ask any more questions.

Thanks to all.

 

[rjoubert]

If you are using your own code to calc the age, you could put your code into a function (create code module and put your function there). Then, use the control source of the textbox on your report to use your function.

Code module...

Public Function GetAge(DOB as Date) as Integer
   'Do your calculation

   GetAge = 'the result of your calculation
End Function

Report field's control source...
= GetAge([DOB Field])

 

[dhookom]

Gerilee,
Your calculations should work in the On Format event of the report section as long as your code references bound control names. You can't refer to a field in your report's record source unless it is bound to a control.

As rjoubert and I have both suggested, you should be able to do this all with an expression in a control source. You haven't provided any reasons why this would not work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]