Unable to open a query as a recordset

[Petrol]

I have a query 'qry_LOI2' whose SQL is as follows:

 SELECT qry_LOI.*, "Para 5" AS Para5 FROM
qry_LOI INNER JOIN LOIPayerText ON qry_LOI.WhoPays = LOIPayerText.Wh oPays;

(It did have a lot more fields, but I have removed most of them during debugging to try to isolate the problem). It opens successfully by double-clicking it in the the GUI Navigation pane, but when I try to open it as a recordset in VBA it fails with Error 3061 ("Too few parameters"). The VBA line is

Set rsLOI2 = CurrentDb.OpenRecordset("qry_LOI2", dbOpenDynaset)

What could cause this?
(The query that it uses, 'qry_LOI', is as follows:

 SELECT
qry_Applicant.P ersonID,
qry_Applicant.[Name_FN-SN],
qry_Applicant.T itle,
qry_Applicant.F irst_name,
qry_Applicant.S urname,
qry_Applicant.D ate_last_invite d,
DateDiff("d", [Date_last_invit ed], Date()) AS Days_since_last _invited,
qry_Applicant.R esponse,
qry_Applicant.G ender,
qry_Applicant.E ventCode,
qry_NextWalks.E ventType,
qry_Applicant.D ate_of_Birth,
qry_Applicant.S treet,
qry_Applicant.S uburb_text,
qry_Applicant.H ome_phone,
qry_Applicant.S ilent,
qry_Applicant.M obile,
qry_Applicant.E mail,
qry_Applicant.S uppress_contact _info,
qry_Applicant.C hurch_NDS,
qry_NextWalks.N ext_Walk,
Venue ([qry_NextWalks].[Next_Walk]) AS Venue_name,
qry_NextWalks.V enueAddress,
qry_NextWalks.C lightVenue,
qry_NextWalks.C lightAddress,
qry_NextWalks.S tartDate,
qry_NextWalks.S TC,
qry_NextWalks.C omments,
qry_Applicant.W ho_pays,
Switch (
[Who_pays] = "P",
"Pilgrim",
[Who_Pays] = "S",
"Sponsor",
[Who_pays] = "B",
"Both",
[Who_pays] = "C",
"Community"
) AS WhoPays,
qry_Applicant.F ees_payable,
qry_Applicant.F ees_received,
qry_Applicant.S ponsor,
qry_Applicant.S ponsorEmail,
[qry_NextWalks].[Next_Walk] & "/P" & [PersonID] & "-" & [Surname] AS RefNo,
qry_OfficeBeare r.Name_FnSn AS OfficeBearer,
qry_OfficeBeare r.OfficeBearers Title,
qry_OfficeBeare r.OfficeBearers Street,
qry_OfficeBeare r.OfficeBearers Suburb,
qry_OfficeBeare r.OfficeBearers Email,
EventOffset ([qry_Applicant].[EventCode]) AS
OFFSET
,
qry_NextWalks.R egistrar
FROM
qry_Applicant
INNER JOIN (
qry_NextWalks
INNER JOIN qry_OfficeBeare r ON qry_NextWalks.R egistrar = qry_OfficeBeare r.Position_Titl e
) ON qry_Applicant.E ventCode = qry_NextWalks.E ventCode
WHERE
(
((qry_Applicant .PersonID) > 1)
AND (
(DateDiff("d", [Date_last_invit ed], Date())) > 60
OR (DateDiff("d", [Date_last_invit ed], Date())) IS NULL
)
AND (
(qry_Applicant. Response) IN ("D", "E", "F", "W")
OR (qry_Applicant. Response) IS NULL
)
AND ((qry_Applicant .Gender) IS NOT NULL)
AND ((qry_NextWalks .Next_Walk) IS NOT NULL)
AND ((qry_Applicant .Who_pays) IS NOT NULL)
)
ORDER BY
qry_Applicant.G ender DESC;

(The stray whitespaces scattered through the above code are not there in the original).
As well as removing most of the fields from qry_LOI2, I have also tried rebuilding it frrom scratch, but that didn't help.

[NeoPa]

My best guess is that it's related to using `CurrentDb()` directly - which is highly contra-indicated.

Try instead :

Dim dbMy As DAO.Database
Set dbMy As CurrentDb()
Set rsLOI2 = dbMy.OpenRecordset("qry_LOI2", dbOpenDynaset)

This only helps if that's your problem, & I don't know that. However, I see nothing else here so hopefully that's it & gets fixed.

[Petrol]

Thanks for the suggestion. I made that change, but unfortunately it didn't help.
A quick scan of my project reveals that I have used the 'CurrentDb.' syntax scores of times, and also the 'Set db=CurrentDb()' syntax in scores of other instances. All of them seem to work well and have been working well for years. While I take your point about best practice for the future, do you think it would be worth the effort of changing all the former instances now?

[Petrol]

Hmm, new information has just come to light.
I ran the same test (Open from GUI, open in vba as recordset) on the antecedent query, 'qry_LOI', and the vba failed in the same way ('Too few parameters'). So then I repeated the process on the three antecedents of qry_LOI, and one of them failed. It was qry_OfficeBeare r, whose SQL code is

  SELECT Positions.Position_Title, People.[Name_FN-SN] AS Name_FnSn,
  People.First_name, Positions.Description AS OfficeBearersTitle,
  Board_service_history.Calendar_year, [Street] & ", " AS OfficeBearersStreet,
  [Suburb_name] & " " & [Suburbs].[State] & " " & [Postcode] AS OfficeBearersSuburb,
  People.Email AS OfficeBearersEmail, Board_service_history.CommunityCode
  FROM ((Board_service_history
   INNER JOIN People ON Board_service_history.PersonID = People.PersonID)
   INNER JOIN Positions ON Board_service_history.Posn_ID = Positions.Posn_ID)
   INNER JOIN Suburbs ON People.Suburb_ID = Suburbs.Suburb_ID
  WHERE (((Board_service_history.Calendar_year)=Year(Now()))
  AND ((Board_service_history.CommunityCode)=[TempVars]![CommunityCode]));

The antecedents of qry_OfficeBeare r are all tables, and all open OK as recordsets.
So the problem is the same, but the origin of the problem is presumably this qry_OfficeBeare r. But I can't see anything wrong with it, and as I said, it opens OK on screen.

[NeoPa]

That's a deeper question.
There are issues with repeatedly building on CurrentDb() as each reference is to the result of a function that returns a DAO.Database object. No two calls to CurrentDb() ever return the same object. It's always a copy of the database with updated Collections.
That points to where using a cached copy in a DAO.Database object can leave you with stale Collections if you don't call .Refresh() where necessary. So, repeated CurrentDb() calls can waste a lot of memory & jumble up what you do use, but caching can sometimes leave you with stale data (For instance if you delete TableDefs without calling .Refresh() then .TableDefs() will still reflect the before state).

[Petrol]

OK, I'll bear that in mind in future. Thanks.

[Petrol]

Further investigation discloses a surprising and apparently undocumented limitation. It appears that you can use TempVars as criteria in a query ... but only if you open the query from the GUI. If you open it in VBA, you get the "Too few parameters" error (3061).
Is this a known limitation? Is it documented anywhere? I couldn't find any such suggestion in Microsoft Learn.
Instance:

 SELECT Walks.WalkNumbe r, Walks.StartDate
FROM Walks
WHERE (((Walks.WalkNu mber)=[Tempvars]![SelectedEvent]));

runs when clicked in Nav pane, but not from OpenRecordset.
(TempVars!Selec tedEvent = "BS115", which is a value for WalkNumber in one of the records.)
Any suggestions how I can get around it?

[NeoPa]

None that are simple Petrol :-(
I typically avoid TempVars generally, but if you had a table defined to store a single record & set Fields in that table (just the one in this case) to match where you need to inject your data then you could link to that table to get the value you require.
So, your recent SQL would look something like this instead :

SELECT [WalkNumber]
     , [StartDate]
FROM   [Walks]
WHERE  ([WalkNumber]=[tblMatch].[SelectedEvent]);

[Petrol]

Even that isn't as simple as it may appear, NeoPa, because it still leaves me with the problem of getting the TempVar into the table.
However, I solved the problem by using the TempVar in a wrapper function and calling that in the SQL.

I'm interested in your comment that you generally avoid TempVars. I knew nothing about them until last year, when as you'll recall, somebody (Crystal?) in the SF Bay Area user group suggested using them instead of globals, to avoid the loss of content when an untrapped error occurs. So I converted all my globals throughout the project to TempVars! :-(.
Anyway, that's done now and the immediate problem is fixed, but I am still annoyed that there seems to be an undocumented restriction on using TempVars in queries opened from code. Everything I can find online assures me you can use TV in queries and doesn't mention the restriction.
Ah well, we live and learn.

[NeoPa]

I wouldn't suggest the work-around I suggested was simple or straightforward Peter, in fact I specifically called out none was.

However, I should have thought of the wrapper function idea. That actually is relatively straightforward to be fair (& quite common - I just had a brain-lapse).

I guess I tend not to use the TVs mainly because I was working without them for so long & had solutions based on not requiring them (Shame I couldn't bring that one to mind earlier really). I wasn't aware of that specific limitation.

[Petrol]

Is the restriction something that should be brought to Microsoft's attention? If not to fix it, at least to document it to save others wasting a week as I have.