Access 2013 - Prepopulate a subform based on criteria selected in the main form PLUS a list of items from a query

Actually - I also think that on selecting the Add Members from Meeting, the call should be to use qryAttendanceExecutive if Type selected is executive, OR qryAttendanceFullBoard if Type selected is Full Board (these are the options with the unbound 'Type' (aka cboMtgType) field noted above.

Why?  Because there are 3 tables related to this:

1. tblMemberData that has their names and other pertinent information. 
2. tblBOD that contains members that are a part of the full board
3. tblCommitteesServed that contains members that are a part of various committees (including Executive)

So a way to run the query related to the two types, based on the cboMtgType selection - THEN to query based on the Board year so only those that were a part of that board/committee during that term will populate/append into the subform below.

The cboMtgDate will be auto-appended to the form based on the above, but is NOT related to the two queries.

NOTE:  The subform is called frmAttendAppend with the source of tblAttendance.

To be clear, Board of Director Members can also be a part of various committees.  But not required.  Committee members will always be a part of the Board.

Does that make sense?

I would not do it that way. I would just record who attended. So your subform should have a combobox to select the board member and then add their comments. You can filter the combobox for just the members who would be attending the selected meeting.

Firstly, the inclusion of the autonumber ID column in the primary key of tblAttendance is pointless.  As an autonumber its values are distinct, so the column is a candidate key.  By including it in a composite key that key becomes a trivial key as it contains a candidate key.  If you delete the unnecessary ID column from the table and include only the other three columns in the primary key, it will then not be possible to insert an invalid row which duplicates the values in these three columns, as this would violate the key and raise an error.

As regards the insertion of rows for all legitimate members into the table, I would in most circumstances tend to agree with Scott with regard to inserting rows only for those who attended, but you might have a valid reason for wishing to record non-attendees also.  It's for you to decide in the context of your business model.

You'll find an example of something similar in StudentLog.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, an 'All Students' button in the Courses form inserts all students registered for a course as attendees of a session of the course with the following code:

    Dim strSQL As String
    
    strSQL = "INSERT INTO CourseAttendances" & _
        "(StudentID,CourseID,CourseDate) " & _
        "SELECT StudentID, " & Parent.[CourseID] & ", #" & _
        Format(Parent.[sfcCourseSessions].[Form].[CourseDate], "yyyy-mm-dd hh:nn:ss") & _
        "# FROM CourseRegistrations WHERE CourseID = " & Parent.[CourseID]
    
    CurrentDb.Execute strSQL
    
    Me.Requery

As you can see this executes an INSERT INTO statement to insert rows into the CourseAttendances table by selecting those students registered for the course in question.  The date of the session (CourseDate) is inserted as a date literal into each row.

In my case the facility to insert all registered students is provided as it is often easier to insert all possible attendees and then delete the non-attendees, rather than inserting each attendee individually.  However, the methodology would essentially be the same in your case.  You would of course have to devise the SQL for the INSERT INTO statement so that it inserts the correct members in your case

I re-read your posts and I think you have other issues about structure than Ken mentioned.

"there are 3 tables related to this:

1. tblMemberData that has their names and other pertinent information. 
2. tblBOD that contains members that are a part of the full board
3. tblCommitteesServed that contains members that are a part of various committees (including Executive)"

tblBOD should probably not exist. What you have is a many relationship. One member can be on multiple committees and one committee can have multiple members. So, instead you should have your tblMemberData then tblCommittees and finally  junction table: tjxMemComm. tblCommittess would look like this:

tblCommittees

CommitteeID (PK Autonumber)

CommitteeName

(note: Full should be listed as a Committee in this table)

Then you have:

tjxMemComm

MemCommID (PK Autonumber)

MemberID (FK)

CommitteeID (FK)

So each member that is part of a committee has a record in this table for each committee they are on, including a record for the Full board. From there your queries would include tjxMemComm filtered for the committee the meeting is for.

The BOD list contains the 'history' of the board of directors back to 1970 something.  Each board member could serve on a 2 or 4 year term (depending on other factors).  This information contains and is treated very differently than committee participation, e.g., join dates, term limits, and a host of other things.

The tblMemberData uses the MemberID through the db to link.  That is the one to many.

This table also contains potential candidates for the board,

the tblBOD again contains all the Board information as it relates to the specific member.

Note that this db has been in existence for 2 years already and there are a TON of reports, queries, forms, etc that are involved.

But even if I did have it as you suggest, I still don't know how to get the form to pre-populate with the current board members so we can note who attended, was absent, or who sent in a stand by. 

I would like to be able to click the button - have all the 'current' members for the specified board or committee show up so the Assistant can quickly go down the list and selected the attendance option related to each one, and note any comments if necessary.

I want the table to prepopulate with the meeting type and meeting date that I selected for the filter.

I need this information to be tracked in an Attendance table so we can track and report historically.  Basically, if you're a part of the board and don't attend 1/2 the meetings, perhaps you shouldn't be on the board or committee.

Can anyone help me with 'that' part?  Thank you.

Let me take a look at this one - I saw the next responds before I saw this one - sorry.  (regarding my statement of tell me how to do what I'm looking to do)

We need to know a lot more about the structure of the individual tables and the relationship types between them before we can advise you further with any degree of confidence.  The easiest way to supply this would be by posting an image of the relationships window, set out in such a way that the full structure of each table is visible, and the relationships between the tables are clear.  The image below, for instance is of the model for the StudentLog demo which I mentioned earlier.  As you can see the overall structure of the model is readily apparent from this.



To assign members to a meeting you will need to build an 'append' query such as that illustrated in my earlier reply for assigning students to a course session.  In essence this means that you are able to build an SQL statement which selects the MemID values of those members who are eligible to attend the meeting, along with the relevant MtgType and MtgDate values, which can be derived in various ways.  The SQL statement I posted gets the analogous CourseDate and CourseID values by means of parameters which, in the case of the CourseDate, builds a date literal by referencing a control in the parent form, and in the case of the CourseID, by restricting the CourseRegistrations table by referencing another control in the parent form in the WHERE clause.

Again, why do you need to prepopulate ALL the members? Why not just record those who attend. To make it easier, you can use a Multi-Select Listbox. The user can then just click on the members who attended and then you can run an Append query to just append those who were selected.

I apologize for not responding earlier.  Large project, and out of office at a conference.

They want to track very specifically who attended, who didn't, and if they didn't attend, did someone come in their place, or were they just absent.

I still have to take a look at all the previous posts, but have to finish another project first.  Unfortunately this has to be placed on the back burner for a bit.

I will return with images and such in a while.  Thank you for your time and patience.

Happy Holidays