Using openrecordset with query giving run-time error 3061

[mophead]

I am getting a run-time error 3061 when using openrecordset command with a query. I am testing and learning code to export table information to an MS Excel worksheet. I have pasted the code below. Now I have used this code over and over several times and never had any problems until now.

CODE:
Private Sub RunExcel_Click()

Dim NewWkBk As Excel.Workbook
Dim PlantWkSh As Excel.Worksheet
Dim FinWkSh As Excel.Worksheet
Dim ProWkSh As Excel.Worksheet
Dim MainWkSh As Excel.Worksheet
Dim CurrRow As Integer
Dim strInputName As String
Dim strFileName As String
Dim strSQL As String
Dim MyDb As Database
Dim MySet As Recordset

strInputName = Me!FileName
strFileName = "J:\ApplicationDev\SuzetteRamsden\My Documents\IT Office Work\EconM\ExcelWorkSheets\" + strInputName

'Open new Excel Workbook and name
Set NewWkBk = EconModel.Workbooks.Add
NewWkBk.SaveAs strFileName

'Add new worksheet for main model and name
Set MainWkSh = NewWkBk.Worksheets.Add
Let MainWkSh.Name = "Economic Model"

'Add new worksheet for process data and name
Set ProWkSh = NewWkBk.Worksheets.Add
Let ProWkSh.Name = "Process Data"

'Add new worksheet for plant data and name
Set PlantWkSh = NewWkBk.Worksheets.Add
Let PlantWkSh.Name = "Plant Data"

'Add new worksheet for financial data and name
Set FinWkSh = NewWkBk.Worksheets.Add
Let FinWkSh.Name = "Financial Data"

EconModel.Visible = True

'Put Title for worksheet and initialize the rows to start from 1
CurrRow = 1

'Turn off warnings
DoCmd.SetWarnings False

Set MyDb = CurrentDb()
Set MySet = MyDb.OpenRecordset("FinDataQuery&quot

'Turn warnings back on
DoCmd.SetWarnings True

'Loop through the financial data query for the current model to the corresponding worksheet
Do Until MySet.EOF
With FinWkSh
.Cells(CurrRow, 1).Value = MySet!CorpTaxRate
.Cells(CurrRow, 2).Value = MySet!AlterTax
End With
MySet.MoveNext
CurrRow = CurrRow + 1
Loop

With MainWkSh
.Cells(1, 1).Name = "Heading"
.Cells(1, 1).Value = "Economic Model for Mega Ammonia Plant"
.Cells(3, 1).Value = "Revenue"
.Cells(3, 1).Name = "Revenue"
.Cells(4, 1).Value = "Variable Cost"
.Cells(4, 1).Name = "VCost"
.Cells(5, 1).Value = "Fixed Cost"
.Cells(5, 1).Name = "FCost"
.Cells(6, 1).Value = "Operating Cost"
.Cells(6, 1).Name = "OpCost"
End With


Exit_RunExcel_Click:
Exit Sub

Err_RunExcel_Click:
MsgBox Err.Description
Resume Exit_RunExcel_Click

End Sub


The error occurs at the line:
Set MySet = MyDb.OpenRecordset("FinDataQuery&quot

I even tried typing a query string and putting the string variable instead (see below), but still get the same error.

ALTERNATIVE CODE:
'Open financial data recordset for current model
strSQL = "SELECT Models.ModelName, FinancialData.CorpTaxRate, FinancialData.AlterTax, "
strSQL = strSQL & "FinancialData.CostOfBorrow, FinancialData.TaxHoliday, "
strSQL = strSQL & "FinancialData.CapCost, FinancialData.LandFixed, FinancialData.LandVariable, "
strSQL = strSQL & "FinancialData.Utilities "
strSQL = strSQL & "FROM Models INNER JOIN FinancialData ON Models.ModelID = FinancialData.ModelID "
strSQL = strSQL & "WHERE (((Models.ModelName)=[Forms]![InputDataArea]![ModelName]));"


'Turn off warnings
DoCmd.SetWarnings False

Set MyDb = CurrentDb()
Set MySet = MyDb.OpenRecordset(strSQL)


I am stomped for a solution. I know it maybe something simple, but I can't figure it out. Any help will be appreciated.

MopHead

 

[rbowes]

I am not sure what the query sql statement is, but your alternative sql doesn't seem to be right. I think I had problems with that kind of statement and the way around it is this set the [Forms]![InputDataArea]![ModelName] to a variable so that the sqlstatement looks like this

Public function SQLState() as string
dim strSQL as string
DIm strParam as string

strParam =[Forms]![InputDataArea]![ModelName]
'OR strParam = Me.ModelName if you create the sql statement in the InputDataArea form

strSQL = "SELECT Models.ModelName, FinancialData.CorpTaxRate, FinancialData.AlterTax, "
strSQL = strSQL & "FinancialData.CostOfBorrow, FinancialData.TaxHoliday, "
strSQL = strSQL & "FinancialData.CapCost, FinancialData.LandFixed, FinancialData.LandVariable, "
strSQL = strSQL & "FinancialData.Utilities "
strSQL = strSQL & "FROM Models INNER JOIN FinancialData ON Models.ModelID = FinancialData.ModelID "
strSQL = strSQL & "WHERE [Models].[ModelName] = '" & strParam & "'));"

SQLState = strsql

end function

Then in RunExcel_Click() event

Set MySet = MyDb.OpenRecordset(SQLState)

Give it a try, I think it will solve your problem

Bob