Access

[Guest]

I am trying to write a database to record which employees are present during
meetings. I have an "employee" table and an "event" table and an
"attendance" cross table between them that has a combination of foreign keys
from each of the parent tables. I want to show the event table in a main
form and the attendance table in a subform. I want to be able to click a
boolean field called "present" beside each employee to indicate those
employees present. The problem is that I want to show all employees in the
subform at once without having to select them individually, indicate whether
they are present or not in the "present" boolean, then have the event number
from the main form pass to the subform and post those records to the
"attendance" cross table. I have tried many things without total success.

 

[John Vinson]

I am trying to write a database to record which employees are present during
meetings. I have an "employee" table and an "event" table and an
"attendance" cross table between them that has a combination of foreign keys
from each of the parent tables. I want to show the event table in a main
form and the attendance table in a subform. I want to be able to click a
boolean field called "present" beside each employee to indicate those
employees present. The problem is that I want to show all employees in the
subform at once without having to select them individually, indicate whether
they are present or not in the "present" boolean, then have the event number
from the main form pass to the subform and post those records to the
"attendance" cross table. I have tried many things without total success.

One way to do this is to base the Subform, not on the attendance table
itself, but on a Query joining the employee table to the attendance
table using a Left Outer Join. Create a Query joining the two tables;
join on the EmployeeID (or whatever is the linking field). Select the
join line and select Option 2 - "Show all records in Employees and
matching records in Attendance". Include BOTH the EmployeeID from
Employees and the EmployeeID from Attendance in the selected fields
(this will let Access autofill the ID).

Include *some* field - it can just be a Yes/No Attended field from
Attendance - in the query.

Then on the subform you will see all employees, initially with NULL
values for the Attendance field. As you go down the list clicking the
checkbox, Access will create a new record in the attendance table for
that employee.

Alternatively, you can use a multiselect Listbox showing all
employees. Post back if you'ld like some sample code to move the
selections from a listbox into a related table.

John W. Vinson[MVP]

 

[Guest]

John,

Thanks so much for taking the time to help me. However, I have tried all
those steps except the multiselect listbox. One of the problems is trying to
pass the event ID number from the main form to the subform. I created an
outer join and even tried a left outer join once between the employee and
attendance table and joined on the employee ID. When I tried that I couldn't
seem to find a way to get the event ID to pass from the main form to the sub
form. I tried a macro with a setvalue and a requery in it on the "update"
event of the main form, but it didn't work. But, if you have time, please
keep the dialogue coming, you certainly have some insightful ideas and I'm
sure it will lead to success. Looking forward to your next post. Thanks,
Dean

 

[Guest]

John,

I would like some sample code to move the
selections from a listbox into a related table. Thanks

 

[John Vinson]

John,

Thanks so much for taking the time to help me. However, I have tried all
those steps except the multiselect listbox. One of the problems is trying to
pass the event ID number from the main form to the subform. I created an
outer join and even tried a left outer join once between the employee and
attendance table and joined on the employee ID. When I tried that I couldn't
seem to find a way to get the event ID to pass from the main form to the sub
form. I tried a macro with a setvalue and a requery in it on the "update"
event of the main form, but it didn't work. But, if you have time, please
keep the dialogue coming, you certainly have some insightful ideas and I'm
sure it will lead to success. Looking forward to your next post. Thanks,
Dean

Hm. Using the EventID as the master/child link property of the subform
should work.

If it doesn't, or if you want to try the listbox approach anyway,
here's some code from an app I've built; you'll need to adapt it to
your table and fieldnames of course, but the logic should work.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
' selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table,
' newly cleared rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID
' combination is currently in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson[MVP]