Recordset and Recordcount help

[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

 

[Douglas J. Steele]

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

 

[gjtired]

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

 

[BruceM]

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.

 

[Rastro]

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

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.

 

[BruceM]

You would still need to reference the Form property of the subform control:
Forms![frmRegistrationSel]![sbfrmRegistrationSelSub].Form![RegID]

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

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.

 

[gjtired]

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

 

[Rastro]

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