DLook up returning multiple records - Help (1 Viewer)

[monk_1987]

I have a continuous form which displays contacts (private pupils, as I run my own music tuition business) from the contact table. There is another table which links to the contact table which stores the contacts lesson history and whether or not they attended those lessons.

I want to work out the attendance of each contact with a click of a button. I have used the dcount function to count attended lessons then divided by all their lessons (regardless of attended or not) and times by 100 to get my attendance percentage, which works great.... But here is the problem....

It only returns the attendance percentage of the first contact on the form. Is there anyway it can loop through each contact to do all the contacts so I can see each persons attendance record?

If anyone can help or suggest an alternative I would be extremely grateful.

 

[pr2-eugin]

Why not create a Query include the calculation in that and then use that as the record source for the continuous form? There is not need to involve click of button.. IMO this is easy and less messy.. But I might be wrong, as I do not know your exact requirement..

PS : Your title says something, but description is different?

 

[DavidAtWork]

another option would be to have an unbound textbox on your form that uses the same DCount method, only use the vba DCount and apply it to the OnCurrent event of your form,
i.e. Me.textbox1 = DCount("[lessonID]","[tblLessons]","[contactID] = " & Me.contactID & " AND "[lessonAttended] = True")/DCount("[lessonID]","[tblLessons]","[contactID] = " & Me.contactID)
Set the Format of the textbox to a %, you need to adjust the code to suit your controls and table names/fields etc

David