Skip to Content
0
Former Member
Mar 27, 2008 at 05:01 PM

calling a report that have dynamic parameter from vba

80 Views

i have this report that have a dynamic parameter , when i call from my application using vba it aske for server name , user id and password , even i have provided the connection string right , so if there is diffrent way to do it can you tell me , because this is killing me from over a month

i have attached the a screen shot and the code is following

thanks

Dim cn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim userinfo As New RetrieveGlobals9.retrieveuserinfo

Dim strsql, sqlDataSource, pwd, userId1, constring, interCompanyID As String

Dim path, userf, userto As String

Public application As New CRAXDRT.application

Public report As New CRAXDRT.report

Private Sub CUPRPayrollReports_Initialize()

sqlDataSource = userinfo.sql_datasourcename

userId1 = userinfo.retrieve_user

pwd = "sql"

interCompanyID = "ASCTR"

End Sub

Private Sub DepartmentWisePrint_Changed()

CUPRPayrollReports_Initialize

path = "C:\Program Files\Microsoft Dynamics\GP\departmentWise.rpt"

Set cn = New ADODB.Connection

constring = "Provider=MSDASQL" & _

";Data Source=" & sqlDataSource & _

";User ID=" & userId1 & _

";Password=" & pwd & _

";Initial Catalog=" & interCompanyID

With cn

.ConnectionString = constring

.CursorLocation = adUseNone

.Open

End With

Set rst = New ADODB.Recordset

strsql = "SELECT * FROM DepWise"

rst.Open strsql, cn, adOpenDynamic, adLockOptimistic, adCmdText

Set report = application.OpenReport(path, 0)

report.Database.SetDataSource rst, 3, 1

report.EnableParameterPrompting = True

ReportViewer.CrystalActiveXReportViewer1.ReportSource = report

ReportViewer.Top = 0

ReportViewer.Left = 0

ReportViewer.Height = 500

ReportViewer.Width = 760

ReportViewer.ScrollBars = fmScrollBarsBoth

ReportViewer.CrystalActiveXReportViewer1.Top = 0

ReportViewer.CrystalActiveXReportViewer1.Left = 0

ReportViewer.CrystalActiveXReportViewer1.Height = 500

ReportViewer.CrystalActiveXReportViewer1.Width = 750

ReportViewer.CrystalActiveXReportViewer1.EnableGroupTree = False

ReportViewer.CrystalActiveXReportViewer1.EnableRefreshButton = True

ReportViewer.CrystalActiveXReportViewer1.EnableExportButton = True

ReportViewer.CrystalActiveXReportViewer1.EnablePrintButton = True

ReportViewer.CrystalActiveXReportViewer1.ViewReport

'If ReportViewer.Visible = False Then ReportViewer.Show

'If ReportViewer.Visible = False Then

ReportViewer.Show

Set rst = Nothing

Set cn = Nothing

End Sub