Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with returning the correct recordcount from a recordset 2

Status
Not open for further replies.

jisque

MIS
Jul 16, 2003
13
GB
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, have tried your suggestion but get the following error message "No value given for one or more required parameters
 
Suppress some spaces:
Code:
rstMemRecs.Open "Select M_ID FROM tblSubscriptions WHERE M_ID = '" & Me.FindMember & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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:
Code:
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


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top