Thread: output to function
12-23-04, 12:27 #1Registered User
- 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:
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?---
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.
12-25-04, 06:29 #2Cavalier King Charles
- 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
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
msgbox "all done and no errors!", vbinformation, "LUCKY!"
set recs = nothing
set dabs= nothing
msgbox err.description & " - " & err.number, vbcritical, "UNLUCKY!"
end subcurrently using SS 2008R2
12-27-04, 12:17 #3Registered User
- 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.