Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004

    Unanswered: output to function


    I am having problem with the output to function in a macro. I am trying to ouput a report without having to enter the name and location for each one as there are over 30,000 files that will be created. I think I understand what the code is saying but I am unsure as to how to incorporate the query paramaters into the module. The module I am working on looks looks like this:

    ' stream
    Function stream()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim strStream As String
    Dim strClaim As String
    Dim strType As String
    Set db = CurrentDb

    ---This is the part i ca't figure out - i know i have to pass these parameters from the query but I am unsure how to do it--
    qdf.Paramaters("[Forms]![input]![claim]") = input![claim]

    Set rs = db.OpenRecordset("input")

    strStream = rs![claim] & "_" & rs![type]
    strClaim = rs![claim]
    strType = rs![type]

    DoCmd.OutputTo acReport, "offload_notes", "RichTextFormat(*.rtf)", "m:\" & strClaim & "\" & strType & "\" & strStream & "_123104.rtf", False, "", 0

    DoCmd.GoToRecord , "input", acNext

    ---Note that this query is a parameter query and I think I need to supply the parameters in the procedure - how do i do that?---


    Exit Function

    MsgBox Error$
    Resume stream_Exit

    End Function

    Where [claim] and [type] are form fields,
    the report name is [offload_notes],
    the underlying query name is [stream]
    and the form name is [input]

    Any help you cna provide would be beneficial.



  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    dao.querydef.parameters is read-only according to my a2k object browser.

    what are you trying to do?
    ?? you have a query that is returning 30,000 values??
    ?? you intend to run a separate report for each of the 30,000 values - the report is based on a second query??

    if that was YES to those two questions, then here's an Xmas day thrash at the problem.

    i think the way forward might be to get the 30,000 values into a recodset and then use them as a filter for each report. note that i don't use filters myself, so the following code may contain errors, but i hope it gets you started. you could use a recordset in a form, but why bother - nobody is going to look at 30,000 records in a form!

    we are going to try filters, so first thing to do is kill the parameters in the query driving the report.

    then some code (probably a _Click() of a button somewhere, but for the time being i'll call it print30000)

    private sub Print30000()
    dim dabs as dao.database
    dim recs as dao.recordset
    dim strFile as string
    dim strFilt as string

    on error goto err_Print30000

    set dabs = currentdb
    set recs = dabs.openrecordset("nameOfQueryReturning30000recor ds")

    'loop thru each record
    with recs
    do while not .eof

    'set a filter on the report
    'WARNING - i have never used this - hope it works out!!!!
    strFilt = "((claim = '" & !claim & "') AND (type = '" & !type & "'))"
    reports!offload_notes.filter = strFilt
    reports!offload_notes.filteron = true

    'output the file
    strFile = !claim & "_" & !type
    DoCmd.OutputTo acReport, "offload_notes", "RichTextFormat(*.rtf)", "m:\" & !claim & "\" & !type & "\" & strFile & "_123104.rtf", False, "", 0

    end with

    msgbox "all done and no errors!", vbinformation, "LUCKY!"

    set recs = nothing
    set dabs= nothing
    exit sub

    msgbox err.description & " - " & err.number, vbcritical, "UNLUCKY!"
    resume exit_print30000

    end sub
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2004

    Re: X-mas thrash

    Thanks I'll give it a go. I was using a form instead of a filter as I am not familiar with using filters either. The weird thing is that the code i wrote runs and outputs everything correctly, but it never passess a second value for the file name and path directory. What ends up happening is that the same file gets overwritten X number of times. I'll go ahead and give this a go and see if I can get it to work. thanks a ton.



Posting Permissions

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