If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access
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. -- W Dean Welch |
#2
|
|||
|
|||
Access
On Sat, 24 Dec 2005 09:14:02 -0800, "Dean Welch"
wrote: 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] |
#3
|
|||
|
|||
Access
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 -- W Dean Welch "John Vinson" wrote: On Sat, 24 Dec 2005 09:14:02 -0800, "Dean Welch" wrote: 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] |
#4
|
|||
|
|||
Access
John,
I would like some sample code to move the selections from a listbox into a related table. Thanks -- W Dean Welch "John Vinson" wrote: On Sat, 24 Dec 2005 09:14:02 -0800, "Dean Welch" wrote: 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] |
#5
|
|||
|
|||
Access
On Sat, 24 Dec 2005 11:09:02 -0800, "Dean Welch"
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Ambiguous Name Error | pm | Using Forms | 10 | June 5th, 2005 09:19 PM |
2002 vs 2003 | Patrick Stubbin | General Discussion | 2 | May 17th, 2005 07:27 AM |
The "Right" web hosting for data access pages?? | Ron Ehrlich | General Discussion | 9 | May 6th, 2005 05:49 AM |
Book recommendations, please | Top Spin | New Users | 2 | March 1st, 2005 12:43 AM |
is Access 2003 any better than XP? | Gorb | General Discussion | 4 | November 11th, 2004 09:44 PM |