How to fill, selected by a quarry, table fields automatically?

[Kicked]

Hi,



I have got table containing informations about Change Notes. Every so often this Change Notes are finished. I have got a Report showing finished Reports between certain dates (from date; to date). Next time, when I am generating Report, using te same dates or dates partially covering previous dates, access shows already printed Change Notes. I want to avoid duplicates on Reports and I want to have these Reports to be saved with certain date to be archived. Please help me.

[June7]

Filter the Change Notes records by some criteria.

What do you mean by 'archive'? You mean the actual physical paper report?

[Kicked]

I need some way to let Access know, that, the records (Change Notes) has been already in the Report (Printed, mailed etc.). So next time, I will not get them again in the report. Well. There is a way to make it manually, opening every record and adding some info, but I need this to be done automatically. Like, for example: every Report will add to Change Note Table an information about a date of the Report. So next day report will not include them. About archiving, I think I have got it. When Report is made, I have added Macro which is saving Report as an file on the disc. This is perfect archive as I can share this files with others on the network. Just this problem with automated adding Date to the specific records in the table when Report is done, printed what so ever. Hard to explain.

[June7]

You need a field for filtering records. Say, a Status field (Date, Yes/No, or Text type). Code could automatically update this field when report is run. Report would include all (unless you have some additional criteria to be considered, like customer ID) records where this field is No or Null (depending on which type you go with) then code would update the No or Null to some value. Have to consider possibility of need to rerun the report. I would go with the date field. Update field to date of report. Then could have option to rerun a report from a given date (either keep the date or modify for the rerun).

[Kicked]

Well, I know, I need another field in Change Note Table. I know, it should be a date field. The only problem is: how to make automatic function to update date field when report has been made? I have already included Macro which is saving Report as snapview on the hard disc when Report is made.

[June7]

Use an SQL UPDATE. I don't know if this can be done in macro, I only use VBA.

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tablename SET fieldname=somevalue WHERE fieldname=criteria"
DoCmd.SetWarnings True

Code could be in the same event that triggers opening the report.

[Kicked]

Well, if you can give me an idea how to make Report during process of closing down update Table Field with a current date I will be happy. Maybe tomorrow I bring this files over and make an attachement to the Reply.

[June7]

The example in previous post shows UPDATE with literal strings. You would have to concatenate the variable values.
DoCmd.RunSQL "UPDATE tablename SET datefieldname=#" & Date() & "# WHERE IDfieldname=" & Me.IDtextbox