Too few parameters. Expected 1

H

Hywel

New member
Local time
Today, 20:22
Joined
Sep 4, 2008
Messages
5
Please forgive me if I’m doing something wrong as this is my first posting, I am completely new to using forums for advice.

I am using the following code to export the result of a query to an excel sheet and would like to pass a parameter to the query “Query2”. If the query contains the parameter everything works fine but if iI try to get it to read the parameter from a textbox on a form I get the message:

Too few parameters. Expected 1.

This is the line that seem to be the problem:

Set rs = db.OpenRecordset("Query2", dbOpenSnapshot)

I got the original code (shown below) from a thread on this forum for which I’m truly grateful

Dim db As DAO.Database
'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
"C:\database.mdb")
Set db = CurrentDb
Set rs = db.OpenRecordset("Query2", dbOpenSnapshot)
Dim mysheet As Object, xlApp As Object
' Set object variable equal to the OLE object.
Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.Workbooks.Open("c:\Access2000\Samples\Names.xls").Sheets(2)

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
mysheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
'Add the data starting at cell A2
mysheet.Range("A2").CopyFromRecordset rs
'Format the header row as bold and autofit the columns
With mysheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With
xlApp.Visible = True
xlApp.UserControl = True
'Close the Database and Recordset
rs.Close
db.Close
End Sub
 
You probably have a form reference (or two) in your query somewhere?
You can use a standard function to open your recordset object. (Passing it either a query name or a SQL statement)...

Set rs = fDAOGenericRst("Query2", dbOpenSnapshot, , , db)

where the function is defined as something like:

Code: 
Function fDAOGenericRst(strSQL As String, Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                                          Optional intOptions As DAO.RecordsetOptionEnum, _
                                          Optional intLock As DAO.LockTypeEnum, _
                                          Optional pdb As DAO.Database) As DAO.Recordset
    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
 
    If Not pdb Is Nothing Then
        Set db = pdb
    Else
        Set db = CurrentDb
    End If
 
    On Error Resume Next
    Set qdf = db.QueryDefs(strSQL)
    If Err = 3265 Then
        Set qdf = db.CreateQueryDef("", strSQL)
    End If
    On Error GoTo 0
 
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
 
    If intOptions = 0 And intLock = 0 Then
        Set rst = qdf.OpenRecordset(intType)
    ElseIf intOptions > 0 And intLock = 0 Then
        Set rst = qdf.OpenRecordset(intType, intOptions)
    ElseIf intOptions = 0 And intLock > 0 Then
        Set rst = qdf.OpenRecordset(intType, intLock)
    ElseIf intOptions > 0 And intLock > 0 Then
        Set rst = qdf.OpenRecordset(intType, intOptions, intLock)
    End If
    Set fDAOGenericRst = rst
 
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
 
End Function
 
Many thanks for your reply.
I have got it working with the following code, the only slight problem is that it opens the excel sheet in read-only. Is there a way around this please?

Dim db As DAO.Database
'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
"C:\database.mdb")
Set db = CurrentDb
'Set rs = db.OpenRecordset("Query2", dbOpenSnapshot)
strSQL = "SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName FROM Customer WHERE CustomerID =" & Forms!TransferToExcel!txtCustomerID
Set rs = db.OpenRecordset(strSQL)
Dim mysheet As Object, xlApp As Object
' Set object variable equal to the OLE object.
Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.Workbooks.Open("c:\Access2000\Samples\Names.xls").Sheets(2)

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
mysheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
'Add the data starting at cell A2
mysheet.Range("A2").CopyFromRecordset rs
'Format the header row as bold and autofit the columns
With mysheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With
xlApp.Visible = True
'xlApp.UserControl = True
'Close the Database and Recordset
rs.Close
db.Close
 
You don't have the spreadsheet open in any other instance of Excel (hidden or otherwise). You have full permissions to the location where it's stored?

Hmm - I've just seen the opening method.
You don't have any object variable pointing to the workbook itself. You just point directly to the sheet.
I'd prefer a dedicated object...

Set mybook = xlApp.Workbooks.Open("c:\Access2000\Samples\Names.xls")
Set mysheet = mybook.Sheets(2)

But make sure you're working on a unique file. (Perhaps create a copy of the file and test from scratch on that).
 
I'm afraid this code gives still opens the spread sheet in Read-Only.

Sorry for this, as you may have guessed I'm pretty new to this sort of programming.

Many thanks.
 
And if you open the file manually it's editable?
 
No its still read-only and opens with a message:-
File in use
Names.xls is locked for editing

Could it be something to do with the line:

xlApp.Visible = True

Does this in some way lock the file as if there was another user.
 
I'm not an expert in this area but copied and modified your code just enough to use one of my databases and everything is fine the spread sheet is not read only so I don't think this code is the culprit.
Have you read Excel help read only and checked if anything there applies.

Brian
 
Hywel said:
No its still read-only and opens with a message:-
File in use
Names.xls is locked for editing

Could it be something to do with the line:

xlApp.Visible = True

Does this in some way lock the file as if there was another user.
Click to expand...

Have you rebooted your computer since the initial error message about the too few parameters? Or have you gone into Task manager and ended any Excel instances running that are not visible? It is highly likely that you have an instance of Excel running from one of your runs that didn't get terminated and that is causing your issue.

I would reboot your computer and see if that fixes it.
 
I decided to switch the computer off as I'd had enough of it for today and found that there must have been another instance of Excel running. After restarting everthing worked fine.

Many thanks to all that have helped.
 
Hywel said:
I decided to switch the computer off as I'd had enough of it for today and found that there must have been another instance of Excel running. After restarting everthing worked fine.

Many thanks to all that have helped.
Click to expand...

Believe me, been there and done that. Just a hint for future pain elimination - I usually put the xlApp.Visible = True part right after the create object during my initial development so that I don't wind up with hidden instances open. Then, after everything is working okay THEN I will move the visibility change back to the end so that the user doesn't see everything happening until it is done (plus it speeds up the processing).
 
Damn, I'd forgotten the first law of my Sytems Programmer group
When all seems lost hit the big red button and start again. :D

Glad your back Bob.

Brian
 
Hywel said:
I decided to switch the computer off as I'd had enough of it for today and found that there must have been another instance of Excel running. After restarting everthing worked fine.
Click to expand...

You had spotted that I suggested that as a possible cause yeah?

Glad you're sorted anyway.
 
You must log in or register to reply here.

Similar threads

B
  • Question Question
export and transpose problem
Replies
8
Views
402
Pat Hartman
P
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
479
Gasman
Gasman
N
Solved Failing To Save Imported Data from the internet in Ms Access
Replies
6
Views
452
nector
N
N
VBA MS Access Error Handling
Replies
5
Views
664
Pat Hartman
P
N
Data source changed from a table to a query
Replies
13
Views
621
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom