Link report date with a table field

[lizzywu]

Hi again, any help or suggestion is highly appreciated.

My questions is: if I want the report date on my report to go back to the field of my table each time I print out my report, is there any way to do that?



What I did was: The whole column of the report date in the table was originally empty, with no actual values entered. I bound a textbox to the field in my report. Since the column contains no actual values, I made the textbox as, say, report_date: date(). That way the textbox in the report has the current date, but this date only appears in the report. I would like the date to appear correspondingly in the table.

Thank you very much.

[June7]

Not sure understand the question. Field is in the report's RecordSource, bind a textbox to field. There must be more to the situation you haven't described.

[lizzywu]

Hi June, I tried to make my question clearer as above. Hope you can understand it. Many many thanks.

[June7]

You could set the field value before or after report opens.

I recommend setting value before opening report.

How are you opening the report? A button Click on a form? Put code in that event. If the date field is in the form's RecordSource and current record is the one needing update, could be simply: Me.fieldname = Date() then close form or DoCmd.RunCommand acCmdSaveRecord to commit the change before opening report. Otherwise, use an UPDATE sql action.

Then instead of Date() in the report textbox, bind to field.

[lizzywu]

June, I tried Me.fieldname=Date(), but it didn't work. I open the report by its print preview, instead of a button click on a form. Do you have any other ideas?

Thank you again.

[June7]

Where did you put the code? Do you want to provide project for analysis?

[lizzywu]

June, I attach the file here. Thank you so much.

[June7]

Can't set data values after report opens so that code behind the report won't work. Must update the date field in the table BEFORE running report. You want this date field to update every time report is run?

No forms in this project - just table, query, report.

Either manually input the date directly into the table or run an Access UPDATE query or build a form behind which code (macro or VBA) can execute to run UPDATE.

[Stingaway]

Use a macro to run an update query that changes the date with Now() instead of date() (just because it gives you more info..) and then run the report in preview mode from the macro. Create a form and a button on the form to fire the macro...

[lizzywu]

Hi, June and Stingaway,

Is it possible that you help me run the macro in the file I gave (if it wouldn't take you too much time)? As Access is new to me, I am unable to do that although I tried running the macro and set up a form.

Really appreciate.

[June7]

Give us your revised file with the attempt.

Run Compact & Repair, zip if still large.

[lizzywu]

June, here's my attempt. If there's anything further I should do, please let me know.

Thank you very much.

[June7]

Well, that embedded macro is coded to open form, not report, and does nothing to update the date field, and no UPDATE query built. Expected more effort than that. Access Help has guidelines on building macros and queries.

I normally use VBA instead of macros. Far as I understand, each line of the macro builder is an action. You will need one to run the UPDATE query and one to open the report. You will also need an Access UPDATE query saved. I got this to work:

The Update query: UPDATE tcf SET tcf.report_date = Now();
This query will update every record in the table.

The macro:
MacroName...Action...Arguments
TCFreport...OpenQuery...UpdateTCF, Datasheet, Read Only
................OpenReport...Report1, Print Preview, , , Normal

The arguments are selected at the bottom of the query builder.

Remove the code from the report module. Set report HasModule property to No.

[lizzywu]

June, millions of thanks. It works! One more question: how could I keep the report date unchanged, say, not each time of running the macro, but only the time I want it save?

I knew I hadn't made enough effort, but it took me quite some time to catch up. However, I'll read more guide once I'm done with my midterms.

[June7]

Include filter criteria in the UPDATE query: WHERE ReportDate Is Null

This will update records that don't already have a date. If you want to be able to run UPDATE on demand and change all records even if already dated, then need to put that action in a separate button.