Update unbound control in report based on if statement

[echomania]

Greetings.



I have a an access database used to track a process. For simplicity, let's say it is a three step process and three things are tracked: when the product was received, when it was shipped, and a tracking number. Two are date fields: [ReceivedDate] and [PackageFWD] and one is a text field [TrackingNumber].

I am trying to incorporate a feature into a report whereby I could have an unbound field ([ProcessStep])identify at which point in the process the item is currently at.

Here is what I've tried so far, to no avail:

Private Sub Report_Activate()

If IsNull(ReceivedDate) = True Then
    Me.ProcessStep = "Awaiting Received Date"

ElseIf IsNull(PackageFWD) = True Then
    Me.ProcessStep = "Awaiting PackageFWD"

ElseIf CertificateNumber = "" Then
    Me.ProcessStep = "Awating Certificate Number Assignment"

Else
  'Do Nothing

End If

The report has multiple rows. I have had luck with the updating on the ReceivedDate, but it seems to update the field for all records (including those that have a date in the ReceivedDate field). Seems like it is all or nothing and unable to differentiate between records.

I should reference that the query behind the report uses a Nz() function on the dates to return a 0 value.

Any thoughts/suggestions?

[pbaldy]

Try the detail format event, which fires for every record.

[echomania]

Try the detail format event, which fires for every record.

Thanks, but no luck.

Even removing the If statements didn't work to update the control in the Detail Format event.

I tried simply putting:

    Me.ProcessStep = "Anything"

in the Detail Format event and it didn't update the ProcessStep unbound control at all. The report OnActivate event works, but as mentioned earlier it updates the control for all the records (despite the If statements).

Any other ideas? THanks!

[pbaldy]

Can you post the db here? You want the format event of the section containing the textbox, presumably the detail section.

[qa12dx]

place unbound text in detail, in control source place this;

= iif([ReceivedDate] is null, "Awaiting Received Date", iif([PackageFWD] is null, "Awaiting PackageFWD", iif([CertificateNumber] is null, "Awating Certificate Number Assignment")))

it will show value when 1st critrea is met. meaning, if both 1 and 2nd criterea is true it will show only the 1st value.
when u know if criteria 1&2 is not met, then 3rd will be a certain value, u can just put in the value instead of the un-needed 3rd rule.

[echomania]

Thank you qa12dx!

That did it. I don't know why I was trying to make it any harder than it needed to be by trying to do it with VBA. Much simpler. Works like a charm!

[qa12dx]

forums like these have helped me a lot. glad i could give back