OpenRecordset problem

[CBrighton]

I've just tried it again.

Query:

SELECT tblTeamFigures.TeamID, 
tblTeams.TeamName, 
tblTeamFigures.TaskID, 
tblTasks.TaskName, 
tblTeamFigures.FigureDate, 
tblTasks.TaskTiming, 
tblTeamFigures.BroughtForward, 
tblTeamFigures.NewIn, 
tblTeamFigures.NumberWorked
FROM tblTeams I
NNER JOIN (tblTasks 
INNER JOIN tblTeamFigures 
ON tblTasks.TaskID = tblTeamFigures.TaskID) 
ON tblTeams.TeamID = tblTeamFigures.TeamID
WHERE (((tblTeamFigures.TeamID)=[Forms]![frmTeamSelect]![cboTeam]) 
AND ((tblTeamFigures.FigureDate)=[Forms]![frmTeamSelect]![cboDate]));

VBA:

Dim db As Database
Set db = CurrentDb
Dim rst As Recordset
Set rst = db.OpenRecordset("qryNewIn")

Result:

Run-time error '3061':

Too few parameters. Expected 2.

The query works fine (it's the recordsource of another form and also works when viewed) but trying to open it via OpenRecordset in Access 2003 VBA gives an error.

 

[vbaInet]

The problem is with the way you're opening the recordset.

Replace your code with this:

Dim rst As DAO.Recordset, qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("qryNewIn")
qdf.Parameters("[Forms]![frmTeamSelect]![cboTeam]") = Forms!frmTeamSelect!cboTeam
qdf.Parameters("[Forms]![frmTeamSelect]![cboDate]") = Forms!frmTeamSelect!cboDate

Set rst = qdf.OpenRecordset

 

[CBrighton]

Ah, thanks.

I've used QueryDef's before but only for action queries rather than seelct queries.

I'll keep this in mind, but is there any advantage in doing this rather than using my current method of copying the SQL into the VBA recordset? By which I suppose I mean does it work quicker on a saved query than SQL written into the recordset?

 

[gemma-the-husky]

try this instead, if you haven't fixed it yet.

strSQL = "SELECT * FROM PriceList WHERE PriceList.ListID = " & Me![List33] & " And PriceList.[Valid] = " & True

 

[LPurvis]

By
>> copying the SQL into the VBA recordset
do you mean hard coding the SQL in VBA and opening a recordset based on that?

If you're duplicating the query definition in code then, IMO, that hard coded VBA string is limiting you as opposed to using a dedicated, reusable query object.
There's no need to go through replacing all calls to use an explicit querydef object. Just use a function you can call each time instead of OpenRecordset.
For example, time to wheel out the old standard, such as fDAOGenericRst in this thread.

And in passing reference to the earlier mentioning of
>> SQL backend the bools are treated differently. I wonder how it will interpret Nulls if the field was wrapped in Nz().

Jet, via the ODBC provider, takes the responsability for evaluating local function calls before it is SQL neutralised by ODBC.
If you perform that function call (such as Nz) on a field for evaluation, then if there is no ODBC equivalent to be passed to the server then you get the dreaded fetch of all match rows that could be parsed.
So SELECT * FROM TableName WHERE BoolField = Nz(Forms!Form1!Control1, 123)
is resolved and passed to the server as
SELECT * FROM TableName WHERE BoolField = 123
(or whatever).
However
SELECT * FROM TableName WHERE Nz(BoolField, 123) = Forms!Form1!Control1
would require at least a full index scan on that field (which is unlikely) more like a table scan. Then Jet filters the rows on the client. It's about careful syntax formation and function selection.

What was the question again? lol

Cheers.

 

[micks55]

Dave, Thanks for the reply, will try it.
Currently having major probs logging in to forum. Managed this time with Firefox but its very hit and miss. Mike