Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Perhaps the series of posts starting at <post#=350282>post 350282</post#> will give you an idea.

    BTW Did you ever get to look at my reply in <post#=353019>post 353019</post#> of over two months ago?

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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):

    <pre> 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
    </pre>

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •