Trouble setting recrodset to variable

• SQLAJ

  Ten Centuries

  Points: 1359

  More actions

  April 28, 2008 at 12:19 pm

  #123702

  Hi,

  I am having trouble setting the return record set into a variable to then pass to an excel file.

  I am close (at least according to my errors) hopefully some one can shed some light.

  Thanks in advance.

  /*********************************/

  Private Sub cmdCPI_Click()

  Dim rst As ADODB.Recordset

  Dim frm As Form

  Dim strPeriod As String

  Dim strSQL As String

  Dim prm As String

  Dim objXLApp As Excel.Application

  Dim CommandText As String

  Dim cnn As ADODB.Connection

  Set rst = New ADODB.Recordset

  Dim cmd As New ADODB.Command

  Dim params As ADODB.Parameters

  Dim param As ADODB.Parameter

  Set cnn = DatabaseConnection()

  'cnn.CommandTimeout = 600

  'Declare variables for Stored Procedure

  Dim myVariable As Variant

  Dim myReturn As String

  If IsFormLoaded("fdlgFiscalPeriod") Then DoCmd.Close acForm, "fdlgFiscalPeriod"

  ' Open the form as acDialog to pause the code for user input

  DoCmd.OpenForm "fdlgFiscalPeriod", WindowMode:=acDialog, OpenArgs:=PeriodType.FISCALMONTH

  'Make sure the form is still loaded when control is passed back

  If Not IsFormLoaded("fdlgFiscalPeriod") Then Exit Sub

  'Get a pointer to the now hidden form

  Set frm = Forms!fdlgFiscalPeriod

  If frm.txtChoice = "CANCEL" Then

  DoCmd.Close acForm, "fdlgFiscalPeriod"

  Exit Sub

  End If

  strPeriod = frm.lstPeriod.Column(1, frm.lstPeriod.ListIndex)

  DoCmd.Close acForm, "fdlgFiscalPeriod"

  'Append parameters

  With cmd

  .ActiveConnection = cnn

  .CommandTimeout = 300

  .CommandText = "usr_spRunCPI_Report"

  .CommandType = 4

  Set params = .Parameters

  End With

  params.Append cmd.CreateParameter("@p_Report_FY_FM", adVarChar, adParamInput, 50, strPeriod)

  ' Specify input parameter values

  params("@p_Report_FY_FM") = strPeriod

  Set rst = cmd.Execute

  ' Execute the command

  Set strSQL = Trouble setting recrodset to variable

  'strSQL = rst.Fields("myVariable").Value

  Set objXLApp = New Excel.Application

  'open the template workbook

  objXLApp.Workbooks.Open (cAppPath & "CPI_Report\CPI_Report.xls")

  objXLApp.ActiveSheet.Name = strPeriod

  Call ExportToExcel(objXLApp, "A2", strSQL, strPeriod)

  objXLApp.Visible = True

  End Sub

  ______________________________
  AJ Mendo | @SQLAJ

• SQLAJ

  Ten Centuries

  Points: 1359

  More actions

  April 28, 2008 at 1:11 pm

  #807997

  Never mind all I figured it out.

  ______________________________
  AJ Mendo | @SQLAJ