Listbox question

[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

 

[June7 via AccessMonster.com]

Not easy to design long distance but offer this and hope helps and doesn't
lead you astray.

Seems purpose of form is to add attendees to an event.

1. The Members table is a 'lookup' source for the Attendees record. No
objects should be bound to this table. The Members table would be used as a
RowSource for the Attendees MemberID listbox (or combobox) bound to the table
field. A selection of item would change the current record. To add a new
attendee need to invoke event to add record to the Attendees table first then
select the MemberID.
2. See 1
3. Yes, if form is properly set up. Form RecordSource must be a join of the
three tables. Create textboxes, listboxes, comboboxes bound to the
appropriate field of respective tables.
Combobox bound to Attendees.EventID (with Events.EventID as RowSource)
Textbox bound to Event.EventName (Locked = Yes)
Listbox or Combobox bound to Attendees.MemberID (with Members.MemberID as
RowSource) showing Name fields as additional columns. Make Last Name the
field users make choice on but column for MemberID should be the bound column.

4. EventID field of Attendees table will have to be manually populated for
existing records.

Set form AllowEditions property to Yes. Use form navigation bar to add new
record.

 

[June7 via AccessMonster.com]

Edit for previous post:
Instead of a textbox for the Event Name, add column to the Attendees.EventID
combobox.

Not easy to design long distance but offer this and hope helps and doesn't
lead you astray.

Seems purpose of form is to add attendees to an event.

1. The Members table is a 'lookup' source for the Attendees record. No
objects should be bound to this table. The Members table would be used as a
RowSource for the Attendees MemberID listbox (or combobox) bound to the table
field. A selection of item would change the current record. To add a new
attendee need to invoke event to add record to the Attendees table first then
select the MemberID.
2. See 1
3. Yes, if form is properly set up. Form RecordSource must be a join of the
three tables. Create textboxes, listboxes, comboboxes bound to the
appropriate field of respective tables.
Combobox bound to Attendees.EventID (with Events.EventID as RowSource)
Textbox bound to Event.EventName (Locked = Yes)
Listbox or Combobox bound to Attendees.MemberID (with Members.MemberID as
RowSource) showing Name fields as additional columns. Make Last Name the
field users make choice on but column for MemberID should be the bound column.

4. EventID field of Attendees table will have to be manually populated for
existing records.

Set form AllowEditions property to Yes. Use form navigation bar to add new
record.

I'm modifying a client's existing form. It's a continuous form,
showing multiple events. Each event is attended by multiple members.

[quoted text clipped - 38 lines]

Jeff

 

[Arvin Meyer MVP]

Instead of using a list box, I suggest a subform with a combo box.

If using it from the Events form have link on EventID and have the MemberID
selected with a combo box.

If using it from the Members form, link on MemberID and select the EventID
from a combo box.