Automating Report to Snapshot for >400 records (2000 SR-1)

Topic

New Reply

I have been asked by a colleague to provide some help in automating a large, repetitive task. The job required is to output a large number of Access reports to snapshot format.

BACKGROUND: We use Access for maintaining manufacturing specifications and it works well. Normally, only a couple of specs may change at a time and then the individual reports are called up, output to snapshot and moved to our server for reference. In this case, there are over 400 reports that have been affected by a change and she’d rather not call up each one separately and output to snapshot.

DETAILS: A table contains all the reference data which is loaded into a report. There are 453 records in this table at the present time. We have a report which loads the required data for a particular record (based on one field [PartNumber]) from the table, as chosen from a separate lookup form. The lookup form is automatically called up when the report is loaded. The user must chose the record in question and then upon clicking OK, the report opens. The user then exports this report in a snapshot format.

How can I build a VBA module which will simply open that report for each record in the table and output a snapshot one-by-one? I’ve tried to duplicate a similar (but slightly unique) task from a different database, but it is not working.

Drew

Reply | Quote

Replies

Perhaps the series of posts starting at post 350282 will give you an idea.

BTW Did you ever get to look at my reply in post 353019 of over two months ago?

Reply | Quote

Edited by HansV to break very long lines that caused horizontal scrolling

Hans,

Thanks for taking a look at my problem. I had, by the way, already read through the other posting by CaptainKen to try and learn something from that dialog. However, I am still stuck. I am about to try something similar to his approach, but I think I need to create copies of the original forms and reports, etc. as there are events associated with them opening. I was able to get it to load each report in turn, but it’s prompting for the part number two more times for each record (this is related to the issue I mentioned in my previous sentence). The copies will then need to be edited in order to remove the associated events which are causing the part numbers to be requested again. At least that’s my guess. I figured it should be a simple loop such as the following (this is straight from the existing module):

    Do Until .EOF 'sets through recordset, creating setup sheet for each record
        If Dir("C:My Documents", vbDirectory) = "My Documents" Then
            If Dir("C:My DocumentsPackagingSpecs", vbDirectory) = "PackagingSpecs" Then
                strPartNumber = rstPackagingSpec!CompanyNo
                'assign current PartNumber to variable
                strFileName = "C:My DocumentsPackagingSpecs" & strPartNumber & ".snp"
                'create file name
                DoCmd.OpenReport "rpt,PackagingSpecForSnapshotOutput", acViewPreview, , _
                    "qry,PackagingSpecs" 'open report for current record
                DoCmd.SelectObject acReport, "rpt,PackagingSpecForSnapshotOutput"
                'select report
                DoCmd.OutputTo acOutputReport, , strOutputFormat, strFileName, False
                'create .snp file of report
                DoCmd.close acReport, "rpt,PackagingSpecForSnapshotOutput", acSaveNo
                'close report
               .MoveNext 'move to next record
            Else
                MkDir ("C:My DocumentsPackagingSpecs")
            End If
        Else
            MkDir ("C:My DocumentsPackagingSpecs")
        End If
    Loop

The code above is a slightly modified version of some code that works in a similar situation in another database. I can’t see why it won’t work in this one.

Drew

Reply | Quote

As far as I can see, you are opening the report with a WhereCondition argument. This has no influence on DoCmd.OutputTo; this will open the report without where-condition and export it. You will have to make the record source of the report filter individual records. The thread I referred to shows one way of doing that.

Reply | Quote

Thanks for your assistance Hans. I did finally get the task accomplished. The problems occured where the reports that were being opened had associated events tied to them “On Open”. I had to replicate those reports and forms and remove the events connected with them. It now works perfectly.

Drew

Reply | Quote

Thanks for your assistance Hans. I did finally get the task accomplished. The problems occured where the reports that were being opened had associated events tied to them “On Open”. I had to replicate those reports and forms and remove the events connected with them. It now works perfectly.

Drew

Reply | Quote

As far as I can see, you are opening the report with a WhereCondition argument. This has no influence on DoCmd.OutputTo; this will open the report without where-condition and export it. You will have to make the record source of the report filter individual records. The thread I referred to shows one way of doing that.

Reply | Quote

Edited by HansV to break very long lines that caused horizontal scrolling

Hans,

Thanks for taking a look at my problem. I had, by the way, already read through the other posting by CaptainKen to try and learn something from that dialog. However, I am still stuck. I am about to try something similar to his approach, but I think I need to create copies of the original forms and reports, etc. as there are events associated with them opening. I was able to get it to load each report in turn, but it’s prompting for the part number two more times for each record (this is related to the issue I mentioned in my previous sentence). The copies will then need to be edited in order to remove the associated events which are causing the part numbers to be requested again. At least that’s my guess. I figured it should be a simple loop such as the following (this is straight from the existing module):

    Do Until .EOF 'sets through recordset, creating setup sheet for each record
        If Dir("C:My Documents", vbDirectory) = "My Documents" Then
            If Dir("C:My DocumentsPackagingSpecs", vbDirectory) = "PackagingSpecs" Then
                strPartNumber = rstPackagingSpec!CompanyNo
                'assign current PartNumber to variable
                strFileName = "C:My DocumentsPackagingSpecs" & strPartNumber & ".snp"
                'create file name
                DoCmd.OpenReport "rpt,PackagingSpecForSnapshotOutput", acViewPreview, , _
                    "qry,PackagingSpecs" 'open report for current record
                DoCmd.SelectObject acReport, "rpt,PackagingSpecForSnapshotOutput"
                'select report
                DoCmd.OutputTo acOutputReport, , strOutputFormat, strFileName, False
                'create .snp file of report
                DoCmd.close acReport, "rpt,PackagingSpecForSnapshotOutput", acSaveNo
                'close report
               .MoveNext 'move to next record
            Else
                MkDir ("C:My DocumentsPackagingSpecs")
            End If
        Else
            MkDir ("C:My DocumentsPackagingSpecs")
        End If
    Loop

The code above is a slightly modified version of some code that works in a similar situation in another database. I can’t see why it won’t work in this one.

Drew

Reply | Quote

Perhaps the series of posts starting at post 350282 will give you an idea.

BTW Did you ever get to look at my reply in post 353019 of over two months ago?

Reply | Quote