Creating Single Snapshot Files for Multiple Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form based off a workload table. Once a specific task is completed,
a checkbox is marked yes then a report is created and saved in a snapshot
format. Each snapshot is given a specific name for identification purposes.

As things stand, we cannot check several items off then create the snapshots
because it places the snapshots into one multiple-record snapshot file, which
creates more headaches than I want to explain.

Is there a way to code Access 2002 to create a single snapshot file per
record for several records at a time? My VB knowledge is pretty limited in
that I am still a beginner.

Thank you for any help you can give, even if the answer is "no." At least
then I will stop wasting my time searching the web.
 
Debbilynn,

Yes, it is possible. Here is an example of one approach...

Let's suppose the Record Source of your report is a query named MyQuery.
This query selects the records based on the ticks in the checkbox, and
it sounds like you already have that part sorted. And we also assume
that there is an ID field or some such in the query, and hence the
report, so let's suppose this field is called MyPK.

Ok, so let's open a Recordset based on the records to be outputted, and
loop through one by one, and each time assign the ID into the Where
clause of the report's query. So the code will look something like this...

Dim rst As DAO.Recordset
Dim qdf As DAO.Querydef
Dim BaseSQL As String
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset("SELECT MyPK FROM MyQuery")
Set qdf = CurrentDb.QueryDefs("MyQuery")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL)-3)
With rst
Do Until .EOF
strSQL = BaseSQL & " AND (MyPK=" & ![MyPK] & ")"
qdf.SQL = strSQL
DoCmd.OutputTo acOutputReport, "YourReport", "SnapshotFormat",
![MyPK] & ".snp"
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing

Well, I have made a few assumptions here, but I hope you might get the
general idea.
 
Back
Top