Problem to display no. of records from the listbox.

[awinnn]

Hi,
I have combobox (cboCategory), listbox (lstVehicleInfo) and textbox (txtTotal). When i select any item on the combobox, it will display the relevant category on the listbox. It works except for displaying the no. of records on the txtTotal.

Private Sub cboCategory_AfterUpdate()

Dim strSQL As String, strOrder As String, strWhere As 
           String
Dim str As String, strWhe As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT ID, VehicleNo, Model, Status, Category 
         FROM tblVehicle"

strWhere = "WHERE"

strOrder = "ORDER BY ID;"


If Not IsNull(Me.cboCategory) Then 
  strWhere = strWhere & " (Category) Like '*" & 
             Me.cboCategory & "*'  AND" 
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Me.lstVehicleInfo.RowSource = strSQL & " " & strWhere & "" 
                              & strOrder

str = "SELECT COUNT(ID) as TotID FROM tblVehicle"

strWhe = "WHERE"

If Not IsNull(Me.cboCategory) Then 
   strWhe = strWhe & " (Category) Like '*" &   
            Me.cboCategory & "*'  AND" 
End If

Me.txtTotal = str!TotID

End Sub

the error is in this code,
Me.txtTotal = str!TotID

any idea? thanx in advance..

 

[RoyVidar]

You have declared str as a string (which you shouldn't since 'Str' also is a function). Strings doesn't have any properties or methods, only the value ("Select Count(id)...").

To retrieve a count from some table, you need to for instance use the domain aggregate function DCount, or open a recordset based on the string you store in the string variable.

Roy-Vidar