Looking for the right code

[DUNNER7]

I found a 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)? Anyone have any
ideas do I use the expression builder?

Thanks
Del Dobbs

 

[Jeff Boyce]

Del

"How" depends on "what" ... and you haven't described what table/data
structure you're using.

And if I understand your description, you want to replace one code ("PRE")
with another code ("UNX") ... won't that eliminate any record of when/how
long the "PRE" code applied? What about the idea of adding a date/time
value to the "PRE" code record, then adding a new record with "UNX".

(since we aren't there and don't know your situation, these are total shots
in the dark...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DUNNER7]

You are right, I didn't give all the details. I am working on a student
database in Access 07. I want to keep daily attendance of about 300 students
as they walk into the building. In its simplest form, I have a table
"Demographics" with all demographic information "ID" is the primary key and
it is the unique student ID number assigned by the district. I also have a
table "Attendance" field 1: primary key, auto per entry, field 2: ID
(student ID), field 3: Status (combo box with choices UNX [unexcused], EXC
[excused], PRE [present] UNX being default value, field 4: EntryTime
(datetimestamp).

When student walks into building I want to click on the name on the form and
the data will be entered into the attendance table as UNX change to PRE and
date/time entered into respective fields.

I am able to do this already using a combo box and scrolling down, selecting
name, and selecting PRE from the next box and pressing enter. It is
difficult to scroll down a combo box of 250 - 300 names while student are
entering school in the morning. That is why I wanted a form that would list
the names and I could scroll down the page and click the last name to cause
the data entry to happen.


I left all this out before because I was in a diffent forum and made an
assumption that I shouldn't have about everyone knowing my problem.

Thanks,
Del Dobbs

 

[DUNNER7]

Each click would add a new entry as the student walked into the building.
What would actually need to be recorded was their attendance status changed
from the default UNX to PRE and the actual time and date they entered.

So are you working with an *existing* record in the
Attendance table and you just need to modify the
Status and EntryTime fields?

Or do you need to add a *new* record to the
Attendance table including StudentID, Status and
EntryTime?

--
_________

Sean Bailey

You are right, I didn't give all the details. I am working on a student
database in Access 07. I want to keep daily attendance of about 300 students
as they walk into the building. In its simplest form, I have a table
"Demographics" with all demographic information "ID" is the primary key and
it is the unique student ID number assigned by the district. I also have a
table "Attendance" field 1: primary key, auto per entry, field 2: ID
(student ID), field 3: Status (combo box with choices UNX [unexcused], EXC
[excused], PRE [present] UNX being default value, field 4: EntryTime
(datetimestamp).

When student walks into building I want to click on the name on the form and
the data will be entered into the attendance table as UNX change to PRE and
date/time entered into respective fields.

I am able to do this already using a combo box and scrolling down, selecting
name, and selecting PRE from the next box and pressing enter. It is
difficult to scroll down a combo box of 250 - 300 names while student are
entering school in the morning. That is why I wanted a form that would list
the names and I could scroll down the page and click the last name to cause
the data entry to happen.


I left all this out before because I was in a diffent forum and made an
assumption that I shouldn't have about everyone knowing my problem.

Thanks,
Del Dobbs

Del

"How" depends on "what" ... and you haven't described what table/data
structure you're using.

And if I understand your description, you want to replace one code ("PRE")
with another code ("UNX") ... won't that eliminate any record of when/how
long the "PRE" code applied? What about the idea of adding a date/time
value to the "PRE" code record, then adding a new record with "UNX".

(since we aren't there and don't know your situation, these are total shots
in the dark...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I found a 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)? Anyone have any
ideas do I use the expression builder?

Thanks
Del Dobbs

 

[Jeff Boyce]

I still don't have a very clear picture of your data structure...

Could you post something like the following (a simplified example of
"enrollment"):

tblStudent
StudentID
LName
FName

tblClass
ClassID
ClassTitle

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Thanks.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DUNNER7]

tblDemographics
ID (student ID#)
LastName
FirstName
MiddleI
Sex
Race

tblAttendance
AttID (auto number)
ID (student ID#)
Status (combo box values = "EXC", "UNX" (default), "PRE")
EntryTime (date/time stamp)

Created Multiple Item form based on Demographic Table so that all 260 kids
were listed alphabetically on form. Want to be able to click last name on
the form and cause Status and EntryTime on Attendance table to change and
record data.
I have other tables, but these are the two that I need immediate attention
on and the others are not relevant to this issue.


Thanks,
Del Dobbs

 

[Jeff Boyce]

Thanks for the clarification...

Let's see if I can paraphrase your post ...

You want a way to identify which student(s) attended on which day(s).

I'm not understanding the purpose of/need for the [Status] field.

If you have a record of a StudentID with a DateTime value in your Attendance
table, wouldn't that be proof of attendance? Conversely, if a StudentID
doesn't show for a particular date, isn't that "absent"?

What is [Status] adding to your process?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DUNNER7]

I want a way to track when/if a student comes into the school. The status
just shows if they were there, they were not there, and reasons for absences.
Any kid who does not show up to school on a given day is automatically
considered UNXecused until we recieve an excuse from a parent, court
official, medical professional etc. So the default would always be
unexcused. The status gives me a way of sorting who was present for the day
(in a report) or who was absent on any given day. We would go back in and
change any UNX to an EXC or some other reason once the student brought in
appropriate documentation. Does that clear it up?

Del

Thanks for the clarification...

Let's see if I can paraphrase your post ...

You want a way to identify which student(s) attended on which day(s).

I'm not understanding the purpose of/need for the [Status] field.

If you have a record of a StudentID with a DateTime value in your Attendance
table, wouldn't that be proof of attendance? Conversely, if a StudentID
doesn't show for a particular date, isn't that "absent"?

What is [Status] adding to your process?

Regards

Jeff Boyce
Microsoft Office/Access MVP

tblDemographics
ID (student ID#)
LastName
FirstName
MiddleI
Sex
Race

tblAttendance
AttID (auto number)
ID (student ID#)
Status (combo box values = "EXC", "UNX" (default), "PRE")
EntryTime (date/time stamp)

Created Multiple Item form based on Demographic Table so that all 260 kids
were listed alphabetically on form. Want to be able to click last name on
the form and cause Status and EntryTime on Attendance table to change and
record data.
I have other tables, but these are the two that I need immediate attention
on and the others are not relevant to this issue.


Thanks,
Del Dobbs

 

[John W. Vinson]

I want a way to track when/if a student comes into the school. The status
just shows if they were there, they were not there, and reasons for absences.
Any kid who does not show up to school on a given day is automatically
considered UNXecused until we recieve an excuse from a parent, court
official, medical professional etc. So the default would always be
unexcused. The status gives me a way of sorting who was present for the day
(in a report) or who was absent on any given day. We would go back in and
change any UNX to an EXC or some other reason once the student brought in
appropriate documentation. Does that clear it up?

It leads to a contradiction with your previous request. If you only create a
record *at the time that a student comes in the door* then UNX is in fact not
appropriate - you know the student IS present!

You can do this two ways: prepopulate the table with a record for each
student, UNX, and just use a simple continuous form displaying all those
records and flip the status when they come in; OR you can start with an empty
table, add a new record when each student comes in (per my previous
suggestion), and then run an Unmatched query after everyone should be in class
to identify the truants. Both methods have their pluses and minuses, I'm not
sure which would be better in your case.

But you can't have both simultaneously!

 

[Beetle]

You could run an append query in the Double Click event
of the LastName text box on your form;

Private Sub txtLastName_DblClick(Cancel As Integer)

Dim strSQL As String, strMsg As String

strMsg = "Do you want to add an attendance record for this student?"

strSQL = "Insert Into tblAttendance(ID, Status, EntryTime) " & _
"Values (" & Me!ID & ", 'PRE', #" & Now & "#);"

If MsgBox(strMsg, vbYesNo, "Add Record?") = vbYes Then
CurrentDb.Execute strSQL, dbFailOnError
End If

End Sub

--
_________

Sean Bailey

Each click would add a new entry as the student walked into the building.
What would actually need to be recorded was their attendance status changed
from the default UNX to PRE and the actual time and date they entered.

So are you working with an *existing* record in the
Attendance table and you just need to modify the
Status and EntryTime fields?

Or do you need to add a *new* record to the
Attendance table including StudentID, Status and
EntryTime?

--
_________

Sean Bailey

You are right, I didn't give all the details. I am working on a student
database in Access 07. I want to keep daily attendance of about 300 students
as they walk into the building. In its simplest form, I have a table
"Demographics" with all demographic information "ID" is the primary key and
it is the unique student ID number assigned by the district. I also have a
table "Attendance" field 1: primary key, auto per entry, field 2: ID
(student ID), field 3: Status (combo box with choices UNX [unexcused], EXC
[excused], PRE [present] UNX being default value, field 4: EntryTime
(datetimestamp).

When student walks into building I want to click on the name on the form and
the data will be entered into the attendance table as UNX change to PRE and
date/time entered into respective fields.

I am able to do this already using a combo box and scrolling down, selecting
name, and selecting PRE from the next box and pressing enter. It is
difficult to scroll down a combo box of 250 - 300 names while student are
entering school in the morning. That is why I wanted a form that would list
the names and I could scroll down the page and click the last name to cause
the data entry to happen.


I left all this out before because I was in a diffent forum and made an
assumption that I shouldn't have about everyone knowing my problem.

Thanks,
Del Dobbs

:

Del

"How" depends on "what" ... and you haven't described what table/data
structure you're using.

And if I understand your description, you want to replace one code ("PRE")
with another code ("UNX") ... won't that eliminate any record of when/how
long the "PRE" code applied? What about the idea of adding a date/time
value to the "PRE" code record, then adding a new record with "UNX".

(since we aren't there and don't know your situation, these are total shots
in the dark...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I found a 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)? Anyone have any
ideas do I use the expression builder?

Thanks
Del Dobbs

 

[DUNNER7]

I think I discovered our misunderstanding after reading your last post. I
will be starting off with a blank table and it will grow in length equal to
the number of students who walk through the school door each day. Day 1
could have 250 kids come to school Day 2 could have 300 come in, etc., etc.
adding onto the number of rows each day in the attendance table. I probably
do not need the status with the "UNX" default since by simple deduction,
everyone who did not come through the door is "UNX" everyone in is "PRE".
Now that is straightened out how do I do the code?

Thanks....you are really making me think!!

 

[Beetle]

Repost of my reply elsewhere in the thread:

You could run an append query in the Double Click event
of the LastName text box on your form;

Private Sub txtLastName_DblClick(Cancel As Integer)

Dim strSQL As String, strMsg As String

strMsg = "Do you want to add an attendance record for this student?"

strSQL = "Insert Into tblAttendance(ID, Status, EntryTime) " & _
"Values (" & Me!ID & ", 'PRE', #" & Now & "#);"

If MsgBox(strMsg, vbYesNo, "Add Record?") = vbYes Then
CurrentDb.Execute strSQL, dbFailOnError
End If

End Sub