Count Records behind a Recordset Form

[Jamesy_007]

Hi everyone, it's been a while



I was looking back at some codes and practicing some vba. I have this book Tips and tricks from MvPs and I decided to build a ultra-fast searching form. It took me a while to try and understand and get the code to work as I constantly stress, I truly love MS Access but am not a programmer; only aspiring . I have this search form with a search textbox and Listbox type objects. When you type in a few characters of someone's name into the search box and press enter the list box fills with a Recordset of names from the db or table [tblEmployees]. Great! works fine.

However, I placed also another unbound txtbox field where I want it to display the number# of records that displays after the search in the ListBox. I tried placing codes and expressions in the AfterUpdate event. I just can't get this to work. What exactly am I doing wrong because this form object [frmSearch] is tied to another form and a module, which enables it to function properly. How to get the number# of records in a Recordset where the form display only one(1) in the record selectors. Note also, even when multiply records pull up after searching names the record selectors says (1). Say I search ("be") and (25) records pull into the list box. I want a textbox to display this (25). I have tried these codes...

Function FindRecordCount(strSQL As String) As Long  On Error GoTo Error_Handler
  
Dim db As Database
Dim rstRecords As Recordset
   Set db = CurrentDb
   Set rstRecords = db.OpenRecordset(strSQL)
   If rstRecords.EOF Then
      FindRecordCount = 0
   Else
      rstRecords.MoveLast
      FindRecordCount = rstRecords.RecordCount
   End If
   rstRecords.Close
   db.Close
   Set rstRecords = Nothing
   Set db = Nothing
     
Exit_Here:
Exit Function


Error_Handler:
  MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
 Resume Exit_Here
End Function

         Sub RecordCountX()
     
     Dim dbs As Database
     Dim rstEmployess As Recordset
     
     Set dbs = OpenDatabase("Database Management Practice db.accdb")
     
     With dbs


     ' Show the RecordCount property after populating the
     ' Recordset.
     rstEmployess.MoveLast
     Debug.Print "Dynaset-type recordset " & _
     "from tblEmployees table after MoveLast"
     Debug.Print " RecordCount = " & _
     rstEmployees.RecordCount
     rstEmployees.Close
 
     .Close
     End With

[davegri]

Try calling your first example from the form_current event.

[June7]

I tested function by calling from Immediate Window and texbox. It works.

Show code that calls function.

[Jamesy_007]

I tested function by calling from Immediate Window and texbox. It works.

Show code that calls function.

Really ok... am not sure what I am programming wrong..

This is the stand alone Module tied to the Employees form below

Public glngIDSelect As Long

Public Function fGetID() As Long
On Error GoTo Error_Handler
  glngIDSelect = 0
  DoCmd.OpenForm "frmSearchEmployees"


  Do While glngIDSelect = 0
    DoEvents
   Loop
   If glngIDSelect = -1 Then
   glngIDSelect = 0
   End If
   fGetID = glngIDSelect


Exit_Here:
Exit Function


Error_Handler:
 MsgBox Err.Number & " : " & Err.Description
 Resume Exit_Here
End Function

And also, note this is a button on the employees form that opens the Search-Form, but remember everything at this point works... what I am interested in is a unbound txt box that will display the number# of employees after the strCriteria in searching

Private Sub btnEmpSearch_Click()  On Error GoTo Error_Handler
  
  Dim lngID As Long
  Dim rst As DAO.Recordset
  Dim db As DAO.Database
  Dim strCriteria As String
   
   lngID = fGetID ' Function call from mdlSearch
   
   If lngID <> 0 Then
   
   Set db = CurrentDb
   Set rst = Me.RecordsetClone
   strCriteria = "[ID] =" & lngID
   
   rst.FindFirst strCriteria
   If Not rst.NoMatch Then
   Me.Bookmark = rst.Bookmark
   Else
    MsgBox "Employees Not Found"
   End If
   
     Me.Refresh
   End If
   
Exit_Here:
   On Error Resume Next
   rst.Close
   db.Close
   
   Set rst = Nothing
   Set db = Nothing
   Exit Sub
    
Error_Handler:
   MsgBox Err.Number & ": " & Err.Description
   Resume Exit_Here
   
   End Sub

I really hope you guys can help because I still don't fully understand Recordsets. Got it from one of my books

[davegri]

If all you want is to see the number of rows in the listbox, it's pretty simple, and has nothing to do with the form's recordsource.
txtListcount is the textbox on the form to display the count.
mylistbox is the name of the listbox.

However, you have not shown the code that creates the rowsource for the listbox. The below line would be incorporated into that code.

txtListcount = me.mylistbox.listcount

[June7]

I knew had to be something simpler. Can just put expression in textbox Control Source: =mylistbox.ListCount

And nowhere do I see function FindRecordCount called but don't need it anyway.

[Jamesy_007]

If all you want is to see the number of rows in the listbox, it's pretty simple, and has nothing to do with the form's recordsource.
txtListcount is the textbox on the form to display the count.
mylistbox is the name of the listbox.

txtListcount = me.mylistbox.listcount

Yes!, Thanks davegri and June7 this works (=[LstResults].
[ListCount]) as an expression bound by the Control Source


Why I kept placing codes in the AfterUpdate event I don't know, also the first two functions from first thread were placed as Afterupdate, I have just one little peeve ... When the search form loads the same unbound text box [txtResultsNo] displays the amount of records as (1) before a search is submitted ... What if I want it to say 0 or null

[June7]

Use VBA to set value of textbox instead of expression in ControlSource.