too few parameters error ?

[qwertyjjj]

I have a form with 2 subforms.
There are various dropdowns on the the first subform, which set the recordsource for the form and can be changed continually.
The recordource is determined from some queries which have parameters like
Forms![TabsReport]!.[Units1].Form.combo_A

All the drop downs work correctly and data is changed accordingly so the queries must be picking up the parameters from the form correctly.

However, when I try to export this to Excel, I get a "too few parameters. Expected 1" error even though it is trying to use excatly the same recordsource.

Any ideas why ?


Private Sub cmd_Export_Click()

Dim iCount As Integer
Dim strCell As String

iCount = DCount("

", Form.RecordSource)
        
        '--------------------------------------
        'Main recordset with level codes
        '--------------------------------------
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rst As DAO.Recordset

        Set db = CurrentDb
        Set rs = db.OpenRecordset(Form.RecordSource, dbOpenSnapshot)
      
        'Start a new workbook in Excel
         Dim oApp As New Excel.Application
         Dim oBook As Excel.workBook
         Dim oSheet As Excel.workSheet
      
         Set oBook = oApp.Workbooks.Add
         Set oSheet = oBook.Worksheets(1)
      
         'Add the field names in row 1
         Dim i As Integer
         Dim iNumCols As Integer
         iNumCols = rs.Fields.Count
         For i = 1 To iNumCols
         oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
         Next
     
         'Add the data starting at cell A2
         oSheet.Range("A2").CopyFromRecordset rs
     
         'Format the header row as bold and autofit the columns
         With oSheet.Range("a1").Resize(1, iNumCols)
         .Font.Bold = True
         .EntireColumn.AutoFit
         End With
     
         With oSheet
             'now delete all data for previous months as it's not needed on the spreadsheet
             .Columns("Z").Delete Shift:=xlToLeft
             .Columns("R:X").Delete Shift:=xlToLeft
             .Columns("O").Delete Shift:=xlToLeft
             .Columns("G:M").Delete Shift:=xlToLeft
         End With
        
        '--------------------------------------
        'Top 10 recordset
        '--------------------------------------
        Set rst = db.OpenRecordset(Forms!TabsReport!Top10Debtors_BU.Form.RecordSource, dbOpenSnapshot)
        
        'Add the field names in row 1
        iNumCols = rst.Fields.Count
        
        For i = 1 To iNumCols
        oSheet.Cells(iCount + 16, i).Value = rst.Fields(i - 1).Name
        Next
        
        'Format the header row as bold and autofit the columns
        strCell = "A" & iCount + 16
        With oSheet.Range(strCell).Resize(1, iNumCols)
        .Font.Bold = True
        .EntireColumn.AutoFit
        End With
       
        'Add the data starting at cell A?
        strCell = "A" & iCount + 17
        oSheet.Range(strCell).CopyFromRecordset rst
        
        '------------------------------------------
        'Format the sheet as per Finance template
        '------------------------------------------
        With oSheet
            .Rows("1:1").Insert Shift:=xlDown
            .Rows("1:1").Insert Shift:=xlDown
            .Rows("1:1").Insert Shift:=xlDown
            .Rows("1:1").Insert Shift:=xlDown
            .Range("A3").Value = "(1) Debt Summary (£ 000's)"
            .Range("A3").Font.Underline = xlUnderlineStyleSingle
            
            .Range("A" & iCount + 7).Value = "Totals"
            .Range("A" & iCount + 8).Value = "Totals Last Month"
            .Range("A" & iCount + 9).Value = "Variance"
            .Range("A" & iCount + 7 & ":A" & iCount + 9).Font.Bold = True
            
            .Range("A" & iCount + 11).Value = "(2) Percentage Ageing"
            .Range("A" & iCount + 11).Font.Underline = xlUnderlineStyleSingle
            
            .Range("A" & iCount + 13).Value = "Current Month"
            .Range("A" & iCount + 14).Value = "Ageing Last Month"
            .Range("A" & iCount + 15).Value = "Variance"
            
            .Range("A" & iCount + 18).Value = "(3) Top 10 Bad Debt Provisions"
            .Range("A" & iCount + 18).Font.Underline = xlUnderlineStyleSingle
            
            .Columns("A:A").EntireColumn.AutoFit
            
        End With


        '--------------------------------------
        'Tidy up
        '--------------------------------------
        oApp.Visible = True
        oApp.UserControl = True
       
        'Close the Database and Recordset
        rs.Close
        rst.Close
        db.Close
        
        Set rs = Nothing
        Set rst = Nothing
        Set db = Nothing
        Set oBook = Nothing
        Set oSheet = Nothing
        Set oApp = Nothing
End Sub

 

[Bodisathva]

Usually the "too few parameters..." error shows (in my experience, at least) when the automated query is not retrieving unique results. Check for duplicates in your query results or some part of missing criteria that would enable the query to retrieve a unique record.

 

[qwertyjjj]

But the drop downs work correctly in conjunction with the same parametised queries.
For example, I select an option in the drop down. The query then gets called as the recordsource using the value in the drop down.
If that works, then I can't figure out why the recordsource in the code above wouldn't work as they are being called from exactly the same form?

One thing to note is that the subform is called Units1 whereas the original form that it is "made from" is just called Units

Again, I can't see how this would make a difference as the drop down populates the form correctly with data so why can't the recordset be created from the same query?

 

[boblarson]

I believe your problem is this part:

Set rs = db.OpenRecordset(Form.RecordSource, dbOpenSnapshot)

I am pretty sure the syntax would need to be:

Set rs = db.OpenRecordset(Form("YourFormNameHere").RecordSource, dbOpenSnapshot)

OR

Set rs = db.OpenRecordset(Me.RecordSource, dbOpenSnapshot)

Don't know why it actually works for you in one area, though. Give this a try and see if that helps.

 

[qwertyjjj]

Hi
Can't seem to get those to work either

At the start of the sub I have used the following:
MsgBox (Form.Recordsource)

This brings up a message box with the correct query it is running everytime I change a drop down and then try to export yet it always falls over on the same line Set rs = .......

with the "Too few parameters error. Expected 1"

Interestingly enough, on the queries that use no parameters, it works fine.
So, the parameters are obviously the problem. But why would creating a recordset from the same recordsource cause a problem, especially as the recordsource brings back the data onto the form but cannot seem to retrieve it for the separate recordset?

I have attached a stripped down version of the DB if that will help.
Hold down the shift button when you open to unlock the toolbar on the top.
Then open the TabsReport Form.
If you press the export to excel button when the first dropdown has All Divisions selected then it works.
As soon as you select more specific divisions from the next dropdown, like SY200, and the parameter queries are used, it errors.

 

[qwertyjjj]

Any idea? Seems like a strange error.
Is there any alternative way round it

 

[qwertyjjj]

bump......

 

[boblarson]

Sorry, I took a look and your queries are so complex I couldn't spot where the problem lies. I took my best shot and hopefully someone else can spot the problem. Good luck! Be sure to post an answer if you get this sorted.

 

[qwertyjjj]

Hi
Thanks for looking. I know the queries look complex but the only bit that really matters in them, is in the 1st join SELECT Field FROM Structure WHERE...
and then it goes on to put in a parameter from the form. This is the only place where parameters are in the query.
You will see in the code that the drop downs use the same parameter queries and they work correctly. Yet, when trying to export a recordset from the same parameter query, there's an error.
Just cannot figure it out.

 

[qwertyjjj]

Hi
I did get this sorted eventually.
It turns out that Access can't reconcile the form references in the queries, so you have to actually refresh all the parameters by doing:

Set db = CurrentDb
Set qdf = db.QueryDefs(Me.Form.RecordSource)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm