VBA Query Problem (1 Viewer)

[nelslynn]

Sorry, I have looked for hours on this forum and cannot figure out what is going on.

Why do I get an error of "Too few Parameters, expecting 2"

My query "qryRecCt" (saved as an Access query) queries a recordset based on a form that is open and clearly gives the parameters needed >> form!frmCompetitor.txtYear and form!frmCompetitor.CompetitorID. Before showing a subform at the footer, I want to allow additions based on the number of records in the recordset of the subform.

I have this as the open_event for the subform:

Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("qryRecCt")

... and I get the "Too few parameters" error

I've also tried to construct the query in a string with the following:

strsql = "Select..."
set r = CurrentDb.OpenRecordset(strsql)
If r.Recordcount >0 then
....
else
endif

For some crazy reason the recordcount is off. If file through the records with:

r.movefirst
loop
str = r.fields(1)
r.movenext
loop

... etc and this works fine, it won't go past the number of records that the query is suppose to produce. In other words it gives me a recordcount of 3 but will only show 1 record when I try to loop through.

I'm at wits end. Can anyone help??

Should I be using ADO... I will be eventually using with with XP?

 

[Rich]

subForm_Load
If Me.RecordsetClone.RecordCount >0 Then
Do Whatever

 

[nelslynn]

Another problem

First of all thanks for the reply, but I discovered I need to go about this in a different way. First of all, I have a combo box of names at the top of the main form that is linked to the subform. The subForm_Load event will trigger on the first record, but when I change the name in the combo box, I need to check the recordcount based on the new name.

Also, I want the ability to add ONLY one record on the subform if the recordcount = 0.

Am I clear as mud?

Could someone please explain how to find the recordcount of a query that is saved in Access. Why do I get "Too few Parameters" error with Set r = CurrentDb.OpenRecordset("qryRecCt") ?

 

[Jon K]

You can't use CurrentDb.OpenRecordset on a parameter query.

Pass the values to the parameters before opening the recordset. Use MoveLast to get the correct RecordCount.

Dim db As DAO.Database
Dim r As DAO.Recordset
Dim qDef As DAO.QueryDef

Set db = CurrentDb
Set qDef = db.QueryDefs("qryRecCt")

' pass value to the first parameter in the query.
qDef.Parameters(0) = form!frmCompetitor!txtYear

' pass value to the second parameter in the query.
qdef.parameters(1) = form!frmCompetitor!CompetitorID

Set r = qDef.OpenRecordset

r.MoveLast
MsgBox r.RecordCount