Problem with returning the correct recordcount from a recordset 2

[jisque]

I have a Subscription form in a Gym membership database. When the member renews their subscription, the user uses a drop down list box to select the member name and enter their on the subscription form. Then, I would like to count first the number of times the member’s id appears in the subscriptions table, so I that I can use that number to generate a new subscription_id for the member. The code I have used is as follows:

Private Sub FindMember_AfterUpdate()
Dim rstMemRecs As ADODB.Recordset
Set rstMemRecs = New ADODB.Recordset
rstMemRecs.ActiveConnection = CurrentProject.Connection
rstMemRecs.CursorType = adOpenStatic
rstMemRecs.Open "Select M_ID FROM tblSubscriptions WHERE M_ID = ' " & Me.FindMember & " ' "
rstMemRecs.MoveLast
Debug.Print rstMemRecs.RecordCount

I get a record count of zero even though I know there are several occurrences of particular ids in the subscriptions table.

However when I open the recordset with the following:
rstMemRecs.Open "Select M_ID FROM tblSubscriptions”

I get a full record count of the records in the table. Greatly appreciate any help.

 

[PWise]

try
rstMemRecs.Open "Select M_ID FROM tblSubscriptions WHERE M_ID = " & Me.FindMember

 

[jisque]

pwise, have tried your suggestion but get the following error message "No value given for one or more required parameters

 

[PHV]

Suppress some spaces:

rstMemRecs.Open "Select M_ID FROM tblSubscriptions WHERE M_ID = '" & Me.FindMember & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

Good eyes, PHV! I didn't see the spaces until I copied and pasted the OP's code.

Might I suggest an alternative that avoids the flakey RecordCount property:

rstMemRecs.Open "Select Count(*) As SubscriptionCount FROM tblSubscriptions WHERE M_ID = '" & Me.FindMember & "'"

If Not (rstMemRecs.BOF And rstMemRecs.EOF) Then
  Debug.Print rstMemRecs("SubscriptionCount") & " records"
Else
  Debug.Print "0 records"
End If

 

[jisque]

Thanks everyone for your very valuable support