Exporting an *updated* calculated result from a form to a report

[teresamichele]

I'm using Access 2007 and having a bit of a problem.

I have a form with a field for due date. I have an After Update event assigned to that to assign a priority code based on the distance from the current date to the due date. The priority code then appears in another box on the form.

The problem is that, of course, that priority code will change as the date gets closer to the due date but the reports I run will not automatically update the priority code if I simply link the result into the field on the report.

I've tried both using an IIf function as the control source for the Priority code on the report and using a piece of VBA "on load" for the report as a whole. Neither work, though, and I'm getting annoyed!

The VBA code I tried to use is:

Private Sub Report_Load()
Me.Priority = IIf([Me.FormEntry.DatePriority] < 30, "A", IIf([Me.FormEntry.DatePriority] < 60, "B", IIf([Me.FormEntry.DatePriority] < 90, "C", "D")))
End Sub

To which I get "Run-time error '2465': Microsoft Office Access can't find the field '|' refererred to in your expression. - but I have no idea what it's talking about.

I'm sure this is a user error but can someone help me?

 

[John Big Booty]

Your structure is wrong for VBA try;

Private Sub Report_Load()

If Me.DatePriority < 30 Then 
     Me.Priority = "A"
ElseIf Me.DatePriority < 60 Then
     Me.Priority = "B"
ElseIf Me.DatePriority < 90 Then
     Me.Priority = "C"
Else
     Me.Priority = "D"   
EndIf 

End Sub

This would work on a from but will not work in a Report as reports do not have an On Load event.

You will need to use;

IIf([Forms!FormEntry!DatePriority] < 30, "A", IIf([Forms!FormEntry!DatePriority] < 60, "B", IIf([Forms!FormEntry!DatePriority] < 90, "C", "D")))

In the query that is populating your report, paste it into the top row of a new column in the design grid, you will notice that Access will automagically assign it a name like Expr1: which you can change to Priority:.

 

[boblarson]

This would work on a from but will not work in a Report as reports do not have an On Load event.

Hey P - Just an FYI for you - Reports in 2007 and above DO have an On Load event.

 

[John Big Booty]

Hey P - Just an FYI for you - Reports in 2007 and above DO have an On Load event.

I guess that first piece of code, I presented, would work in '07 then?

 

[boblarson]

I guess that first piece of code, I presented, would work in '07 then?

No, not likely.

For things like this you want to put it on the FORMAT event of the section involved. THEN it will work.

 

[John Big Booty]

Just as easy to use the second solution and do it in the query, then?

 

[boblarson]

Just as easy to use the second solution and do it in the query, then?

Yes, that it would be.