Save selected items NOT selected in an Extended List Box based onother condition

[big.brown]

Hello,

I am truly a novice and hope that you will be able to answer this
question.

Need: Save a list of members that are NOT selected in an Extended List
Box and indicate them as not present for a ConfType (conference type)
unless they are "excused" for a given date range already recorded in
another table.

My code is below and I do not know how to modify it to accomplish the
above need.

Thank you!

--------------------

Public Function CreateAttendanceRecords()
On Error GoTo Err_CreateAttendanceRecords_Click

Dim i, j, k As Variant
Dim dbs As Database
Dim rst As Recordset
Dim strSql As String

strSql = "SELECT RecordID, ResidentID, Date, ConfType FROM
TblAttendance;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql)

For Each i In Forms!frmAttendance!lstResidents.ItemsSelected

rst.AddNew
rst!ResidentID = Forms!frmAttendance!lstResidents.ItemData(i)
rst!Date = Forms!frmAttendance!Date
For Each j In Forms!frmAttendance!
lstConferenceTypes.ItemsSelected
rst!ConfType = Forms!frmAttendance!
lstConferenceTypes.ItemData(j)
Next j
rst.Update
Set rst = Nothing
MsgBox "Records Created"

Exit_CreateAttendanceRecords_Click:
Exit Function

Err_CreateAttendanceRecords_Click:
Select Case Err.Number
Case 3022 'ignore duplicate keys
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_CreateAttendanceRecords_Click
End Select

End Function

 

[Steve Sanford]

unless they are "excused" for a given date range already recorded in
another table.

Can yo give more info??? Table name field names, (a few) examples of records
in the table.

Do you want "Not Present" entered in the field "ConfType"?? If not, what
should be entered??

Here is the basic code - untested!!! Still needs to have the excused check
added in the indicated place.


Option Compare Database
Option Explicit

Public Sub CreateAttendanceRecords()
On Error GoTo Err_CreateAttendanceRecords_Click

Dim i, j, k As Variant
Dim dbs As Database
Dim rst As Recordset
Dim strSql As String

Dim ConfDate As Date 'date of conf from form
Dim ctlList As Control, vItem ' list stuff
Dim N As Integer

ConfDate = Forms!frmAttendance!Date
Set dbs = CurrentDb

strSql = "SELECT ResidentID, Date, ConfType"
strSql = strSql & " FROM TblAttendance;"

Set rst = dbs.OpenRecordset(strSql)

'list box
ctlList = Forms!frmAttendance!lstResidents

'reverse selections to get the
'members that are NOT selected
For N = 0 To ctlList.ListCount
ctlList.Selected(N) = Not ctlList.Selected(N)
Next

'process list
For Each vItem In ctlList.ItemsSelected

'**********************
'this is where we check to see if they are "excused"
'for a given date range already recorded in another Table
'**********************

'add new record to recordset
rst.AddNew
rst!ResidentID = ctlList.ItemData(vItem)
rst!Date = ConfDate

rst!ConfType = "not present"
'save the record
rst.Update
Next


'clear the list box
For N = 0 To ctlList.ListCount
ctlList.Selected(N) = False
Next

MsgBox "Records Created"

Exit_CreateAttendanceRecords_Click:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

Err_CreateAttendanceRecords_Click:
Select Case Err.Number
Case 3022 'ignore duplicate keys
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_CreateAttendanceRecords_Click
End Select


End Sub





HTH

 

[big.brown]

Hello Steve,

Thank you very much for your reply. As you can see in the sample data
below, there is a date range during which they are excused.
Typically, when someone is excused, they are excused for all
"conference types" (ConfType = ALL). The data that should be checked
is the "Excused Status" as "OFF."

Can yo give more info??? Table name field names, (a few) examples of records
in the table.

RecordID Person ID StartDate EndDate
ConfType ExcusedStatus
44 7 08/01/2008 08/31/2008 CD OFF
47 9 08/01/2008 08/31/2008 CD OFF
65 5 09/01/2008 09/30/2008 DJC OFF
72 9 09/01/2008 09/30/2008 DJC OFF

Do you want "Not Present" entered in the field "ConfType"?? If not, what
should be entered??

I was going to use the code "ABS" to represent "Absent"

 

[Steve Sanford]

RecordID Person ID StartDate EndDate

ConfType ExcusedStatus
44 7 08/01/2008 08/31/2008 CD OFF
47 9 08/01/2008 08/31/2008 CD OFF

*** What is the name of the table for the above data??? ***

*** What does [ExcusedStatus] = "OFF" represent? What means that the
person is excused??


OK, if I understand right, you have a form ("frmAttendance ") that has a
control for the conference date ("Date" - bad choice for an object name.
"Date" is a reserved word in Access) and a list box ("lstResidents").

You will select people in the list box and anyone NOT selected you want to
enter a record for them in the table "TblAttendance" as ABSENT, unless they
are excused. Only ABSENT people will have a record entered.

They are excused for all conferences on that date if indicated by a record
in the "??????" (see above) table, with a value of "OFF" (??) in the
"ExcusedStatus" field.

In the table "TblAttendance", the fields to be entered are:

Field Value
-----------------------------------
ResidentID PK ' From List box
Date Forms!frmAttendance!Date ' date of conference
ConfType "ABS" ' Absent



How close am I ?

 

[big.brown]

Hello Steve,

Your code to record unselected records was perfect. Thank you.

RecordID   Person ID       StartDate                       EndDate
ConfType   ExcusedStatus
44 7       08/01/2008      08/31/2008      CD     OFF
47 9       08/01/2008      08/31/2008      CD     OFF

***  What is the name of the table for the above data??? ***
TblExcused

***  What does [ExcusedStatus]  = "OFF" represent? What means that the
person is excused??

OFF is the "code" word for being excused but actually as I think about
your question, there are several codes or reasons why a person would
be excused- illness, maternity, etc. Perhaps, I should record the
reason and then have the CodeValue = OFF for all of the reasons.

Being excused means that they can be absent and not be penalized for
it, i.e. not be marked as absent in the table.

Ultimately, the goal is to calculate attendance as an aggregate value
and for each individual conference (% attendance for the monday
lectures separate from the tuesday lectures) on a rolling 12-month
average.

CodeType CodeValue CodeDescription
EXCUSED OFF Excused (per policy)
EXCUSED ILL Illness
EXCUSED OTH Other
EXCUSED DTH Death
EXCUSED JD Jury Duty
EXCUSED HOS Hospitalization
EXCUSED MAT Maternity
EXCUSED TRP Transportation Problems
EXCUSED CC Child Care
EXCUSED FHP Family Health Problems
EXCUSED DOC Doctor/Dentist appts. (unavoidable)

OK, if I understand right, you have a form ("frmAttendance ") that has a
control for the conference date ("Date" - bad choice for an object name.
"Date" is a reserved word in Access) and a list box ("lstResidents").

I will change the object name for the conference date. Thanks.

You will select people in the list box and anyone NOT selected you want to
enter a record for them in the table "TblAttendance" as ABSENT, unless they
are excused. Only ABSENT people will have a record entered.

They are excused for all conferences on that date if indicated by a record
in the "??????" (see above) table, with a value of "OFF" (??) in the
"ExcusedStatus" field.

In the table "TblAttendance", the fields to be entered are:

Field               Value

RecordID Resident ID Date ConfType
15589 95 05/26/2009 WD
4210 5 05/21/2009 GR
679 62 05/20/2009 ETH
345 7 05/20/2009 ABS

How close am I ?

You are exactly correct. However, I am thinking about this structure
because I need to record an absence specific to a particular ConfType,
which would require an additional field, e.g. Absent for each
ConfType.

Thanks.

 

[Steve Sanford]

OFF is the "code" word for being excused but actually as I think about
your question, there are several codes or reasons why a person would
be excused- illness, maternity, etc. Perhaps, I should record the
reason and then have the CodeValue = OFF for all of the reasons.

Well, "OFF" as a value for the field "ExcusedStatus" isn't really
informative. I would use "Excused" or "Unexcused".

Does it matter *why" they were excused? Is the excused reason being tracked
in other ways (timesheets)?? If you are not going to do anything with the
reason why a person was excused, do you want to spend time entering it into
the databse?

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hello Steve,

Your code to record unselected records was perfect. Thank you.

RecordID Person ID StartDate EndDate
ConfType ExcusedStatus
44 7 08/01/2008 08/31/2008 CD OFF
47 9 08/01/2008 08/31/2008 CD OFF

*** What is the name of the table for the above data??? ***
TblExcused

*** What does [ExcusedStatus] = "OFF" represent? What means that the
person is excused??

OFF is the "code" word for being excused but actually as I think about
your question, there are several codes or reasons why a person would
be excused- illness, maternity, etc. Perhaps, I should record the
reason and then have the CodeValue = OFF for all of the reasons.

Being excused means that they can be absent and not be penalized for
it, i.e. not be marked as absent in the table.

Ultimately, the goal is to calculate attendance as an aggregate value
and for each individual conference (% attendance for the monday
lectures separate from the tuesday lectures) on a rolling 12-month
average.

CodeType CodeValue CodeDescription
EXCUSED OFF Excused (per policy)
EXCUSED ILL Illness
EXCUSED OTH Other
EXCUSED DTH Death
EXCUSED JD Jury Duty
EXCUSED HOS Hospitalization
EXCUSED MAT Maternity
EXCUSED TRP Transportation Problems
EXCUSED CC Child Care
EXCUSED FHP Family Health Problems
EXCUSED DOC Doctor/Dentist appts. (unavoidable)

OK, if I understand right, you have a form ("frmAttendance ") that has a
control for the conference date ("Date" - bad choice for an object name.
"Date" is a reserved word in Access) and a list box ("lstResidents").

I will change the object name for the conference date. Thanks.

You will select people in the list box and anyone NOT selected you want to
enter a record for them in the table "TblAttendance" as ABSENT, unless they
are excused. Only ABSENT people will have a record entered.

They are excused for all conferences on that date if indicated by a record
in the "??????" (see above) table, with a value of "OFF" (??) in the
"ExcusedStatus" field.

In the table "TblAttendance", the fields to be entered are:

Field Value

RecordID Resident ID Date ConfType
15589 95 05/26/2009 WD
4210 5 05/21/2009 GR
679 62 05/20/2009 ETH
345 7 05/20/2009 ABS

How close am I ?

You are exactly correct. However, I am thinking about this structure
because I need to record an absence specific to a particular ConfType,
which would require an additional field, e.g. Absent for each
ConfType.

Thanks.

 

[big.brown]

Well, "OFF" as a value for the field "ExcusedStatus" isn't really
informative. I would use "Excused" or "Unexcused".

Does it matter *why" they were excused? Is the excused reason being tracked
in other ways (timesheets)?? If you are not going to do anything with the
reason why a person was excused, do you want to spend time entering it into
the databse?

I agree with your point. You are right that the reason for the
excused absence is not important.

The information that should be recorded so that accurate, useful
attendance reports can be generated:
1. Conference Date
2. Conference Type
3. Present, Absent, or Excused- where Present and Absent are specific
to a conference type and either Present or Absent is recorded for
every conference date and type, unless they are excused.

Thank you.