UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Attendance Form, Access 2013    
 
   
hefty
post Jan 10 2017, 03:57 PM
Post#1



Posts: 21
Joined: 30-March 16



Hey UtterAccess,

I want to build a form that will show a list of all the members that are suppose to be attending a meeting and then have the option to change their attendance of the meeting. I can't think of how to properly create the form, i have done a little searching on this topic and i think the general idea i want is a form that will allow the user to select the committee and the meeting, then have a sub-form display all the members for the selected committee, and a field to update the status.

My table structure is as follows;

tblMember
*memberid
first name
last name
etc..

tblCommitteeMember
*committeememberid
memberID
committeeID
positionID

tblMeeting
*MeetingID
committeeID
MeetingDate
Meetingnumber ( right now its a value list, I'm going to update to its own table)

tblAttendance
*AttendanceID
MemberiD
MeetingID
Status

Is this possible given my table structure, or what else can i do so the user can easily selected the committee and have the members displayed and then select their status.

Thanks UtterAccess!
Go to the top of the page
 
doctor9
post Jan 10 2017, 04:25 PM
Post#2


UtterAccess Editor
Posts: 17,610
Joined: 29-March 05
From: Wisconsin


hefty,

Assuming that "status" means either present or absent, I'd remove the field entirely. If the person attends the meeting, add a record with the appropriate memberID/meetingID values. If they don't attend, don't add a record.

For the data entry form, have a single main form based on the tblMeeting table. Then, add a continuous subform based on the tblAttendance table, using the MeetingID fields as the parent/child fields for the subform. Use a combobox based on a query using the tblMember table and the tblCommitteeMember table and bind it to the MemberID field. The tblCommitteeMember table is included just to filter the list of -possible- attendees down to just the people in this meeting's committee. You could also include the tblAttendance in the combobox's query, I think, to list only people who aren't already entered as "present" for the meeting, so the list would get shorter and shorter with each person added to the meeting.

An alternative idea would be to use an unbound Listbox control instead of a subform with a combobox. Set it to Multiselect mode. In the main form's Current event, add the committee members associated with this meeting's committee. Then, select the members who have a record in the attendance table. Finally, in the BeforeUpdate event, loop through the .Selected people and if they're not already in the tblAttendance table for this meeting, add them.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
hefty
post Jan 11 2017, 10:26 AM
Post#3



Posts: 21
Joined: 30-March 16



I would like to track if they attended, not attended or phoned in. Does doing so limit my ability to create the form you outlined below? On that topic thank you for the suggestion, I'm going to give it shot now.
Go to the top of the page
 
ScottGem
post Jan 11 2017, 01:41 PM
Post#4


UtterAccess VIP / UA Clown
Posts: 32,136
Joined: 21-January 04
From: LI, NY


I built an app for use by my company. The App is used for mass meetings. The app allows for the creation of a meeting and then sessions of that meeting. It also allow for creating a list of employees to invite to the meeting. The employee then logs into the app, selects themselves from a list of employees, then selects the session they will attend. There names are then added to the session roster.

Does this sound like something that would work for you?

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
doctor9
post Jan 11 2017, 04:20 PM
Post#5


UtterAccess Editor
Posts: 17,610
Joined: 29-March 05
From: Wisconsin


hefty,

> I would like to track if they attended, not attended or phoned in.

Ah, that explains the "status" more clearly. In that case, I'd just make your Status field a Long Integer datatype, name it "lngStatusID" and then create this table:

tblStatusTypes
StatusTypeID [Autonumber, Primary Key]
strStatus ("Attended in person", "Phoned In", "Absent")

Then, when it comes time to create a new meeting, once you select the committee, you can use code to auto-populate the tblAttendance table with all of the members of that committee, with the default status of "Absent". Then, as they show up in person or join the conference call, you can change their status accordingly.

I recommend using a table of status types because it allows you total flexibility. Somewhere down the road, somebody's going to suggest a fourth status type for the report like "Left halfway through" or something, and having this table will make the change extremely easy (i.e. add a record to the table of status types).

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
hefty
post Jan 12 2017, 10:38 AM
Post#6



Posts: 21
Joined: 30-March 16



Dennis,

Thank you, i was able to create the form based off your instructions, it isn't as pretty as i would have wanted it to be but it serves its purpose and is a huge improvement over the previous form i had.

Scott,

Your app, does sound like it would be of use to me. I would like to check it out if possible. But as i said above I was able to make something that worked.

Thank you both!
Go to the top of the page
 
ScottGem
post Jan 12 2017, 11:37 AM
Post#7


UtterAccess VIP / UA Clown
Posts: 32,136
Joined: 21-January 04
From: LI, NY


I will have to convert the app to a more generic version which will take some time.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2017 - 08:27 AM