Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: One File Output per Access Recordset Record - Part1

    Greetings all, I am what would be considered an annual VB code developer, In other words, I just dont do it enough to be any good at it. So, I am hoping that one of the experts out there can help me accomodate the Winds of Change.

    For many moons I have produced a daily report for our local fire department that provided incident information for the previous days events. This was accomplished with an Access report (myReport) and a select query (myQuery). The select query pulls about 50 fields from a few SQL tables via ODBC. It normally has about 80 records for any given day. The select query is the record source for the report. This daily process is automated and generates one file (.RTF) that is about 80 pages long & it does not print to a printer. The powers that be have asked for a change with the following requirements.

    #1 - Each incident must now be output to its own file (80 one page files a day instead of one 80 page)

    #2 - The output filename has to be formatted in a certain fashion (this will require one value from a field in the data)

    These changes are to accomodate an automatic import to a new document management system.

    I have looked around and come up with this code that I have leashed to a form load

    ------------------------

    Private Sub Form_Load()

    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rec As DAO.Recordset

    Set rec = db.OpenRecordset("myQuery", dbOpenDynaset)

    Do While Not rec.EOF

    DoCmd.OutputTo acOutputReport, "myReport", acFormatRTF, "C:\Test\INC" & rec!Master_Incident_Number & ".rtf"

    rec.MoveNext

    Loop

    ------------------------

    It seems to loop properly and even produces one file per record, My problem is that each output file contains the entire recordset (probably the contents of myQuery). I need it to only contain the current record the recordset pointer is pointing to. I dont know how to tell the report (myReport) to use the current recordset record instead of the myQuery record source

    Thanks for any help :-)

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Have a hidden control on your form and filter the query by looking at that control.
    Now in VBA open a recordset with all your daily outputs in it and with each cycle populate the hidden control before you output the report.
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Mar 2013
    Posts
    2
    Software Matters - Thanks for your reply. I was able to get what I needed using this.

    Private Sub Form_Load()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rec As DAO.Recordset
    Set rec = db.OpenRecordset("SELECT Master_Incident_Number FROM myQuery;", dbOpenDynaset)
    Do While Not rec.EOF
    DoCmd.OpenReport "myReport", acViewPreview, , "Master_Incident_Number='" & rec!Master_Incident_Number & "'"
    DoCmd.OutputTo acOutputReport, , acFormatRTF, "C:\Test\INC" & rec!Master_Incident_Number & ".rtf"
    DoCmd.Close acReport, "myReport", acSaveNo
    rec.MoveNext
    Loop

Posting Permissions

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