Sub-Form - On click yes/no add record to table

[dmgoetz via AccessMonster.com]

I have a form that is the main information for an event, I then have a sub-
form that I want to list all the members in my db. With that list I want to
click a yes/no and add the members to the specific event I am viewing in the
form. This will need to add this MemberId and EventId to my EventsToMembers
table.

I have separate tables for member, events, and EventsToMembers.

Any help that you could provide would be appreciated. Thank you

Darren

 

[tina]

well, that's relatively easy, just takes several steps to set up.

add a Yes/No field to tblMembers, which i'll call ckAdd. base the subform on
tblMembers. make sure the subform is not linked to the main form (the
LinkChildField and LinkMasterFields properties of the subform control,
within the main form, must be blank when you look at them in the main form's
Design view).

the simplest setup would be to add a command button to the main form. on the
button's Click event, add code which runs an Append query to add only those
members with a checkmark next to their name to table EventsToMembers; make
sure the Append query pulls the EventID from the main form. you can add an
Update query to the same button procedure to set the ckAdd field in
tblEmployees to False, and then requery the subform, so that the members
list in the subform is "reset" and ready for the user to add members to the
next event.

if you don't want to add a field to tblMembers, you can accomplish the same
thing using a separate table to hold the checkmarked records, and base the
subform on a query. if you want details of this alternative, post back.

hth

 

[dmgoetz via AccessMonster.com]

I would rather not add the field to tblMembers, I would like to base it off
of a query. Can you please help me with that? Thanks D

 

[tina]

ok. we'll start from the beginning, so disregard what i posted before and
i'll just lay it all out in this email (even the parts that may be the same)
for simplicity.

create a new table, as

tblChecks
MemberID (foreign key from tblMembers, make it the primary key of this
table)
ckAdd (Yes/No data type)

link *from* tblMembers *to* tblChecks in the Relationships window. this will
be a one-to-one relationship.

create a query based on the two tables, with a LEFT JOIN *from* tblMembers
*to* tblChecks, on the common MemberID field. include both MemberID fields
in the query's output, as well as all the other fields you need from
tblMembers, and the ckAdd field from tblChecks.

base the subform on the query. the only controls you need in the subform are
the control(s) showing the member name and a checkbox control bound to the
ckAdd field. make sure the subform is not linked to the main form (the
LinkChildField and LinkMasterFields properties of the subform control,
within the main form, must be blank when you look at them in the main form's
Design view). when the user checkmarks a box in the subform, that MemberID
is automatically added to tblChecks as a new record.

the simplest setup would be to add a command button to the main form. on the
button's Click event, add code which runs an Append query to add all records
in tblChecks to table EventsToMembers; make sure the Append query pulls the
EventID from the main form. use a Delete query to delete all records from
tblChecks, either as soon as the append is completed or at whatever point
you want a "fresh" member list to show in the subform, and then requery the
subform.

hth