Find first, find 2nd, find 3rd, etc.

G

Guest

I have a table which includes,among others, the following fields,
TranDate(Date field), ClientID(Number field), and Attendance(Text field). I
would like to be able to have a series of text boxes on a form that would
pull in the first date and attendance, then the second date and attendance,
then the third date and attendance and so on, for each clientID. If I could
get help on the first three, then I can continue for the other days of the
month. I think I may need one text box for date and another for attendance.
Is this possible? I am using Access 2000.
Thanks, Vernon
 
G

Guest

I think I can answer this, but I want to ask a couple of structural questions
first:

1. Is this a display form only (i.e. shows previously-entered data), or will
you use it to input information also? That is, do you want to use this to
input attendance, or just report on it?
2. Do you want a form that shows each day of a particular month, along with
the attendance, for the current client? That is, do you want a snapshot of a
single complete month for the selected client, with a line for each day of
the month, or a running form that shows all attendance for all clients and
dates, ordered by perhaps ClientID, then date?
3. If you do want a whole month, do you want every day to appear, or just
the days that will have entries?
 
G

Guest

Brian said:
I think I can answer this, but I want to ask a couple of structural questions
first:

1. Is this a display form only (i.e. shows previously-entered data), or will
you use it to input information also? That is, do you want to use this to
input attendance, or just report on it?

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. Do you want a form that shows each day of a particular month, along with
the attendance, for the current client? That is, do you want a snapshot of a
single complete month for the selected client, with a line for each day of
the month, or a running form that shows all attendance for all clients and
dates, ordered by perhaps ClientID, then date?

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. If you do want a whole month, do you want every day to appear, or just
the days that will have entries?

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.
 
G

Guest

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.
 
G

Guest

Thanks Brian. Your response is very close to what I needed. I will now make
the form multiple columns and use it as a subform on my main form. Then I can
layout a report to be printed. I think this is the proper way to go.
Thanks again,
Vernon

Brian said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top