Error 3061 (1 Viewer)

[Insane_ai]

I am gettign Error 3061 Too Few parameters, Expected=1

Dim RSH_W As DAO.Recordset

strSQL_WH = "SELECT DISTINCT RPTBASE_BROKER_PRICING.CONTAINER_SIZE FROM RPTBASE_BROKER_PRICING" _
& " WHERE ((RPTBASE_BROKER_PRICING.Product_Type) = 'W')" _
& " ORDER BY RPTBASE_BROKER_PRICING.CONTAINER_SIZE;"

Set RSH_W = DB.OpenRecordset(strSQL_WH)

The base Query RPTBASE_BROKER_PRICING is tied to a form that is open when this code is running. There are no parameters defined for the base query.

Can someone shed some light on this error please? I have not been able to find anything that reveals and actual cause.

I cheat a bit with the followign code to make sure my SQL is correct:
'SQL Testing Section:
'DoCmd.OpenForm "SQLTester"
'With Forms!SQLTester
' .txtSQL = strSQL_WH
'End With

The SQL command works without issue when pasted into a blank query.

 

[boblarson]

Is RPTBASE_BROKER_PRICING a QUERY? If so, does it, or any other queries that it might depend on, have parameters? If so, you'll need to declare them in each query leading up to this one.

 

[Insane_ai]

Bob,

Thank you for the suggestion but there are no parameters for the RPTBASE_BROKER_PRICING query. That query is built from tables connected to Oracle 10g via ODBC. It does depend on a combo box to choose the broker for pricing and a date field to choose the proper price book by effective date.

The purpose of this code is to eventually integrate a crosstab query into a report. The step I am stuck on is getting the actual container sizes from the price book so I can declare the headings in the PIVOT statement in the next step.

 

[boblarson]

Bob,

Thank you for the suggestion but there are no parameters for the RPTBASE_BROKER_PRICING query. That query is built from tables connected to Oracle 10g via ODBC. It does depend on a combo box to choose the broker for pricing and a date field to choose the proper price book by effective date.

The purpose of this code is to eventually integrate a crosstab query into a report. The step I am stuck on is getting the actual container sizes from the price book so I can declare the headings in the PIVOT statement in the next step.

Okay, you have me confused. You state:

but there are no parameters for the RPTBASE_BROKER_PRICING query

But then you go and say

It does depend on a combo box to choose the broker for pricing and a date field to choose the proper price book by effective date.

And those would be PARAMETERS! And again, like I stated you have to DECLARE YOUR PARAMETERS IN EACH QUERY THAT IS DEPENDENT UPON THEM and that means including the Crosstab Query, so it can pass the parameters to the query/queries that makes up that crosstab query.

 

[CJ_London]

This may be early days for the crosstab but be sure to predeclare the column headings if you want to use it in a form or report

 

[CJ_London]

Also, is your form with the combobox open when you run the query?

 

[Insane_ai]

Allow me to clarify.

I have no declared parameters in the query, the query itself is built with preceding VBA statements that plaster the variables directly into the SQL, rewriting the query definition meaning I have no PARAMETERS statement in the SQL itself. I see your point but having a WHERE clause is not the same as PARAMETERS clause. (If I just exposed some ignorance with that last statement, please correct me.)

The point in getting the list of container sizes is to be able to declare the headings for each price book that can have one or more of the following sizes and possibly new ones in the future: 5,10,25,35,55,85,100,150,250,350,500 etc.

Since I cannot know what sizes will be quoted for each price book, I have to make it dynamically based on the users' selections without leaving anything out or adding irrelevant data in.

I think I can make this happen with static queries to pull the list of container sizes quoted and just pull that data.

I still have no idea why the SQL works fine in a query but not in code, it's a pretty simple query.

Thank you all for your assistance on this one, I really appreciate it.

 

[CJ_London]

The point in getting the list of container sizes is to be able to declare the headings for each price book that can have one or more of the following sizes and possibly new ones in the future: 5,10,25,35,55,85,100,150,250,350,500 etc.

If your crosstab is currently

TRANSFORM ...
SELECT ...
FROM ...
GROUP ...
PIVOT Fld
 
you will need to change the pivot line to
 
PIVOT Fld In (Col1, Col2, Col3...)

 

[Insane_ai]

Thanks CJ but I haven't made it to the crosstab query yet. I still need to get the headings so I can define them then refer to them for the report.

I am still troubleshooting the error 3061 issue at this point an may just use a static query to get around the recordset problem.

 

[Insane_ai]

RESOLVED:

I found the error in my base query, I had failed to replace the direct reference in to the form with the variable in one of the four instances it is used.

Thank you all for letting me pick your brains on this one.

 

[hondauser]

I have encountered a similar error message with the following SQL. Can someone provide me some suggestions?
Much appreciated.

strSQL = "INSERT INTO tblVendors ( cmdVendorID, cmdVendor, cmdVendorType, cmdFacilitator, cmdAddress1, cmdAddress2, cmdCity, cmdState, cmdZip, cmdContact, cmdLocationSearch, cmdEffectiveDate )" & _
" SELECT tblVendors.VendorId, tblVendors.VendorName, tblVendors.VendorType, tblVendors.Facilitator, tblVendors.VendorAddr1, tblVendors.VendorAddr2, tblVendors.VendorCity, " & _
" tblVendors.VendorState, tblVendors.VendorZip, tblVendors.VendorContact, tblVendors.LocationSearch, tblVendors.EffectiveDate FROM tblVendors;"

 

[CJ_London]

Looks like you are trying to insert the vendorID into the same table

strSQL = "INSERT INTO [COLOR=red]tblVendors ( cmdVendorID[/COLOR], cmdVendor, cmdVendorType, cmdFacilitator, cmdAddress1, cmdAddress2, cmdCity, cmdState, cmdZip, cmdContact, cmdLocationSearch, cmdEffectiveDate )" & _
" [COLOR=red]SELECT tblVendors.VendorId[/COLOR], tblVendors.VendorName, tblVendors.VendorType, tblVendors.Facilitator, tblVendors.VendorAddr1, tblVendors.VendorAddr2, tblVendors.VendorCity, " & _
" tblVendors.VendorState, tblVendors.VendorZip, tblVendors.VendorContact, tblVendors.LocationSearch, tblVendors.EffectiveDate [COLOR=red]FROM tblVendors[/COLOR]

 

[hondauser]

I see the issue. No, the data comes from the input on the form, some from combo box and some from text box. How would the SQL syntax be for data coming from the form input?

 

[hondauser]

Sorry perhaps my previous posting is not clear. The form that I created contains unbound fields. Instead of using a form with bind fields, I added some customization query codes to some input fields. After input the data to these fields, how do I insert a record to the table with all these unbound fields?

 

[CJ_London]

It would be something like

strSQL = "INSERT INTO tblVendors 
(cmdVendorID, cmdVendor, cmdVendorType, cmdFacilitator, cmdAddress1, cmdAddress2, cmdCity, cmdState, cmdZip, cmdContact, cmdLocationSearch, cmdEffectiveDate ) "
VALUES (me.ctrl1, me.ctrl2, me.ctrl3......)"

this assumes that cmdVendor etc is the names of your fields in tblVendors

Also, if cmdVendorID is an autonumber field type in your table then you shouldn't specify it in your insert