Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / October 2007

Tip: Looking for answers? Try searching our database.

Update Records Export

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Toby  Gallier - 29 Oct 2007 22:39 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
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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.