I am working with a form that includes a subform. The main form includes individual member information. The subform assigns the individual to a variety of committees. Within the subform, one field is a list box to select which committee(s) to assign the member to. The subform will be a datasheet or in the format of continuous forms in order to assign an individual to more than one committee. The problem I am having is that some committees sometimes also have related subcommittees. My database design has the following:
TblMembers (with all member info including MemberID)
TblCommittees (including ID and all info)
TblSubcommittees (related to TblSubcommittees)
TblMember Assignments (related to TblMembers, includes fields for MemberID, CommitteeID and SubcommitteeID, though neither fields are required)
In my current subform, I am attempting to use a drop-down list of committees to make assignments for individuals. This is working and populating the assignments table. I also have a drop-down for subcommittees, which also works, but I would like the data for subcommittees to be limited to those subcommittees that are associated with whatever is selected for the main committee drop-down. This *seems* to work in the beginning of the datasheet, but as extra assignments (rows) are added, the subcommittee list doesn't display properly.
As a result, I decided subcommittees should be assigned in a separate form, so that after I assigned a member to a committee, there is a button or link I can click on to open the Subcommittee Assignment form and show ONLY those subcommittees related to the main committee assignment just made (Where TblCommittees.ID = TblSubcommittees.Committee ID for the committee just assigned). But somehow I am not telling Access what the value of Committees is and so the Subcommittee form does not display results based upon the latest assignment.
I'm not sure of the best way to go about this, but I know somehow the timing the data storage is affecting this. I think there may be a way for me to get the Subcommittee data based on the very LAST or LATEST assignment made in TblMembers Assignments, and I think that command may be the way to fix this problem.
I cannot recall how to use the command in this context, but if anyone can remember, or has any other suggestions, I'd really appreciate it.
Thanks
TblMembers (with all member info including MemberID)
TblCommittees (including ID and all info)
TblSubcommittees (related to TblSubcommittees)
TblMember Assignments (related to TblMembers, includes fields for MemberID, CommitteeID and SubcommitteeID, though neither fields are required)
In my current subform, I am attempting to use a drop-down list of committees to make assignments for individuals. This is working and populating the assignments table. I also have a drop-down for subcommittees, which also works, but I would like the data for subcommittees to be limited to those subcommittees that are associated with whatever is selected for the main committee drop-down. This *seems* to work in the beginning of the datasheet, but as extra assignments (rows) are added, the subcommittee list doesn't display properly.
As a result, I decided subcommittees should be assigned in a separate form, so that after I assigned a member to a committee, there is a button or link I can click on to open the Subcommittee Assignment form and show ONLY those subcommittees related to the main committee assignment just made (Where TblCommittees.ID = TblSubcommittees.Committee ID for the committee just assigned). But somehow I am not telling Access what the value of Committees is and so the Subcommittee form does not display results based upon the latest assignment.
I'm not sure of the best way to go about this, but I know somehow the timing the data storage is affecting this. I think there may be a way for me to get the Subcommittee data based on the very LAST or LATEST assignment made in TblMembers Assignments, and I think that command may be the way to fix this problem.
I cannot recall how to use the command in this context, but if anyone can remember, or has any other suggestions, I'd really appreciate it.
Thanks
