We help IT Professionals succeed at work.

Data Environment

vbjohn
vbjohn asked
on
I have been asking questions everywhere about Data Reporter.  And it seems that no one uses this thing.

My Question is...  I am using a Recordset that is coded in the Module and I was wondering if you can set that RECORDSET that is in the Module and then move it into a temporary table that is in the Data Environment.  Cause I am doing a UNION QUERY in the Module SINCE you could not do it in the Data Environment, which stinks.

I hope that I have explained it well enough.  If I need to explain further please let me know.


John-
Comment
Watch Question

Commented:
if i understand you correctly then you can do something like:
   Dim myRS as new ADODB.Recordset
   Dim strSQL as string

   strSQL = "<your query>"
       
   myRS.Open strSQL, conn, , adLockReadOnly
   
   'set report datasource
   Set rptYourReport.DataSource = myRS

in your report, just set the datafield property of your textboxes to a valid field in myRS.

Author

Commented:
I tried that and it gives me an error...  This is the Code that I am trying to use.


Dim sSql As String
Dim Rs1 As ADODB.Recordset
Dim Cn1 As ADODB.Connection
Dim sCon As String

'On Error Resume Next
sCon = "Provider=MSDASQL.1;Password=;Persist Security Info=True;User ID=sa;Data Source=dynamics;Initial Catalog=IC"
 
 sSql = "SELECT CHEKNMBR, UPRTRXAM, CHEKDATE, EMPLOYID From UPR30300 " & _
" WHERE AUCTRLCD = 'UPRC02' AND PAYROLCD = 'SALARY' " & _
" Union SELECT LSTCHNUM, LSTCHAMT, LSTCHKDT, VENDORID From PM00201 " & _
" WHERE LSTCHNUM BETWEEN '1' AND '765565';"

Set Cn1 = CreateObject("Adodb.connection")
Cn1.CommandTimeout = 0
Cn1.ConnectionTimeout = 0
Cn1.Open sCon

Set Rs1 = CreateObject("ADODB.Recordset")
Rs1.CursorLocation = adUseClient
Rs1.Open sSql, Cn1, adOpenStatic, adLockReadOnly

  'set report datasource
Set CFTPTReport.DataSource = Rs1

CFTPTReport.Sections("Section1").Controls("txtChekdate").DataMember = "Rs1"
CFTPTReport.Sections("Section1").Controls("txtChekdate").DataField = "CHEKDATE"
CFTPTReport.Sections("Section1").Controls("txtCNum").DataMember = "Rs1"
CFTPTReport.Sections("Section1").Controls("txtCNum").DataField = "CHEKNMBR"
CFTPTReport.Sections("Section1").Controls("txtAmnt").DataMember = "Rs1"
CFTPTReport.Sections("Section1").Controls("txtAmnt").DataField = "UPRTRXAM"
CFTPTReport.Sections("Section1").Controls("txtEMPLOYID").DataMember = "Rs1"
CFTPTReport.Sections("Section1").Controls("txtEMPLOYID").DataField = "EMPLOYID"

CFTPTReport.Show

Commented:
what's the error?

Commented:
when i did it, i didn't set datamember property and i set datafield property at design time rather than at run-time

Author

Commented:
Error message:

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

And I did set the datafield property and still got this stinkin error.

Commented:
what happens if you don't set the datamember property?

Author

Commented:
Error message:

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

And I did set the datafield property and still got this stinkin error.

Author

Commented:
I can email you the screen shots on Monday if you like.  To show you my settings.

If you want me to then email me your address: jcmcgovern@hotmail.com

Commented:
yeah, monday is better, i'm actually really busy on my own project right now... sorry for the delay.  
Commented:
when i did this, i did not set the datamember property at all.  So in your report properties, leave datamember and datasource blank.  In your field properties, just set the datafield property.  At run-time set datasource property of your report and that's all.

your field definition should look like "field" rather than "field.[rs1]"

good luck

Author

Commented:
This guy has been real helpful.  Thanks a million!

Commented:
glad to help! it's nice to know that my help is appreciated... good luck with your project!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.