I am trying to figure out a way to update multiple records when a
report is exported. Each record has a "Status" field. I have a query
that selects all records with a status of "To Send" and that feeds
into a report. When the report is exported i want the status to be
changed from "To Send" to "Sent".
Any help would be appreciated.
Thanks!
OldPro - 29 Oct 2007 23:00 GMT
> I am trying to figure out a way to update multiple records when a
> report is exported. Each record has a "Status" field. I have a query
[quoted text clipped - 5 lines]
>
> Thanks!
It can't actually know that the report printed or exported... it only
knows that the attempt was made. If you are using a command button to
export the report, then add a line after the line that generates the
report, that updates the table.
Here is an example:
dim db as dao.database
dim sSQL_WHERE as string
set db = currentdb( )
sSQL_WHERE = " [FieldOne]=" & num1 & " [FieldTwo]=TRUE"
db.execute "UPDATE tblSomeTable SET [Printed] = TRUE WHERE " &
sSQL_WHERE
sSQL_WHERE must be set to the WHERE portion of the same query that the
report is based on with the same parameters.
Fred Zuckerman - 29 Oct 2007 23:09 GMT
>I am trying to figure out a way to update multiple records when a
> report is exported. Each record has a "Status" field. I have a query
[quoted text clipped - 5 lines]
>
> Thanks!
Create a procedure that performs the export, then add an update query that
changes the records from "To Send" to "Sent".
''''Sample - AirCode
Public Sub ExportReport()
DoCmd.OutputTo acOutputReport, "MyReport", acFormatSNP,
"C:\MyReport.snp", 0
DoCmd.RunSQL "UPDATE MyTable SET [Status]='Sent' WHERE [Status]='To
Send'"
End Sub
Fred Zuckerman
paii, Ron - 30 Oct 2007 13:31 GMT
There is no way for Access "know" if the export worked or the report was
printed.
When I need to confirm this type of activity, I add 2 fields to the detail
table.
ExportID Long integer
ExportC Yes / No
Before doing the report or export, I write one export ID number into all the
records selected.
Once the export or report is completed the user will use the export ID to
set ExportC to true.
> I am trying to figure out a way to update multiple records when a
> report is exported. Each record has a "Status" field. I have a query
[quoted text clipped - 5 lines]
>
> Thanks!