Adding a counter to a list box filtered display 2

[rew2009]

I am pretty much a beginner with VB but have a access form with a table and a filtered List Box to show only some of the records in the table. How do I add a counter to show the number of records pulled up by the filtered list box. I’ve looked at “count” and “dcount” but can’t figure how to put code into the form to use these. This is the code that I use in a command button to filter the result in the list box named "ListBoxClientName".

Dim strSearch As String

strSearch = InputBox("Enter Client Name")
strSearch = "*" & strSearch & "*"

If Not IsNull(strSearch) Then
strSQL = "SELECT ID, ClientID, ClientName,ZipCode FROM tblClients " & _
"WHERE ClientName LIKE '" & strSearch & "*'" & _
" ORDER BY ClientName "

Me.ListBoxClientName.RowSource = strSQL
End If


I could either display the result in a column in the list box or a text box adjacent to it. (Unless there is a better way). But I’m not sure how to build in the code. Thanks Russ

 

[RuralGuy]

A ListBox has a .ListCount property that returns the number of rows in the RowSource.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[rew2009]

I don't find that in the property box that opens up in design view. If it is a vb function, what would the associated code be to tie it to a text box to display it and where would I insert it into the form or listbox code?

Thanks Russ

 

[RuralGuy]

A ControlSource of:
=Me.YourListBoxName.ListCount
...should give you something, using YourListBoxName of course.


(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[PHV]

In the Click event procedure of your button:

strSearch = InputBox("Enter Client Name")
strSearch = "*" & strSearch & "*"
strSQL = "SELECT ID, ClientID, ClientName,ZipCode FROM tblClients " & _
    "WHERE ClientName LIKE '" & strSearch & "*'" & _
    " ORDER BY ClientName  "
Me.ListBoxClientName.RowSource = strSQL
Me.yourTextBox.Value = Me.ListBoxClientName.ListCount

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

 

[rew2009]

Thank you guys, it works. One minor thing, it gives me a value of one more than the actual count, ie, if there is only one record it displays "2" and so on. I corrected it with:
Me.CountBox.Value = Me.ListBoxZipCode.ListCount-1,

not a big thing, but I wonder why it does that. Anyway, Thanks Russ

 

[TheAceMan1]

How are ya rew2009 . . .

[blue]not a big thing, but I wonder why it does that . . .[/blue]

[blue]If you set the ColumnHeads property to Yes, [purple]the row of column headings is included in the number of rows returned by the ListCount property.[/purple][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[rew2009]

Thanks, that's the answer, Russ