error 3061 to few parameters (1 Viewer)

[mveijndh]

Running the following recordset generates an error 3061, to few parameters.
When I run the Query by inself it does run and generates the required single opject I'm looking for.

What do I need to do to solve this one?The problem is in the Where clause with the TempVars. If I delete that, it runs fine. problem I do need only 1 record. Replace the TempVars with the Original might do, but the Tempvars is very easy to use and I can't use the DoCmd.TransferSpreadsheet with a filter!

Set rst = dbs.OpenRecordset("queOrderSum")
The query is a very extensive query, but running fine.

SELECT tblOrder.OrderId, FormatDateTime([OrderDate],2) AS OrderDateShort, tblCompany.CompName, [tblCompany!CompStreet] & " " & [tblCompany!CompStreetNum] & " " & [tblCompany!CompAddLocInfo] AS ClientAddress, [tblCompany!CompPostCode] & " " & [tblCompany!CompCity] AS ClientCityCom, tblCountry.CountryName, "+" & [tblCountry]![CountryPhoneCode] & " " & [tblCompany]![CompPhone] AS ClientPhone, "+" & [tblCountry]![CountryPhoneCode] & " " & [tblCompany]![CompFax] AS ClientFax, tblContact.ContEmail, tblCompany_1.CompName, [tblCompany_1]![CompStreet] & " " & [tblCompany_1]![compstreetnum] & " " & [tblCompany_1]![CompAddLocInfo] AS SiteAddress, [tblCompany_1!CompPostCode] & " " & [tblCompany_1!CompCity] AS CitySite, tblCountry_1.CountryName, "+" & [tblCountry_1]![CountryPhoneCode] & " " & [tblCompany_1]![CompPhone] AS SitePhone, "+" & [tblCountry_1]![CountryPhoneCode] & " " & [tblCompany_1]![CompFax] AS SiteFax, tblContact_1.ContEmail, tblCompany_2.CompName, [tblCompany_2]![CompStreet] & " " & [tblCompany_2]![compstreetnum] & " " & [tblCompany_2]![CompAddLocInfo] AS InvoiceAddress, [tblCompany_2!CompPostCode] & " " & [tblCompany_2!CompCity] AS InvoiceCity, tblCountry_2.CountryName, tblCompany_2.CompRegistration, tblCompany_2.CompVAT, "+" & [tblCountry_7]![CountryPhoneCode] & "-" & [tblContact_2]![ContPhone] AS InvoicePhone, "+" & [tblCountry_7]![CountryPhoneCode] & "-" & [tblContact_2]![ContFax] AS InvoiceFax, tblContact_2.ContEmail, tblCompany_3.CompName, [tblCompany_3]![CompStreet] & " " & [tblCompany_3]![compstreetnum] & " " & [tblCompany_3]![CompAddLocInfo] AS SoldByAddress, [tblCompany_3!CompPostCode] & " " & [tblCompany_3!CompCity] AS SoldByCity, tblCountry_3.CountryName, "+" & [tblCountry_3]![CountryPhoneCode] & " " & [tblCompany_3]![CompPhone] AS SoldByPhone, "+" & [tblCountry_3]![CountryPhoneCode] & " " & [tblCompany_3]![CompFax] AS SoldByFax, tblEmployee.EmployeeInitials, tblOrder.CAEJobNumber, tblOrder.ClientOrderNumber, [tblOrder]![OrderValue] & " " & [tblCurrency]![Currency3] AS orderValCur, [tblOrder]![OrderPurchaseValue] & " " & [tblCurrency_1]![Currency3] AS orderPurValCur, [tblOrder]![OrderFreightValue] & " " & [tblCurrency_2]![Currency3] AS orderFreightValCur, [tblOrder]![OrderLabourValue] & " " & [tblCurrency_3]![Currency3] AS orderLaborValCur, [tblOrder]![OrderTravelValue] & " " & [tblCurrency_4]![Currency3] AS orderTravelValCur, [tblOrder]![OrderHotelValue] & " " & [tblCurrency_5]![Currency3] AS orderHotelValCur, tblOrder.OrderValueCurID, tblOrder.OrderPurchaseCur, tblOrder.OrderLabourCur, tblOrder.OrderTravelCur, tblOrder.OrderHotelCur, tblOrder.OrderFreightCur, tblOrder.OrderValue, tblOrder.OrderFreightValue, tblOrder.OrderFreightCur, [PaymentDelayDays] & " Days " & [PaymentDelayStart] AS PaymentTerms, Round([OrderValue]*Switch([OrderValueCurID]=1,(1/[excDefPouToEur]),[OrderValueCurID]=2,1,[OrderValueCurID]=3,(1/[excDefPouToDol])),2) AS OrderValPou, Round([OrderPurchasePou]+[OrderLabourPou]+[OrderTravelPou]+[OrderHotelPou]+[OrderFreightPou]+[OrderParkingPou]+[OrderHandlingPou],2) AS OrderTotCosts, Round([OrderHandlingCost]*Switch([OrderHandlingCur]=1,(1/[excDefPouToEur]),[OrderHandlingCur]=2,1,[OrderHandlingCur]=3,(1/[excDefPouToDol])),2) AS OrderHandlingPou, Round([OrderPurchaseValue]*Switch([OrderPurchaseCur]=1,1/[excDefPouToEur],[OrderPurchaseCur]=2,1,[OrderPurchaseCur]=3,1/[excDefPouToDol]),2) AS OrderPurchasePou, Round([OrderLabourValue]*Switch([OrderLabourCur]=1,1/[excDefPouToEur],[OrderLabourCur]=2,1,[OrderLabourCur]=3,1/[excDefPouToDol]),2) AS OrderLabourPou, Round([OrderTravelValue]*Switch([OrderTravelCur]=1,1/1.45,[OrderTravelCur]=2,1,[OrderTravelCur]=3,1/1.45),2) AS OrderTravelPou, Round([OrderHotelValue]*Switch([OrderHotelCur]=1,1/[excDefPouToEur],[OrderHotelCur]=2,1,[OrderHotelCur]=3,1/[excDefPouToDol]),2) AS OrderHotelPou, Round([OrderFreightValue]*Switch([OrderFreightCur]=1,1/[excDefPouToDol],[OrderFreightCur]=2,1,[OrderFreightCur]=3,1/[excDefPouToEur]),2) AS OrderFreightPou, Round([OrderParkingCost]*Switch([OrderParkingCur]=1,1/[excDefPouToEur],[OrderParkingCur]=2,1,[OrderParkingCur]=3,1/[excDefPouToDol]),2) AS OrderParkingPou, FormatPercent(([OrderValPou]-([OrderPurchasePou]+[OrderLabourPou]+[OrderTravelPou]+[OrderHotelPou]+[OrderFreightPou]+[OrderParkingPou]+[OrderHandlingPou]))/[OrderValPou],2) AS OrderGrossMargin, tblContact.ContMobPhone, tblContact.ContPhone, tblContact.ContFax, tblContact_1.ContMobPhone, tblContact_1.ContPhone, tblContact_1.ContFax, tblContact_2.ContMobPhone, tblContact_2.ContPhone, tblContact_2.ContFax, "+" & [tblCountry_7]![CountryPhoneCode] & " " & [tblContact_2]![ContPhone] AS expCont2Phone, tblCountry_3.CountryPhoneCode, tblCountry_2.CountryPhoneCode, tblCountry_1.CountryPhoneCode, tblCountry.CountryPhoneCode, tblCountry_7.CountryPhoneCode, tblOrder.OrderDate, tblExcDefault.excDefPouToDol, tblExcDefault.excDefPouToEur, tblExcDefault.excDefEurToDol
FROM (((((((((((tblCurrency INNER JOIN ((((tblEmployee INNER JOIN ((((((((tblOrder INNER JOIN tblCompany ON tblOrder.CompContractID = tblCompany.CompID) INNER JOIN tblCountry ON tblCompany.CompCountryID = tblCountry.CountryID) INNER JOIN tblContact ON (tblCountry.CountryID = tblContact.ContCountryID) AND (tblOrder.ContrContactID = tblContact.ContactID)) INNER JOIN tblCompany AS tblCompany_1 ON tblOrder.CompSiteID = tblCompany_1.CompID) INNER JOIN tblCountry AS tblCountry_1 ON tblCompany_1.CompCountryID = tblCountry_1.CountryID) INNER JOIN tblContact AS tblContact_1 ON tblOrder.OnSiteContID = tblContact_1.ContactID) INNER JOIN tblCompany AS tblCompany_2 ON tblOrder.CompInvoiceID = tblCompany_2.CompID) INNER JOIN tblCountry AS tblCountry_2 ON tblCompany_2.CompCountryID = tblCountry_2.CountryID) ON tblEmployee.EmployeeId = tblOrder.[OrderEmployeeID]) INNER JOIN tblContact AS tblContact_2 ON tblOrder.CompInvoiContID = tblContact_2.ContactID) INNER JOIN tblCompany AS tblCompany_3 ON tblOrder.[OrdSoldByID] = tblCompany_3.CompID) INNER JOIN tblCountry AS tblCountry_3 ON tblCompany_3.CompCountryID = tblCountry_3.CountryID) ON tblCurrency.CurrencyID = tblOrder.OrderValueCurID) INNER JOIN tblCurrency AS tblCurrency_1 ON tblOrder.OrderPurchaseCur = tblCurrency_1.CurrencyID) INNER JOIN tblCurrency AS tblCurrency_2 ON tblOrder.OrderFreightCur = tblCurrency_2.CurrencyID) INNER JOIN tblCurrency AS tblCurrency_3 ON tblOrder.OrderLabourCur = tblCurrency_3.CurrencyID) INNER JOIN tblCurrency AS tblCurrency_4 ON tblOrder.OrderTravelCur = tblCurrency_4.CurrencyID) INNER JOIN tblCurrency AS tblCurrency_5 ON tblOrder.OrderHotelCur = tblCurrency_5.CurrencyID) INNER JOIN tblPaymentTerms ON tblOrder.PaymentTerms = tblPaymentTerms.PaymentTermsId) INNER JOIN tblCountry AS tblCountry_4 ON tblContact.ContCountryID = tblCountry_4.CountryID) INNER JOIN tblCountry AS tblCountry_5 ON tblContact.ContCountryID = tblCountry_5.CountryID) INNER JOIN tblCountry AS tblCountry_6 ON tblContact_1.ContCountryID = tblCountry_6.CountryID) INNER JOIN tblCountry AS tblCountry_7 ON tblContact_2.ContCountryID = tblCountry_7.CountryID) INNER JOIN tblExcDefault ON tblOrder.OrderExcDefaultID = tblExcDefault.[excDefExchangeID]
WHERE (((tblOrder.OrderId)=[TempVars]![temporderid]));

 

[arnelgp]

can you use Querydefinition:

dim qd as Dao.QueryDef

set qd = dbs.QueryDefs("queOrderSum")
set rst = qd.OpenRecordset(dbOpendynaset)
set qd=nothing

 

[mveijndh]

I get the erro
Object variable or block variable With not set??

 

[arnelgp]

the code is just a snippet, you must instanciate all object first:

dim db as dao.database
dim rst as dao.recordset
dim qd as dao.querydef

set db = currentdb
set qd = db.querydefs("queOrderSum")
set rst = qd.OpenRecordset(dbOpenDynaset)
set qd=Nothing
set db=Nothing

 

[mveijndh]

Sorry, Same error!

 

[Uncle Gizmo]

What happens if you do this:-

MsgBox " >>> " & [TempVars]![temporderid]

 

[mveijndh]

it provides:
>>> 6 as my answer. (That's what I'm looking for!!)

 

[Minty]

What happens if you substitute 6 in the query instead of the temp var?

 

[mveijndh]

Then it works fine!!
Problem is I need to parse the value before running to transfer to excel, otherwise I get all records and heve to split. As you can see the query is not just a simple one, so writing the sql as string runs into problems with length and parts, so I prefer the Tempvars. If it does not work what other optioin do I have to update the WHERE clause with the record value!!

 

[Uncle Gizmo]

Try:-

(((tblOrder.OrderId)=CInt([TempVars]![temporderid])))

 

[mveijndh]

no change unfortunately

 

[Uncle Gizmo]

Try:-

(((tblOrder.OrderId)=CInt([TempVars]![temporderid])))

Show the above, but with the 6 added that works....

The exact format you use when you call it.

How are you setting the TempVar? Show the code that sets the tempvar, and tell me when it is set.

 

[mveijndh]

Dim lgOrderId As Long
Dim strCurrentPath As String
Dim strFullCurPathFile As String
Dim strCurrentOrderDet As String
Dim strValue As String
Dim test As String
Dim strSQL1 As String
Dim intLenstr As Integer
StartUp
[TempVars]![temporderid] = Me!OrderId.Value
strFileName = Me!CAEJobNumber.Value
strCurrentPath = BrowseFolder_Scripting(strDefaultPath)
strFullCurPathFile = strCurrentPath & strFileName & ".xlsm"
Set Application.Printer = Application.Printers("Foxit Phantom Printer")
the Tempvars are set during startup:

Function StartUp()
strWindowUser = Environ("username")
strSQL1 = "SELECT tblDefault.DefaultID, tblDefault.DefaultUserId, tblDefault.DefaultDate, tblDefault.DefaultCurID, tblDefault.DefaultCountryID, " & _
" tblDefault.DefaultLabourCosts, tblDefault.DefaultPartMult, tblDefault.DefaultFileFolder, tblDefault.DefaultAccountingEmail, tblDefault.DefaultWindowUserName " & _
"FROM tblDefault " & _
"WHERE tblDefault.DefaultWindowUserName='" & strWindowUser & "';"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL1)
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
TempVars.Add "tempDefaultFolder", rst!DefaultFileFolder.Value
TempVars.Add "tempDefaultAccountingEmail", rst!DefaultAccountingEmail.Value
TempVars.Add "tempDefaultUser", rst!DefaultWindowUserName.Value
TempVars.Add "tempDefaultCurID", rst!DefaultCurID.Value
TempVars.Add "tempDefaultCountryID", rst!DefaultCountryID.Value
Else
msgBoxResult = MsgBox("No registred user!", vbOKOnly, "User ID")
Application.Quit
End If
StartUp = True
Set dbs = Nothing
Set rst = Nothing
End Function

strFile = TempVars!tempdefaultfile.Value

this line I use to write the data to the Excel file:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "queOrderSum", strFile, True

I do get the data, but unfiltered.
When I set the Tempvars in the Query it hicks up.

 

[Minty]

Have you tried Debug.Print [TempVars]![temporderid] to make sure it is being set?

 

[mveijndh]

I'm 100% sure the tempvars is set and available the moment the query is made.
Do you have a working example with tempvars in the query request?

 

[Uncle Gizmo]

I am on my PC now and I can see the extent of your query! You don't get an any idea how big it is from a mobile phone. The only thing I can suggest is that you copy all of your objects into a new database to remove the possibility of some corrupt VBA code stored somewhere, then, gradually rebuild your query until it breaks. Just keep adding sections in, and eventually it won't work, that will help you isolate where your problem is.

 

[Minty]

No - I don't use them I'm afraid, they didn't exist when I started out using VBA.
Just to make sure I would definitely do something to explicitly prove it is being set.

What if you simply refer to the Forms control in the query / devils advocate..

 

[Frothingslosh]

I am on my PC now and I can see the extent of your query! You don't get an any idea how big it is from a mobile phone. The only thing I can suggest is that you copy all of your objects into a new database to remove the possibility of some corrupt VBA code stored somewhere, then, gradually rebuild your query until it breaks. Just keep adding sections in, and eventually it won't work, that will help you isolate where your problem is.

Formatting that particular monstrosity will also help in getting it debugged.

 

[mveijndh]

Yep, I'm going to bypass all with a "Print Report" filed on the main table and add this to my query.
That way I can determine what report I'd like to print at any time.

Thanks all for the input, it's really appreciated!!

 

[mveijndh]

I've been keeping on trying and found the lower one did work fine. Meaning the principle works. This also means there most be an error in the query mentioned above.
Thanks very much for all the help.

strSQL1 = "SELECT tblOrder.OrderId, tblOrder.OrderPrint " & _
"FROM tblOrder " & _
"WHERE tblOrder.OrderId= " & [TempVars]![temporderID] & ";"

This works fine!!