Save Consecutive Reports as Snapshots 1

[oakleaf8]

We build a product with +600 different configurations, therefore +600 different part numbers.

My database has a table, primary key "part number" with all the different options checked or unchecked in each record. One report looks at options, determines option locations and displays/prints out all configurations, each page representing a unique part number. This has been our product "bible".

We have a product called "DocTrak" which works with our ERP system. When we print a job from the ERP system we can link the part number to a specific file or set of files with DocTrak, which then get printed with the job paperwork.

So, what I need is code behind a pushbutton that will look at the first record (part number), run the report for just that record, save the report as a one-page snapshot to a specified location on our public drive, move to the next record, repeat process.... until it saves all 600+ as 600+ files in a directory on our public drive.

That way we can specify what .snp file to print when we print an ERP job using DocTrak. When the job gets printed, Snapshot Viewer prints the one-page configuration from the linked .snp file. Assemblers now do not have to wade through 600 pages of configurations.

Does this make sense? Can you help? Thanks!

 

[RobPotts]

Hi,

Are you still having problems with this. I've got this working. First you'll need to create a query with all the feilds to be included in it, then set your report to run from this query. Then make this function

Function fncSaveReports()
Dim qdfTemp As QueryDef
Dim strSQL As String
Dim rst As Recordset
Dim fld As Field
Dim stDocName As String


stDocName = "Name of your report"

Set rst = CurrentDb.OpenRecordset("Table name&quot
Set fld = rst("[Part Number]&quot
rst.MoveFirst
Do Until rst.EOF

'open the query that runs the report
strSQL = "SELECT* FROM Table Name WHERE [Part Number] = '" & fld & "'"

Set qdfTemp = CurrentDb.QueryDefs("Name of Query&quot

'assign strSQL to the SQL property of the Query. Finally set qdfTemp to nothing so don't have open objects.

qdfTemp.SQL = strSQL
Set qdfTemp = Nothing

DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, "C:\Temp\" & fld & ".RTF"

rst.MoveNext

Loop

End Function

Then in the on click of the button put

=fncSaveReports()

Hope this works, the only thing I have found is you need to run 2000 to save as a SNP through code, but I'm no expert.

LEt me know if this works OK for you Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[oakleaf8]

Thanks.

I run it and get a compile error. It highlights:

qdfTemp As queryDef

and says "User-defined type not defined."

 

[RobPotts]

Hi,

My mistake, I made the module in A97, in A2K which I guess your using you'll need to add the DAO referencies. To do this in the code window go to tools Referencies, and make sure that a DOA library is ticked. Then change the start of the code to look like this

Dim qdfTemp As dao.QueryDef
Dim strSQL As String
Dim rst As dao.Recordset
Dim fld As dao.Field
Dim stDocName As String

I've also noted a error in the code, when you specify the saving of the file

DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, "C:\Temp\" & fld & ".RTF"

change the end bit RTF to SNP

DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, "C:\Temp\" & fld & ".SNP"

This should sort out your problem, if not get back and I'll have a look into it a bit more

Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[oakleaf8]

Thanks Rob, works great!

Now all I have to do is figure out code to save them as PDF's instead of SNP's. Tough to do with Acrobat Distiller.