Question??

[DUNNER7]

I am keeping a student database, I have a table for demographics and a table
for students...they are related by student ID#. Here is my problem, I am
able to to use the table I designed for attendance to do it using a drop-down
box linked to the demographic information...pick name enter Present (next
field) and datetimestamp occurs upon enter. Simple enough...however, it will
be cumbersome to try and do this in September while checking in 300 students
into the building during a 15-20 minute period.

Possible solution: I was able to design a report and set it up so that each
kid's id, first and last name are listed and I could easily scroll down the
list of names. My question is: Is there a way to design code so that when I
clicked on the last name that it would enter "PRE" (present) and store the
datetimestamp entry in my attendance table? Is it also possible to cause the
color property of the font of the last name LABEL to change upon the click
and then change back to the default color at some later time period? (to
avoid clicking a name twice).

Thanks,
Del Dobbs

 

[Piet Linden]

I am keeping a student database, I have a table for demographics and a table
for students...they are related by student ID#.  Here is my problem, I am
able to to use the table I designed for attendance to do it using a drop-down
box linked to the demographic information...pick name enter Present (next
field) and datetimestamp occurs upon enter.  Simple enough...however, it will
be cumbersome to try and do this in September while checking in 300 students
into the building during a 15-20 minute period.  

Possible solution:  I was able to design a report and set it up so thateach
kid's id, first and last name are listed and I could easily scroll down the
list of names.  My question is:  Is there a way to design code so that when I
clicked on the last name that it would enter "PRE" (present) and store the
datetimestamp entry in my attendance table?  Is it also possible to cause the
color property of the font of the last name LABEL to change upon the click
and then change back to the default color at some later time period? (to
avoid clicking a name twice).

Thanks,
Del Dobbs

What's the difference between Demographics and Students? How are they
related? Can a student ever have more than one Demographics record? if
not, the two tables should be merged.

One way to populate the attendance table is to create a table of dates
tblDates(SomeDate) then you can simply create a cartesian product
query (drop both the tblDates and tblStudents table into the grid, but
with no join between them then limit for the date(s) you want to
create records for. Then turn this into an append query and append to
the Attendance table.

Then you could just use code to populate the Present field in your
table...
Me.txtPresent = "PRE"

 

[John Spencer MVP]

The easiest way probably be to create all the records in the attendance table
and mark them as Present when you do so. Then you simply need to unmark the
few that are not present.

By the way you cannot do this in a report, it must be done in a continuous
form. You can base the form on a query that shows data for a specified date.

SELECT tblStudents.LastName, tblStudents.FirstName
, tblAttendance.*
FROM tblStudents INNER JOIN tblAttendance
ON tblStudents.StudentID = tblAttendance.StudentID
WHERE AttendDate = /// some date value here ///

A basic query to add all the students in one swoop might look like:
INSERT INTO TblAttendance (StudentID, AttendDate, Status)
SELECT StudentID, Date(), "PRE"
FROM TblStudents
WHERE tblStudents.Status = "Active"

You can use conditional formating to change the appearance of a textbox
control, but not a label. HOWEVER, you can cheat a bit and replace the label
with a textbox control that has its source set to ="Last Name". Then in
select this control and conditional formatting from the menu. Set the
conditional formatting to
EXPRESSION : [Status] <> "PRE" or [Status] is Null
and then select the foreground or background colors as appropriate.

You can set use VBA code and one of the control's events to clear/set the
values. I would probably use the button control bound to the status field and
the click event to change the status from "PRE" to "ABS".

Also, you shoud

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[DUNNER7]

I am sorry, I did not see that you responded to my other post. I changed the
title, because it seemed like it wasn't getting any response. I can work
with this. I have the default "attendancestatus" field in the table
attendance marked as UNX (unexcused), I do not want anybody marked as PRE
(present) until they actually physically walk in the door. I will try and
work with your suggestions.

Thanks,
Sorry about the other mix-up

Del Dobbs

The easiest way probably be to create all the records in the attendance table
and mark them as Present when you do so. Then you simply need to unmark the
few that are not present.

By the way you cannot do this in a report, it must be done in a continuous
form. You can base the form on a query that shows data for a specified date.

SELECT tblStudents.LastName, tblStudents.FirstName
, tblAttendance.*
FROM tblStudents INNER JOIN tblAttendance
ON tblStudents.StudentID = tblAttendance.StudentID
WHERE AttendDate = /// some date value here ///

A basic query to add all the students in one swoop might look like:
INSERT INTO TblAttendance (StudentID, AttendDate, Status)
SELECT StudentID, Date(), "PRE"
FROM TblStudents
WHERE tblStudents.Status = "Active"

You can use conditional formating to change the appearance of a textbox
control, but not a label. HOWEVER, you can cheat a bit and replace the label
with a textbox control that has its source set to ="Last Name". Then in
select this control and conditional formatting from the menu. Set the
conditional formatting to
EXPRESSION : [Status] <> "PRE" or [Status] is Null
and then select the foreground or background colors as appropriate.

You can set use VBA code and one of the control's events to clear/set the
values. I would probably use the button control bound to the status field and
the click event to change the status from "PRE" to "ABS".

Also, you shoud

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am keeping a student database, I have a table for demographics and a table
for students...they are related by student ID#. Here is my problem, I am
able to to use the table I designed for attendance to do it using a drop-down
box linked to the demographic information...pick name enter Present (next
field) and datetimestamp occurs upon enter. Simple enough...however, it will
be cumbersome to try and do this in September while checking in 300 students
into the building during a 15-20 minute period.

Possible solution: I was able to design a report and set it up so that each
kid's id, first and last name are listed and I could easily scroll down the
list of names. My question is: Is there a way to design code so that when I
clicked on the last name that it would enter "PRE" (present) and store the
datetimestamp entry in my attendance table? Is it also possible to cause the
color property of the font of the last name LABEL to change upon the click
and then change back to the default color at some later time period? (to
avoid clicking a name twice).

Thanks,
Del Dobbs

 

[DUNNER7]

I should probably tell you what relevant fields I am working with from the
tables:

Table Demographics
ID (student)
LastName
FirstName

Table Attendance
ID (student)
Status (Drop down...choices UNX, EXC, PRE, SUS default value UNX)
EntryTime (datetimestamp)

I attempted a query using the wizard...picked the six fields from the two
tables then I attempted to use the expression builder under the status field
in the Query design view. I attempted to write what you indicated starting
with the StudentID = TblAttendance.Student Id on through the date value, I
used (now). I think I made some error because nothing happened.???

Del Dobbs

The easiest way probably be to create all the records in the attendance table
and mark them as Present when you do so. Then you simply need to unmark the
few that are not present.

By the way you cannot do this in a report, it must be done in a continuous
form. You can base the form on a query that shows data for a specified date.

SELECT tblStudents.LastName, tblStudents.FirstName
, tblAttendance.*
FROM tblStudents INNER JOIN tblAttendance
ON tblStudents.StudentID = tblAttendance.StudentID
WHERE AttendDate = /// some date value here ///

A basic query to add all the students in one swoop might look like:
INSERT INTO TblAttendance (StudentID, AttendDate, Status)
SELECT StudentID, Date(), "PRE"
FROM TblStudents
WHERE tblStudents.Status = "Active"

You can use conditional formating to change the appearance of a textbox
control, but not a label. HOWEVER, you can cheat a bit and replace the label
with a textbox control that has its source set to ="Last Name". Then in
select this control and conditional formatting from the menu. Set the
conditional formatting to
EXPRESSION : [Status] <> "PRE" or [Status] is Null
and then select the foreground or background colors as appropriate.

You can set use VBA code and one of the control's events to clear/set the
values. I would probably use the button control bound to the status field and
the click event to change the status from "PRE" to "ABS".

Also, you shoud

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am keeping a student database, I have a table for demographics and a table
for students...they are related by student ID#. Here is my problem, I am
able to to use the table I designed for attendance to do it using a drop-down
box linked to the demographic information...pick name enter Present (next
field) and datetimestamp occurs upon enter. Simple enough...however, it will
be cumbersome to try and do this in September while checking in 300 students
into the building during a 15-20 minute period.

Possible solution: I was able to design a report and set it up so that each
kid's id, first and last name are listed and I could easily scroll down the
list of names. My question is: Is there a way to design code so that when I
clicked on the last name that it would enter "PRE" (present) and store the
datetimestamp entry in my attendance table? Is it also possible to cause the
color property of the font of the last name LABEL to change upon the click
and then change back to the default color at some later time period? (to
avoid clicking a name twice).

Thanks,
Del Dobbs

 

[John W. Vinson]

I attempted a query using the wizard...picked the six fields from the two
tables then I attempted to use the expression builder under the status field
in the Query design view. I attempted to write what you indicated starting
with the StudentID = TblAttendance.Student Id on through the date value, I
used (now). I think I made some error because nothing happened.???

A criterion of Now() will retrieve records only where the date field on which
you are using the criterion contains the current date and time accurate to
microseconds. It's almost certainly not going to find any records!

If you're storing the date (using Date()) in this field use a criterion of
=Date() instead of =Now() to get the entire day. If you're storing the date
and time use a criterion of

 

[DUNNER7]

Thanks for your continued help and patience. I think that we are working on
two different results. I am able to open my attendance table and click on
the drop down box and scroll through all the names select one click the next
box scroll down to "PRE" and hit enter and the time/date will record in the
table. My goal is to have that data.

I am looking for a easier way than scrolling down a drop down through 300
names to select one. I am looking for a way that all of my demographic stuff
I need: ID Last and First name are listed and it is listed on a page like a
report and I could scroll down and click on the name and the PRE would be
entered and date time into the attendance table.

If a design a form I would have to use one of the navigation arrows on the
bottom to scroll through the names one at a time. I need something that I
can just scan a page quickly and click and have the event completed. Is this
possible?

 

[DUNNER7]

I believe that I found a way. I was staring at the screen and saw the
command "create multiple items" in the form design tab. I was able to link
this based on the demographics table and eliminate all information except for
StudentId, LastName, FirstName. Now there should be a way to create an event
when I click on the last name that will cause the "UNX" to change to a "PRE"
in the tableattendance.status and the Time and date to be entered in the
table attendance.entry time(datetimestamp)?

Thanks
Del Dobbs

 

[John W. Vinson]

I believe that I found a way. I was staring at the screen and saw the
command "create multiple items" in the form design tab. I was able to link
this based on the demographics table and eliminate all information except for
StudentId, LastName, FirstName. Now there should be a way to create an event
when I click on the last name that will cause the "UNX" to change to a "PRE"
in the tableattendance.status and the Time and date to be entered in the
table attendance.entry time(datetimestamp)?

Should be doable. I'd use the DoubleClick event rather than click (click is
often too sensitive and can lead to unintentionaly updates). The code would be
something like

Private Sub LastName_DblClick()
If Me.Status = "UNX" Then
Me!Status = "PRE"
Me![Entry Time] = Now
End If
End Sub

This may need to be tweaked a bit for your specific situation.

 

[DUNNER7]

We are just about there. I must be doing something wrong however. When I
start typing the code..."If Me.Status (Status is not one of the choices I get
when let little menu with all the choices scrolls up). I am assuming that
Status is referring to the field in the table Attendance. What would be a
reason that it would not show up?

I believe that I found a way. I was staring at the screen and saw the
command "create multiple items" in the form design tab. I was able to link
this based on the demographics table and eliminate all information except for
StudentId, LastName, FirstName. Now there should be a way to create an event
when I click on the last name that will cause the "UNX" to change to a "PRE"
in the tableattendance.status and the Time and date to be entered in the
table attendance.entry time(datetimestamp)?

Should be doable. I'd use the DoubleClick event rather than click (click is
often too sensitive and can lead to unintentionaly updates). The code would be
something like

Private Sub LastName_DblClick()
If Me.Status = "UNX" Then
Me!Status = "PRE"
Me![Entry Time] = Now
End If
End Sub

This may need to be tweaked a bit for your specific situation.

 

[John W. Vinson]

We are just about there. I must be doing something wrong however. When I
start typing the code..."If Me.Status (Status is not one of the choices I get
when let little menu with all the choices scrolls up). I am assuming that
Status is referring to the field in the table Attendance. What would be a
reason that it would not show up?

I see elsewhere in this thread that you want to create a new record. My code
was assuming that you wanted to edit the value in an existing record, so it
won't work.

I'd actually suggest a somewhat different approach. You could use a form with
three controls, based on the Attendance table. Put a Listbox control bound to
StudentID, based on a query showing all active students sorted in alphabetical
order; have a textbox bound to [entry time] with a Default Value of =Now();
and have a combo box bound to the Status field with a default value of "Pre".
Set the form's Data Entry property to True so it will let you enter new
records.

Yoiu can then pick the student from the listbox (type the first letter of the
student's name to jump to that letter in the alphabet) and tab out of the
listbox. The other two fields will fill in automatically (and can be
overridden if you, say, learn that a student has an excused absence, or you
need to enter an entry time other than the current moment).