Recordset and Recordcount help

  • Thread starter Thread starter gjtired
  • Start date Start date
G

gjtired

I have an access 2003 database with a SQL Server backend. I’m trying
to run a query to get a record count. It will either return 1 row or
0. I need to know which it is.

Here is the query:

SELECT Count(tblCSP_Participant.RegID) AS CountOfRegID
FROM tblCSP_Participant
WHERE (((tblCSP_Participant.RegID)= "" & [Forms]![frmRegistrationSel]!
[sbfrmRegistrationSelSub]!RegID & “”) AND
((tblCSP_Participant.Edit_Date)=(select Max(Edit_Date)
from tblCSP_Participant AS b
where b.RegID = tblCSP_Participant.RegID)));

When I get to this line:

Set rs = qd.OpenRecordset()

I get the error:” too few parameters: Expected 1”

If I hard code the RegID in the query instead of [Forms]!
[frmRegistrationSel]![sbfrmRegistrationSelSub]! I don’t get that
error.

Can someone please help.

Thanks
 
The appropriate way to refer to a control on a subform is:

Forms![NameOfParentForm]![NameOfSubformControl].Form![NameOfControlOnSubform]

Note that depending on how you added the subform to the parent form, the
name of the subform control may be different than the name of the form being
used as a subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an access 2003 database with a SQL Server backend. I’m trying
to run a query to get a record count. It will either return 1 row or
0. I need to know which it is.

Here is the query:

SELECT Count(tblCSP_Participant.RegID) AS CountOfRegID
FROM tblCSP_Participant
WHERE (((tblCSP_Participant.RegID)= "" & [Forms]![frmRegistrationSel]!
[sbfrmRegistrationSelSub]!RegID & “”) AND
((tblCSP_Participant.Edit_Date)=(select Max(Edit_Date)
from tblCSP_Participant AS b
where b.RegID = tblCSP_Participant.RegID)));

When I get to this line:

Set rs = qd.OpenRecordset()

I get the error:” too few parameters: Expected 1”

If I hard code the RegID in the query instead of [Forms]!
[frmRegistrationSel]![sbfrmRegistrationSelSub]! I don’t get that
error.

Can someone please help.

Thanks
 
I changed my query to refer to the control like you said, but I still
get the same error.
 
That means you would have this:
Forms![frmRegistrationSel]![sbfrmRegistrationSelSub].Form!RegID
You would not use quotes and ampersands in a query. It's just:
= [Forms]![frmRegistrationSel} etc.

I changed my query to refer to the control like you said, but I still
get the same error.
 
First of all I think you got unnecesary quotes:
WHERE tblCSP_Participant.RegID = "" &
[Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub]!RegID & ""
instead of this:
WHERE tblCSP_Participant.RegID =
[Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub]!RegID

I will do it this way:

SELECT Count(tblCSP_Participant.RegID) AS CountOfRegID
FROM tblCSP_Participant
WHERE (((tblCSP_Participant.RegID) =
[Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub]!RegID)
AND ((tblCSP_Participant.Edit_Date) = (SELECT Max(Edit_Date) as MaxEditDate
FROM tblCSP_Participant AS b
WHERE b.RegID = tblCSP_Participant.RegID))) ;

I tried a similar and it works
Rastro



gjtired said:
I changed my query to refer to the control like you said, but I still
get the same error.

The appropriate way to refer to a control on a subform is:

Forms![NameOfParentForm]![NameOfSubformControl].Form![NameOfControlOnSubfor­m]

Note that depending on how you added the subform to the parent form, the
name of the subform control may be different than the name of the form
being
used as a subform.
Click to expand...
Click to expand...
 
You would still need to reference the Form property of the subform control:
Forms![frmRegistrationSel]![sbfrmRegistrationSelSub].Form![RegID]

Rastro said:
First of all I think you got unnecesary quotes:
WHERE tblCSP_Participant.RegID = "" &
[Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub]!RegID & ""
instead of this:
WHERE tblCSP_Participant.RegID =
[Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub]!RegID

I will do it this way:

SELECT Count(tblCSP_Participant.RegID) AS CountOfRegID
FROM tblCSP_Participant
WHERE (((tblCSP_Participant.RegID) =
[Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub]!RegID)
AND ((tblCSP_Participant.Edit_Date) = (SELECT Max(Edit_Date) as
MaxEditDate
FROM tblCSP_Participant AS b
WHERE b.RegID = tblCSP_Participant.RegID))) ;

I tried a similar and it works
Rastro



gjtired said:
I changed my query to refer to the control like you said, but I still
get the same error.

The appropriate way to refer to a control on a subform is:

Forms![NameOfParentForm]![NameOfSubformControl].Form![NameOfControlOnSubfor­m]

Note that depending on how you added the subform to the parent form, the
name of the subform control may be different than the name of the form
being
used as a subform.
Click to expand...
Click to expand...
Click to expand...
 
I changed the query so the access of the form is correct like everyone
told me. When I run the query in the query window it runs correct.
When I run it through code I still get the same error.

Set rs = qd.OpenRecordset()


I get the error:” too few parameters: Expected 1”


thanks
 
If "RegID" on the subform is the problem (I think it is) try to obtain it by
other way:
For ex.:
intRegID=Forms("frmRegistrationSel").Controls.("sbfrmRegistrationSelSub").Controls("RegID")

strSQL= "SELECT Count(tblCSP_Participant.RegID) AS CountOfRegID" & _
" FROM tblCSP_Participant" & _
" WHERE (((tblCSP_Participant.RegID) = " & _
intRegID & _
") AND ((tblCSP_Participant.Edit_Date) = (SELECT Max(Edit_Date) as" & _
" MaxEditDate FROM tblCSP_Participant AS b" & _
" WHERE b.RegID = tblCSP_Participant.RegID))) ;"

Rastro
 
Back
Top