use DAO Recordset to populate a report

L

longhair

afternoon all
i'm trying to populate a report with a dao recordset. the recordset contains the proper data, but i can't seem to pass the info to select queries(3) for their parameters to run the report
any and all suggestions are welcome

Module
Option Compare Databas
Public rstEffect As DAO.Recordse
Public Part As Strin

Public Sub btnRunReport_Click(
On Error GoTo Err_btnRunReport_Clic

Dim stDocName As Strin
Dim dbs As DAO.Databas
Dim qdfEffect As DAO.QueryDe
'Dim rstEffect As DAO.Recordse
Dim param As DAO.Paramete
Dim txtDate As Dat
Dim fld As DAO.Fiel
Dim varData As Varian
Dim intCount As Intege
Dim DateField As Dat
Dim Part As Strin
Dim strSql As Strin
varData = "
intCount =

txtDate = Me.Form.ReportDat
MsgBox txtDat
Set dbs = CurrentD
Set qdfEffect = dbs.QueryDefs("ToolRoomEffectivenessQuery"
'qdfEffect.Parameters("pInitial") = "yes
qdfEffect.Parameters("pQueryDate") = txtDat
Set rstEffect = qdfEffect.OpenRecordset(dbOpenDynaset
MsgBox rstEffect.RecordCoun
Part = rstEffect.Fields("Part Number"
MsgBox Par

'stDocName = "rptTest1
stDocName = "ToolRoomEffectivenessReport
DoCmd.OpenReport stDocName, acPrevie
rstEffect.Clos
Set rstEffect = Nothin
Exit_btnRunReport_Click
Exit Su

Err_btnRunReport_Click
MsgBox Err.Descriptio
Resume Exit_btnRunReport_Clic

End Su

Private Sub Report_Open(Cancel As Integer
Dim dbs As DAO.Databas
Dim qdfEffectDetail As DAO.QueryDe
Dim rstEffectDetail As DAO.Recordse
'Dim rstEffect As DAO.Recordse
Dim paramDetail As DAO.Paramete
Dim fld As DAO.Fiel

Set dbs = CurrentD
Set qdfEffectDetail = dbs.QueryDefs("Query2"
qdfEffectDetail.Parameters("pJobNum") = rstEffect.Fields("Job Number"
qdfEffectDetail.Parameters("pParDie") = rstEffect.Fields("Parent Die"
qdfEffectDetail.Parameters("pDieType") = rstEffect.Fields("Die Type"
qdfEffectDetail.Parameters("pRows") = rstEffect.Fields("Rows"
Set rstEffectDetail = qdfEffectDetail.OpenRecordset(dbOpenDynaset

Do Until rstEffect.EO
Me.RecordSource = rstEffect.Nam

rstEffect.MoveNex
Loo
End Su

what the problem appears to be, for example,is that data in rstEffect.Fields(Job Number) is not being passed to qdfEffectDetail.Parameters("pJobNum"). i have pJobNum defined as a parameter in my sql, but when i run the code the query (Query2) still prompts me for the input parameters. we can deal with txtDate not being passed to qdfEffect.Parameters("pQueryDate") since we know the date to report on. the fact is that we do not know which records will be returned for the parent portion "ToolRoomEffectivenessQuery" so we will not know what data is needed for the child portion "Query2"

ti
regards

longhair
 
D

dick penny

I struggled with this for2-3 weeks just prior to Christmas. And put a post
into "reports" newsgroup. Search for author name DICK, around 1/8/04 (I
guess).

Basically you cannot populate a report with a recordset DIRECTLY. You must
use a round-a-bout method. Forget that there are some newsgroup posts which
say 'you can by blah, blah, blah.' These only work under some conditions. A
quick summary of what I did is:
1) create a permanante temporay table (a one time action) whose structure
is identical with that of the underlying data table.
2) get your sub-set of records by some manor. I got mine via filter-by-form.
3) delete all records in temp table (don't even bother to chk if any
records, do a SQL delete), then copy your recordset into temp table via VBA
code, say 10-15 lines
4) open your report based on (with record source of) temp table

HTH
Dick Penny


longhair said:
afternoon all,
i'm trying to populate a report with a dao recordset. the recordset
Click to expand...
contains the proper data, but i can't seem to pass the info to select
queries(3) for their parameters to run the report.
any and all suggestions are welcome.

Module1
Option Compare Database
Public rstEffect As DAO.Recordset
Public Part As String

Public Sub btnRunReport_Click()
On Error GoTo Err_btnRunReport_Click


Dim stDocName As String
Dim dbs As DAO.Database
Dim qdfEffect As DAO.QueryDef
'Dim rstEffect As DAO.Recordset
Dim param As DAO.Parameter
Dim txtDate As Date
Dim fld As DAO.Field
Dim varData As Variant
Dim intCount As Integer
Dim DateField As Date
Dim Part As String
Dim strSql As String
varData = ""
intCount = 0

txtDate = Me.Form.ReportDate
MsgBox txtDate
Set dbs = CurrentDb
Set qdfEffect = dbs.QueryDefs("ToolRoomEffectivenessQuery")
'qdfEffect.Parameters("pInitial") = "yes"
qdfEffect.Parameters("pQueryDate") = txtDate
Set rstEffect = qdfEffect.OpenRecordset(dbOpenDynaset)
MsgBox rstEffect.RecordCount
Part = rstEffect.Fields("Part Number")
MsgBox Part

'stDocName = "rptTest1"
stDocName = "ToolRoomEffectivenessReport"
DoCmd.OpenReport stDocName, acPreview
rstEffect.Close
Set rstEffect = Nothing
Exit_btnRunReport_Click:
Exit Sub

Err_btnRunReport_Click:
MsgBox Err.Description
Resume Exit_btnRunReport_Click

End Sub

Private Sub Report_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim qdfEffectDetail As DAO.QueryDef
Dim rstEffectDetail As DAO.Recordset
'Dim rstEffect As DAO.Recordset
Dim paramDetail As DAO.Parameter
Dim fld As DAO.Field

Set dbs = CurrentDb
Set qdfEffectDetail = dbs.QueryDefs("Query2")
qdfEffectDetail.Parameters("pJobNum") = rstEffect.Fields("Job Number")
qdfEffectDetail.Parameters("pParDie") = rstEffect.Fields("Parent Die")
qdfEffectDetail.Parameters("pDieType") = rstEffect.Fields("Die Type")
qdfEffectDetail.Parameters("pRows") = rstEffect.Fields("Rows")
Set rstEffectDetail = qdfEffectDetail.OpenRecordset(dbOpenDynaset)

Do Until rstEffect.EOF
Me.RecordSource = rstEffect.Name

rstEffect.MoveNext
Loop
End Sub

what the problem appears to be, for example,is that data in
Click to expand...
rstEffect.Fields(Job Number) is not being passed to
qdfEffectDetail.Parameters("pJobNum"). i have pJobNum defined as a
parameter in my sql, but when i run the code the query (Query2) still
prompts me for the input parameters. we can deal with txtDate not being
passed to qdfEffect.Parameters("pQueryDate") since we know the date to
report on. the fact is that we do not know which records will be returned
for the parent portion "ToolRoomEffectivenessQuery" so we will not know what
data is needed for the child portion "Query2".
 
L

longhair

morning dick

thanks for the reply. it is basically the route i decided to take this am after struggling with this for a few days. will look up your other post for pointers

regards

longhai

----- dick penny wrote: ----

I struggled with this for2-3 weeks just prior to Christmas. And put a pos
into "reports" newsgroup. Search for author name DICK, around 1/8/04 (
guess)

Basically you cannot populate a report with a recordset DIRECTLY. You mus
use a round-a-bout method. Forget that there are some newsgroup posts whic
say 'you can by blah, blah, blah.' These only work under some conditions.
quick summary of what I did is
1) create a permanante temporay table (a one time action) whose structur
is identical with that of the underlying data table
2) get your sub-set of records by some manor. I got mine via filter-by-form
3) delete all records in temp table (don't even bother to chk if an
records, do a SQL delete), then copy your recordset into temp table via VB
code, say 10-15 line
4) open your report based on (with record source of) temp tabl

HT
Dick Penn


longhair said:
afternoon all
i'm trying to populate a report with a dao recordset. the recordse
Click to expand...
contains the proper data, but i can't seem to pass the info to selec
queries(3) for their parameters to run the report
any and all suggestions are welcome
Option Compare Databas
Public rstEffect As DAO.Recordse
Public Part As Strin
On Error GoTo Err_btnRunReport_Clic
Dim dbs As DAO.Databas
Dim qdfEffect As DAO.QueryDe
'Dim rstEffect As DAO.Recordse
Dim param As DAO.Paramete
Dim txtDate As Dat
Dim fld As DAO.Fiel
Dim varData As Varian
Dim intCount As Intege
Dim DateField As Dat
Dim Part As Strin
Dim strSql As Strin
varData = "
intCount =
MsgBox txtDat
Set dbs = CurrentD
Set qdfEffect = dbs.QueryDefs("ToolRoomEffectivenessQuery"
'qdfEffect.Parameters("pInitial") = "yes
qdfEffect.Parameters("pQueryDate") = txtDat
Set rstEffect = qdfEffect.OpenRecordset(dbOpenDynaset
MsgBox rstEffect.RecordCoun
Part = rstEffect.Fields("Part Number"
MsgBox Par
stDocName = "ToolRoomEffectivenessReport
DoCmd.OpenReport stDocName, acPrevie
rstEffect.Clos
Set rstEffect = Nothin
Exit_btnRunReport_Click
Exit Su
MsgBox Err.Descriptio
Resume Exit_btnRunReport_Clic
Dim dbs As DAO.Databas
Dim qdfEffectDetail As DAO.QueryDe
Dim rstEffectDetail As DAO.Recordse
'Dim rstEffect As DAO.Recordse
Dim paramDetail As DAO.Paramete
Dim fld As DAO.Fiel
Set qdfEffectDetail = dbs.QueryDefs("Query2"
qdfEffectDetail.Parameters("pJobNum") = rstEffect.Fields("Job Number"
qdfEffectDetail.Parameters("pParDie") = rstEffect.Fields("Parent Die"
qdfEffectDetail.Parameters("pDieType") = rstEffect.Fields("Die Type"
qdfEffectDetail.Parameters("pRows") = rstEffect.Fields("Rows"
Set rstEffectDetail = qdfEffectDetail.OpenRecordset(dbOpenDynaset
Me.RecordSource = rstEffect.Nam
Loo
End Su
Click to expand...
rstEffect.Fields(Job Number) is not being passed to
qdfEffectDetail.Parameters("pJobNum"). i have pJobNum defined as a
parameter in my sql, but when i run the code the query (Query2) still
prompts me for the input parameters. we can deal with txtDate not being
passed to qdfEffect.Parameters("pQueryDate") since we know the date to
report on. the fact is that we do not know which records will be returned
for the parent portion "ToolRoomEffectivenessQuery" so we will not know what
data is needed for the child portion "Query2".
 
Top