OK. Here we go.
You have two choices: a form or a report. If you just want to look at it,
you might instead just create a simple report that is grouped by Client ID
and sorted (descending) by TransDate, with the details being TransDate &
Attendance. That might be the easy way.
To do it on a form, though:
Create a form; let's call it Attendance.
Turn off the navigation buttons (keeps the user from navigating to a new
record)
Turn on only the vertical scroll bar
Set the form's Default view to Continuous
Make the FORM's record source this:
SELECT Attendance.*
FROM Attendance
WHERE (((Attendance.ClientID)=[Forms]![Attendance]![ClientIDSelector]) AND
(([Forms]![Attendance]![ClientIDSelector]) Is Not Null)) OR
((([Forms]![Attendance]![ClientIDSelector]) Is Null))
ORDER BY Attendance.ClientID, Attendance.TransDate DESC;
(This will show all client's records until you pick one from the
ClientIDSelector box. If you want the form empty until you pick a client, use
this instead:
SELECT Attendance.*
FROM Attendance
WHERE (((Attendance.ClientID)=[Forms]![Attendance]![ClientIDSelector]))
ORDER BY Attendance.ClientID, Attendance.TransDate DESC;
Create an UNBOUND ClientIDSelector combo box in the header. Make its row
source:
SELECT Clients.ClientID, Clients.ClientName
FROM Clients
ORDER BY Clients.ClientName;
Make the ColumnCount 2 with BoundColumn 1 and column widths of 0;2. This
will allow the user to pick from the clients by name while using the ClientID
for reference.
In the form's detail section, create two locked and/or disabled (to prevent
data entry) text boxes, side by side:
TransDate (bound to TransDate field)
Attendance (bound to Attendance field)
Add this code for ClientIDSelector_AfterUpdate:
ClientIDSelector_AfterUpdate()
Me.Requery
End Sub
Now, when you pick a client, you get all the dates, in descending order,
along with the attendance for each date.
Vernon said:
1. This portion of the form is display only. There are meno fields for
input. Attendance is entered on another form along with the date of
attendance.
2. I want a series of boxes on a running form that shows the date (of
attendance)
and the attendance (P, A, Ex) that was entered on another form and storied
in a table (tblPayments) for only one client (selected from a combo box)
ordered by date.
3. Just the day that have entries. The boxes could go as far as three
months. Some clients only meet once a week, and some clients meet twice a
week. Therefore the number of boxes that get filled in will depend on the
number of dates that are found in the table. If they have just started, there
may only be four dates that are found and pulled into the form.