1,105,409 Community Members

count record from particular field and display total number in labelcaption

Member Avatar
vonnoy
Light Poster
31 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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
Member Avatar
vonnoy
Light Poster
31 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

where am i going to put this code?

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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.

Member Avatar
vonnoy
Light Poster
31 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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
Member Avatar
vonnoy
Light Poster
31 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

thanks a lot AndreRet.

Question Answered as of 1 Year Ago by AndreRet
Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

Only a pleasure. Happy coding.

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article