Listbox questions

[Jeff]

I'm modifying a client's existing form. It's a continuous form,
showing multiple events. Each event is attended by multiple members.
There is a relationship record between the two tables.
Event
EventID
Event Name

tlbAttendees
EventID
MemberID
Comments

Members
MemberID
Last Name
First Name

Currently, they list the members in a text box. I want to be able to
have them select members from a listbox; this will eliminate typing
errors and former members. I can add the listbox with no problem to
display the names, but I have questions about updating these records.

I have several questions:

1) If the user of the form clicks on a name, how can I create the
tblAttendance table entry? Right now, the data is from just the
Members table with no mention of the tblAttendance table entry.

2) Since this is set to a continuous form, it appears that every time
I click on a member name, it appears for each Event on the form. Is
this just a matter of tying the listbox to that event or ?

3) When I bring up an Event, should the names already selected appear
or do I have to do something to this?

4) In moving forward with this, is there a way to convert the existing
Event records (with their text box list of names) to this format? Can
I create a query to update the tblAttendance records?

Thanks

Jeff

 

[tina]

looks like the tables, as posted, are correctly normalized and related, with
tblAttendees serving as the join table between tblEvents and tblMembers.
suggest you re-think the form design. classic setup for entering data is
using a mainform/subform, with the mainform bound to one of the parent
tables, the subform bound to the join table, and a listbox or combobox
control in the subform with RowSource using the other parent table.

so, 1) make sure the three tables are properly related in the Relationships
window, with referential integrity enforced. 2) build a mainform bound to
tblEvents, as SingleForm view - frmEvents. 3) build a subform, bound to
tblAttendees - sfrmAttendees. 4) add a combobox control (or a listbox,
depending on whether the view is Datasheet or Continuous forms) to
sfrmAttendees, and set its' RowSource to tblMembers. 4) add a subform
control to frmEvents, and set its' properties as follows:

Name: ChildAttendees
SourceObject: sfrmAttendees
LinkChildFields: EventID
(this refers to the foreign key field in tblAttendees)
LinkMasterFields: EventID
(this refers to the primary key field in tblEvents)

in Form view, frmEvents will show the related attendees for each event
record, as you move from one record to another. when you add attendees, in
the subform, each record you enter will be automatically linked to the
current event record showing in the mainform. and as you choose an attendee
name in the combobox (or listbox) for the current subform record, that
record will also be linked to tblMembers.

hth

 

[Tom van Stiphout]

You have a standard many-to-many relation between Events and Members,
with Attendees being the junction table.
The standard way to implement this in Access can be seen in the
Northwind sample application, Orders form, where you can see a M:M
between Orders and Products via OrderDetails.
In short you use a parent form on Events, and a subform bound to the
junction table, Attendees in your case. In it, you provide a dropdown
on Members.
Or you have a parernt form on Members, a subform on Attendees, and in
it a dropdown on Events.
Everything can be created declaratively, without a single line of
code.

-Tom.
Microsoft Access MVP