Error: Too Few Parameters, Expected 1

[frispee]

Hi,

I am exporting a record set to Excel within a loop and I am encountering the following error while defining that record set:

Error 3061 - Too few parameters - Expected 1

The line of code highlighted is:

Set SResults = db.OpenRecordset(sqlResult)

The line in context is:

For x = 1 To n
    sqlResult = "SELECT [Annual Material Cost], [Total Logistics Cost], [Logistics Cost per Part], [Total Annual Cost per Part] FROM TACFinal_tbl WHERE [TAC ID] =  '" & Me.TACID_tbx & "' AND [Scenario ID] = " & x
    Set SResults = db.OpenRecordset(sqlResult)
    objSht.Cells(4, y).CopyFromRecordset SResults
    y = y + 4
    Next

Please help as I am stuck on this for a long time!

Thanks in advance

 

[vbaInet]

There's no point of looping, that's what CopyFromRecordset does for you.

1. Open the recordset once with a criteria of "[Scenario ID] BETWEEN 1 AND " & n
2. Run the code.

Back to your original problem. You need to check the value of TACID_tbx. It's not returning a value.

 

[pr2-eugin]

Should not SQL strings end with a ; ??

[B]......... [/B][Scenario ID] = " & x[COLOR=Red] [B]& ";"[/B][/COLOR]

 

[bob fitz]

If [TAC ID] is numeric I don't think you need the single quotes
....WHERE [TAC ID] = '" & Me.TACID_tbx & "' AND ......

 

[boblarson]

If [TAC ID] is numeric I don't think you need the single quotes
....WHERE [TAC ID] = '" & Me.TACID_tbx & "' AND ......

Correct, no quotes if numeric. Is the ”table” being referred to in this query a table or another query?

 

[frispee]

@ All: Thanks so much for replying. However, I am still getting the error. I have done the changes (removing the quotes and adding the ";") I also checked if TACID_tbx is returning a value. It does. I stored it in another variable and made that to be displayed in a 'test' field in the form. The test field gets populated with the expected value. So that does not seem to be the issue. The new query is:

sqlResult = "SELECT [Annual Material Cost], [Total Logistics Cost], [Logistics Cost per Part], [Total Annual Cost per Part] FROM TACFinal_tbl WHERE [TAC ID] =  " & a & " AND [Scenario ID] = " & x & ";"
    Set SResults = db.OpenRecordset(sqlResult)

Removing the loop is not an option. I am trying to get Access to export the table (which has more than one Scenario ID for a given TACID) one scenario at a time into parallel sets of columns. I am sorry if this sounds confusing.
Any more suggestions? Thanks so much for taking an interest

 

[vbaInet]

Is this a query? I can see a field called [Total Annual Cost per part] which may indicate that this is a Totals Query. If it is, is it parameterized?

 

[frispee]

The values of a and n are:

a = Me.TACID_tbx.Value
n = DMax("[Scenario ID]", "TACFinal_tbl", "[TAC ID] = '" & Me.TACID_tbx & "'")

@Bob: Thanks for replying It is a table, not a query.

 

[pr2-eugin]

Try using the query inside the Query builder.. there might be possibility for misspelling some fields.. See if it is returning the desired data..

 

[frispee]

@vbaInet: The fields in the table "TACFinal_tbl" were to be populated by other queries in a different part of the application. This section of the database deals only with exporting the results to Excel. The fields in the table are set to be numeric and sometimes currency.

Thanks for taking an interest in my problem

 

[vbaInet]

I missed Bob's post asking you whether it was a table or a query.

Can you upload a stripped down version of your db so we can see what's happening. And are you sure it's the OpenRecordset line of code that's throwing the error? It most likely is but confirm.

 

[frispee]

Guys, I got it working. And I am to blame for this whole issue: I had set the TACID to be a text field as I was told that it could be alpha-numeric. I completely forgot about that. So I put the single quotes back into a and removed it from x. This combination worked and its working as expected now. Thanks so much to all of you for pitching in. I feel like an idiot now! This forum is the bestest!!

 

[MisterC]

Thanks to all for this very in-depth examination of a problem similar to my own; mine is fixed, and I can move on and forget the past 24 hours, STUCK on the learning curve.