Recordset Open Error

[RayMilhon]

I have the following code in an Access 2010 Report

    Dim lpctr As Long
    Dim rs As New ADODB.Recordset
    Dim strsql As String, lngcomplete As Long, lngincomplete As Long
    lngcomplete = 0
    lngincomplete = 0
    strsql = "Select Status, mbrs from qry_COA_Total_Patients where pcpid = """ & _
        Me.PCPID & """;"
    rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    Do Until rs.EOF
        Select Case rs!Status
            Case Is = "Complete"
                lngcomplete = rs!mbrs
            Case Is = "Incomplete"
                lngincomplete = rs!mbrs
        End Select
    Loop

I'm getting a "No Value given for one or more required parameters" on the line of code that opens a record set the SQL is very simple If I plug it into a new query it runs perfectly but it won't run in code????

The Report source is a summation query of a detail query. The detail query lists all patients for 3 specific measures we are tracking. We also need to know how many individual patients are in these 3 measures as a patient could be in 1,2 or all 3. So I have a query to group the individual patients by status and pcp. The qry_COA_Total_Patients query then counts those patients by status. There are no parameters in any of the queries other than the where clause above.

Any help is appreciated.

[June7]

Does qry_COA_Total_Patients have dynamic parameters?

Review http://forums.aspfree.com/microsoft-...or-538826.html

[RayMilhon]

No it does not

Here's the SQL
Select pcpid,status,count(memberid) from qry_COA_mbrship_step_one group by pcpid,status

Here's the SQL for qry_COA_mbrship_step_one

Select pcp,status,memberid from qry_COA_Detail

and finally here's the SQL for qry_COA_Detail

Select * from dbo_V_is_HVVMG_MeasureData where measure = "COA"

[June7]

Is PCPID a text or number field?

Why 4 queries where one will work? Why two boolean variables?

Try:

strSQL = "SELECT pcpid, status, Count([memberid]) AS CountMemb FROM dbo_V_is_HVVMG_MeasureData WHERE measure='COA' AND PCPID='" & Me.PCPID & "' GROUP BY pcpid, status;"

[RayMilhon]

PCPID is a text field with mostly numeric data. That's what I inherited not what I would have picked. one query will not work due to the data. The same member could be in 1,2 or all 3 measures so it should still be counted for this purpose as 1 regardless. For example:

Dr Smith has 7 Patients All 4 need all of the measures 2 need 2 of the measures and 1 needs 1 measure. This results in 17 measures. putting it in 1 query gives me 17 not 7.

What Boolean variables. There are none. unless that's where I'm messing up. and just don't see them.