Parameter Problem with VBA

[Idealsteve]

am pretty new to visual basic and I have created some code which exports a load of queries to excel based on query names I have stored in a table, however I am encountering problems with a few of the queries as they run based on a value I have put in a form as it creates an error as it says I have no parameters.

Can anyone help

This is the code I am using

Option Compare Database
 Function cmdExportQueries()
 Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryQueryList")
 Dim oXLApp As Excel.Application       'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
 Set oXLApp = New Excel.Application  'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Add 'Create new workbook
 oXLApp.DisplayAlerts = False
 oXLBook.Worksheets(3).Delete
oXLBook.Worksheets(2).Delete
 Dim qryNumber As Integer
qryNumber = 1
 Do Until rst.EOF
     Dim rstQry As DAO.Recordset
    Dim qrySQL As String
     Dim QryName As String
    QryName = rst!QueryName
    
    Dim FrmName As String
    FrmName = rst!FormName
    
               
    qrySQL = "SELECT * FROM [" & QryName & "]"
     Set rstQry = CurrentDb.OpenRecordset(qrySQL)
     Set oXLSheet = oXLBook.Worksheets(qryNumber)
     oXLSheet.Range("A2").CopyFromRecordset rstQry
    
    For iCols = 0 To rstQry.Fields.Count - 1
        oXLSheet.Cells(1, iCols + 1).Value = rstQry.Fields(iCols).name
        oXLSheet.Cells(1, iCols + 1).Font.Bold = True
    Next
    
    oXLSheet.Cells(1, rstQry.Fields.Count).AutoFilter
    oXLSheet.name = Left(QryName, 30)
    oXLBook.Sheets.Add After:=oXLBook.Sheets(qryNumber)
    qryNumber = qryNumber + 1
     rstQry.Close
    rst.MoveNext
 Loop
 oXLApp.DisplayAlerts = True
 oXLBook.Worksheets(qryNumber).Delete
Set oXLSheet = oXLBook.Worksheets(1)
oXLBook.Sheets(1).Activate
 
Dim FilePath As String
FilePath = "F:\LSF Querys\"
 Dim TheFileName As String
TheFileName = "Bacs_1516_" & Year(Now()) & "_" & Month(Now()) & "_" & day(Now()) & ".xlsx"
 oXLBook.SaveAs FilePath & TheFileName
oXLBook.Close
 Set oXLBook = Nothing
Set oXLApp = Nothing
 rst.Close
Set rst = Nothing
 End Function

Thanks
Steve

 

[Ranman256]

this is a database. you dont have to loop thru records to update data.
this is what update queries are for.
you execute queries with parameters in them. These parameters can be filled out on a form, and it only takes 1 command:
docmd.openquery "quUpdfields"

 

[Minty]

Are any of your queries with parameters crosstabs?
If they are you have to explicitly name the parameters in the saved query parameters option.

 

[Idealsteve]

Thanks for you reply

Do you know where I would put the docmd.openquery code in the code above so it runs all of the queries I have in my list.

Thanks
Steve

 

[Idealsteve]

Are any of your queries with parameters crosstabs?
If they are you have to explicitly name the parameters in the saved query parameters option.

Thanks For your reply

All of the queries are select queries

Thanks
Steve

 

[Cronk]

On what line is the error occurring?

I notice that the following statement
Set oXLSheet = oXLBook.Worksheets(qryNumber)
is before the statement to add the sheet

 

[Idealsteve]

On what line is the error occurring?

I notice that the following statement
Set oXLSheet = oXLBook.Worksheets(qryNumber)
is before the statement to add the sheet

Hi

The error is occurring on the following line.

Set rstQry = CurrentDb.OpenRecordset(qrySQL)

The code works fine and exports all of my queries if I type my own values in the queries. The problem occurs when I am using the value in a text box on a form to run the query. the error I keep getting is

Runtime error '3061'
Too Few Parameters. Expected 1.

Thanks
Steve

 

[spikepl]

You needs to look into QueryDef and set the parameter value for the textbox.

It's beause the OpenRecordset looks at the query and has no idea what references to controls mean.

Google QueryDef or access error 3061 or both.

 

[arnelgp]

change this portion:

qrySQL = "SELECT * FROM [" & QryName & "]"     
 Set rstQry = CurrentDb.OpenRecordset(qrySQL)

' remove this line
' qrySQL = "SELECT * FROM [" & QryName & "]"
Set rstQry = CurrentDb.QueryDefs(QryName).OpenRecordset

 

[Idealsteve]

change this portion:

qrySQL = "SELECT * FROM [" & QryName & "]"     
 Set rstQry = CurrentDb.OpenRecordset(qrySQL)

' remove this line
' qrySQL = "SELECT * FROM [" & QryName & "]"
Set rstQry = CurrentDb.QueryDefs(QryName).OpenRecordset

Thanks for this but is still coming up with the same error I removed

qrySQL = "SELECT * FROM [" & QryName & "]"
 And replaced it with 
 Set rstQry = CurrentDb.QueryDefs(QryName).OpenRecordset

Thanks
Steve

 

[arnelgp]

does your query have parameters inside the like Forms!formname!controlname, etc.

 

[Idealsteve]

does your query have parameters inside the like Forms!formname!controlname, etc.

Yes the line I am using in the query is [Forms]![Access Fund 2004]![Text3549]

Thanks
Steve