Creating a log file (archive) of reports printed

[Guest]

I have a form where the user selects which report they want to print and a few other details e.g. they can change the title and a few other fields in the header and footer areas. My problem is how can I open a log file and append a record each time with the actual details as they were printed (with which title etc) I have tried OpenTable but it open the table for manual data entry, I dont want the user to see the log table at all. I will the create a report which can print the log file but not change any of the entries. Thanks

 

[Nikos Yannacopoulos]

Naso,

Create a table to hold details on the print events, with fields for report
name, date/time, detail1...X etc (I would also add a field for the user).
Then use a few lines of code at the beginning of your print procedure to
store that info in the table by means of a DAO recordset. Sample code:

Dim rst As DAO.Recordset
Set rst = CurrentDB.OpenRecordset("TableName")
rst.AddNew
rst.Fields("ReportSelected") = varReportName '(variable must be assigned a
value first)
rst.Fields("TimeStamp") = Now()
rst.Fields("Detail1") = Forms!FormName!txtDetail1
....
....
rst.Fields("Detail1") = Forms!FormName!txtDetail1
rst.Fields("User") = CurrentUser 'if you have implemented security, or
rst.Fields("User") = Environ("UserName") 'to log the Windows logon name
rst.Update
rst.Close
Set rst = Nothing

Of course, you will need to change my example table, field, form and control
names to the actual ones. You will also need to add the DAO library to the
db references, if not already there (Microsoft DAO 3.51 for Access97,
Microsoft DAO 3.6 for Access2K or later).

HTH,
Nikos

I have a form where the user selects which report they want to print and a

few other details e.g. they can change the title and a few other fields in
the header and footer areas. My problem is how can I open a log file and
append a record each time with the actual details as they were printed (with
which title etc) I have tried OpenTable but it open the table for manual
data entry, I dont want the user to see the log table at all. I will the
create a report which can print the log file but not change any of the
entries. Thanks

 

[PC Datasheet]

Doing it the wal you suggest here is not typical! If you get a printer
malfunction, paper jam, etc., your method here logs the report as being printed
more than once. The typical way to do it is to raise a user dialog after
printing asking if the report was printed successfully. If the user answers Yes
then log that the report was printed.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I have a form where the user selects which report they want to print and a few

other details e.g. they can change the title and a few other fields in the
header and footer areas. My problem is how can I open a log file and append a
record each time with the actual details as they were printed (with which title
etc) I have tried OpenTable but it open the table for manual data entry, I dont
want the user to see the log table at all. I will the create a report which can
print the log file but not change any of the entries. Thanks

 

[Guest]

Niko, Thanks for your help
My problem is that I dont only want to append a new record to the log file, but also to keep the current selections to show as the default next time. I guess my problem is more complicated than I first said. I have two table," current " and "history", current table contains one record for each report in my application, history contains all the reports which have been printed and the details they included date printed, title used etc. The user chooses a report on a form which shows hime the current data for the specific report, he makes any changes necessary (usually just the date, and then the report is printed and the history file is updated. The structure of the two table is identical. (All the fields are identical.) If the report has already been printed on the same day I have a field called "copy" which increments so I know how many times it was printed. I can't use now for the date printed because I want the user to specify the date he wants

----- Nikos Yannacopoulos wrote: ----

Naso

Create a table to hold details on the print events, with fields for repor
name, date/time, detail1...X etc (I would also add a field for the user)
Then use a few lines of code at the beginning of your print procedure t
store that info in the table by means of a DAO recordset. Sample code

Dim rst As DAO.Recordse
Set rst = CurrentDB.OpenRecordset("TableName"
rst.AddNe
rst.Fields("ReportSelected") = varReportName '(variable must be assigned
value first
rst.Fields("TimeStamp") = Now(
rst.Fields("Detail1") = Forms!FormName!txtDetail
...
...
rst.Fields("Detail1") = Forms!FormName!txtDetail
rst.Fields("User") = CurrentUser 'if you have implemented security, o
rst.Fields("User") = Environ("UserName") 'to log the Windows logon nam
rst.Updat
rst.Clos
Set rst = Nothin

Of course, you will need to change my example table, field, form and contro
names to the actual ones. You will also need to add the DAO library to th
db references, if not already there (Microsoft DAO 3.51 for Access97
Microsoft DAO 3.6 for Access2K or later)

HTH
Niko

I have a form where the user selects which report they want to print and

few other details e.g. they can change the title and a few other fields i
the header and footer areas. My problem is how can I open a log file an
append a record each time with the actual details as they were printed (wit
which title etc) I have tried OpenTable but it open the table for manua
data entry, I dont want the user to see the log table at all. I will th
create a report which can print the log file but not change any of th
entries. Thank

 

[Nikos Yannacopoulos]

Naso,

You don't necessarily need two tables, you can use the same one to default
the last settings in the form, by using DLookups on the same table where
user = current user and report = selected report (I suppose the before
update event of report selection is a good one to fire some code to populate
the parameter controls doing the lookups)... the only other thing required
being an autonumber field in the table (if not already there), so it's
easier for the DLookups to pick up the most recent record for each user /
report combination. The code would be something like the following, in broad
lines:

recnum = DMax("[AutonumberField]","TableName","[User]='" & CurrentUser & "'
And [ReportName]='" & Me.SelectedReport & "'")
'(all above is one line, if it gets wrapped)
Me.Detail1 = DLookup("[Detail1]","TableName","[AutoNumberField]=" & recnum)
Me.Detail2 = DLookup("[Detail2]","TableName","[AutoNumberField]=" & recnum)
....

Alternatively, you could get the record number in the same way, and use a
recordset instead of the DLookups.

HTH,
Nikos

Niko, Thanks for your help.
My problem is that I dont only want to append a new record to the log

file, but also to keep the current selections to show as the default next
time. I guess my problem is more complicated than I first said. I have two
table," current " and "history", current table contains one record for each
report in my application, history contains all the reports which have been
printed and the details they included date printed, title used etc. The user
chooses a report on a form which shows hime the current data for the
specific report, he makes any changes necessary (usually just the date, and
then the report is printed and the history file is updated. The structure of
the two table is identical. (All the fields are identical.) If the report
has already been printed on the same day I have a field called "copy" which
increments so I know how many times it was printed. I can't use now for the
date printed because I want the user to specify the date he wants.

 

[Guest]

Thanks for the ideas, I have been able to get the history bit working ok, but now I Have a further problem. The report I want to print is on a form and I select it from a drop list of available reports. I then want to open the report from the form, the trouble is the recordsource of the form is a query on the table I mentioned below. How can I include the name of the report in the query so it only selcts the one record?? Many thanks to anyone who can hel

----- Nikos Yannacopoulos wrote: ----

Naso

You don't necessarily need two tables, you can use the same one to defaul
the last settings in the form, by using DLookups on the same table wher
user = current user and report = selected report (I suppose the befor
update event of report selection is a good one to fire some code to populat
the parameter controls doing the lookups)... the only other thing require
being an autonumber field in the table (if not already there), so it'
easier for the DLookups to pick up the most recent record for each user
report combination. The code would be something like the following, in broa
lines

recnum = DMax("[AutonumberField]","TableName","[User]='" & CurrentUser & "
And [ReportName]='" & Me.SelectedReport & "'"
'(all above is one line, if it gets wrapped
Me.Detail1 = DLookup("[Detail1]","TableName","[AutoNumberField]=" & recnum
Me.Detail2 = DLookup("[Detail2]","TableName","[AutoNumberField]=" & recnum
...

Alternatively, you could get the record number in the same way, and use
recordset instead of the DLookups

HTH
Niko

Niko, Thanks for your help
My problem is that I dont only want to append a new record to the lo

file, but also to keep the current selections to show as the default nex
time. I guess my problem is more complicated than I first said. I have tw
table," current " and "history", current table contains one record for eac
report in my application, history contains all the reports which have bee
printed and the details they included date printed, title used etc. The use
chooses a report on a form which shows hime the current data for th
specific report, he makes any changes necessary (usually just the date, an
then the report is printed and the history file is updated. The structure o
the two table is identical. (All the fields are identical.) If the repor
has already been printed on the same day I have a field called "copy" whic
increments so I know how many times it was printed. I can't use now for th
date printed because I want the user to specify the date he wants