Add subform record count to text box?

[uscarioca]

Private Sub cmdSearch_Click()
Me.Refresh
Me.txtRegInfo = "Stores are in Region " & cmbRegion & " (" & cmbState & ")"
End Sub

Can someone help me understand how I can insert a record count variable from a subform qry? My qry populating my subform is called qryPL_Store_List. Lets say qryPL_Store_List has 37 records, I would want txtRegInfo = "37 Stores are in Region 26 (MO)".

Please note that I have got the cmbRegion and cmbState variables to work in the text box. I am stumped on the getting the record count.

Any help would be appreciated.
Thanks Chris

 

[timothybard]

You can try something like this:

  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset
  Dim recordCount As Long

  rst.Open Me.subform.Form.RecordSource, cnn
  While Not rst.EOF
    rst.MoveNext
    recordCount = recordCount + 1
  Wend
  rst.Close
  
  Me.txtRegInfo= recordCount & " Stores are in Region " & cmbRegion & " (" & cmbState & ")"

Replace 'subform' with the name of your subform.