i have search box,listview and 3 labelcaption namely: fname,lname and total.
my database name is students and table name attendance.
if i search particular student and display in listview, once i click the name on the listview automatically display on the labels, and display total number of records of that particular student on labeltotal.
the problem i dont know how to count record on a particular field.

please need a hand on your experties guys.
this is really important for me.

Recommended Answers

All 7 Replies

Use a for loop to get every field value and add it to a counter total -

Dim xRecords As Integer, xTotal As Integer ''Assuming that you are trying to get a number total!!

xTotal = 0

For xRecords = 0 To rs.RecordCount - 1 ''Or however many records were returned...
    ''Also assuming that your record selector is called rs...
    xTotal = xTotal + rs!TheFieldNameHere

    rs.MoveNext
Next xRecords

lTotal.Caption = xTotal

where am i going to put this code?

Show me the code you are using to show the label captions when clicking on listview.

The code will go in there. Paste it here and I will add my part.

i changed my post above,this is what im working now.

i have dbname (data.mdb) and tables ('students' and 'attendance') a listview and searchbox

i add student attendance by 0-for absent and 1-for present
i want to search student from attendance table the number of absent, then display the students name and the total number of absent on listview.

cause its like i want to determine students who have already 3 absences.
if you dont get my point, please just specify.

i really need your help.
i dont have any idea how to code this one.
thanks a lot.

You will do something like this -

Private Sub cmdSearch.Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data.mdb;Persist Security Info=False"
''make sure it is in your folder where your app is saved!!    
cn.Open

Set rs = New ADODB.Recordset
rs.Open ("SELECT * FROM attendance WHERE absent => '0'), con, adOpenDynamic, adLockOptimistic

If rs.EOF = True Or rs.BOF = True Then
    MsgBox "No record found"

    Exit Sub
        Else
    Set ListView1.Datasource = rs

    Dim xRecords As Integer, xTotal As Integer ''Assuming that you are trying to get a number total!!
    xTotal = 0

    For xRecords = 0 To rs.RecordCount - 1 ''Or however many records were returned...
    ''Also assuming that your record selector is called rs...
    xTotal = xTotal + rs!absent
    rs.MoveNext
Next xRecords

lTotal.Caption = xTotal
End Sub

thanks a lot AndreRet.

Only a pleasure. Happy coding.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.