Bring a date value into a form from a report

[vinsavant]

Hello!



I have built an audit trail functionality for a data entry from and I am in need to bring back the most recent change date from a report that tracks all of the changes made to my data entry form. The following explains the different objects that I have.

1. Data Entry Form
2. Table that logs the changes
3. A report that receives its records from the table that logs the changes

• The data entry form uses an auto-number for its primary key lets call it RegistrationReviewID
• Both the table and the report also track that field
• The report assigns a date stamp every time it logs a change, so lets say that the report has 10 records for logged for the RegistrationReviewID 1
• The earliest record on the report is at the beginning of the current month and the most recent is from yesterday.
• I have created this expression (inside of an unbound text field) on the report =Max([DateTime]) and placed it on the record heading for a quick look at the most recent change date for that record. The name of that object is "MostRecentChangeDate"
• Now I would like to bring that value back to the form where it matches the RegistrationReviewID to also identify when was the most recent or last change date to that record.
• I created the following expression on the form but it doesn't work.

=IIF([RegistrationReviewID]=[Reports]![rptAuditTrail]![RegistrationReviewID],[Reports]![rptAuditTrail]![MostRecentChangeDate],"Original")

• Please help!
• Thank you very much for your assistance!!!

[June7]

The function is IIf() not IFF()

[vinsavant]

Thank you for your prompt reply. I made the correction but still gives me an error #Name?

[June7]

That means Access can't find object or control or field referenced.

If you need to pull values from report, why do you have the table name in the reference? Why don't you reference the report name?

[vinsavant]

Since I based my report out of the Audit Trail table, Access assigned the named of the table to the report. I have corrected the syntax to show the proper report name. Still having the issue.

[June7]

I did a test. The form textbox shows #Error until I click Refresh from the menu. Then it does show the value from report textbox.

If you still get #Name? error then Access still doesn't recognize something in the reference. And since I don't know your db I can't really advise how to fix.

I have a situation where I want to save values calculated when report is run. I have code in the report Close event to save values to table.

What do you want to do with the value that is returned to form? That calculated value in the form textbox will not save to table without code (VBA or macro).

[vinsavant]

Thank you so much! I will incorporate your suggestions and keep trying. Best

[John_G]

When you evaluate this expression on your form:


=IIF([RegistrationReviewID]=[Reports]![rptAuditTrail]![RegistrationReviewID],[Reports]![rptAuditTrail]![MostRecentChangeDate],"Original")

is the report open?

If is isn't open, that is the problem - it is not a part of the Reports collection. You do understand, I hope, that like forms, Reports do NOT contain data. Data is stored in tables, and only in tables. So, when you say that "The report assigns a date stamp every time it logs a change," what do you mean by that? Is it saving that timestamp somewhere?

Can you explain further, please?

[vinsavant]

Hi, thank you for your assistance!

Let me clarify my previous description. The "date stamp" on the report actually comes from the tblAuditTrail table. All I have done in the report is use the =MAX() function to extract the most recent date and place it on the section heading. Perhaps I should bring the data directly from the table into my form using something like this:

=IIf([RegistrationReviewID]=[tblAuditTrail]![RecordID],Max([tblAuditTrail]![DateTime]),"Original")

By the way, I tried this code on the form but still get the #Name? error message. For additional clarification, I do not need to store this value, just need it to display while the form is open to let the viewer know when was the last time the record was updated.

Thanks!

[June7]

Can't reference table directly like that. Would have to use a domain aggregate function - DMax() or DLookup().

[John_G]

The function you want is the DMAX() function, not MAX(). Access itself does not have a MAX() function, but there is one in SQL.

What you need is a DMAX() statement what will return the latest date for a specified RegistrationReviewID from tblAuditTrail.

The DMAX would look something like this:

DMAX("DateTime","tblAuditTrail","RegistrationReviewID = " & me!RegistrationReviewID)


where me!RegistrationReviewID is the name of the control containing the ID on your form,

RegistrationReviewID is the name of the field in tblAuditTrail that contains the RegistrationReviewID, and

DateTime is the name of the field that dontains the DateTime in tblAuditTrail.

Now, if there are no records in the table yet for a specified RegistrationReviewID, the DMAX will return a Null, in which case you want "Original" to be shown. To cover that situation, we wrap the whole DMAX in a Nz function, which returns "Original" if the DMAX returns a Null, otherwise it returns the data the DMAX finds:

Nz(DMAX("DateTime","tblAuditID","RegistrationRevie wID = " & me!RegistrationReviewID),"Original")

The unbound field on your form will have this as its control source:

=Nz(DMAX("DateTime","tblAuditID","RegistrationRevi ewID = " & me!RegistrationReviewID),"Original")

[vinsavant]

I truly thank you for your time and efforts in providing me with this detail explanation. I have not been successful in making this code work but will continue trying.

Thank you so much!

[June7]

Except expression in ControlSource cannot use Me. or Me! prefix - only in VBA. Remove the prefix from expression in textbox ControlSource.