Populate subform based on list selection (was Help needed!!)

[nycman]

Hi all,



I'm very very new in Access 2003. Trying to make a simple database with no luck.

I'm trying to make a form where I can select an event name from a lookup list and fill in a subform containing customers that attended to that event. I need reports by event name and by customer. I dont know If I make myself clear.

Thanks

[June7]

Remove EventID field from ParticipantTable.

The form/subform arrangement should be like:

main form bound to either Participants or Events

sub form bound to the junction table you call Main.

Why aren't EventLocation and EventDate in Events table? Don't repeat the EventName in Main table.

[nycman]

Remove EventID field from ParticipantTable.

The form/subform arrangement should be like:

main form bound to either Participants or Events

sub form bound to the junction table you call Main.

Why aren't EventLocation and EventDate in Events table? Don't repeat the EventName in Main table.

- Thank you so much for your answer. I need some clarification though: The user should pick up event name from a combobox or a lookup list, I need this so the user can have predefined list of events and it's easier to avoid typos and spelling errors. The participants should also be selected from a combobox or a lookup list. The reports that I need are:

1. by participant: same participant can attend two or more different events
2. by event: list of participants that attended the event. - there are going to be 2 or more same name events on different dates so this list should go by event name and then listed by date

So If I remove the eventName from the Main table how do I make a lookup list that will list the event names then?

thanks

[June7]

I was considering an 'event' as something that happens somewhere on some date and this would be a single record in events. But you want Events to be just a list of names? Then someone builds a record in Main that combines event name and a location and a date in any combination? Who will be allowed to create these combinations?

Consider:

tblEventNames
ID
EventName

tblEvents
ID
EventNameID (or the name itself, selected from combobox that uses tblEventNames as source)
Location
Date/Time

tblParticipants
ID
LastName
FirstName
etc

tblEventParticipants
EventID
ParticipantID

[nycman]

I was considering an 'event' as something that happens somewhere on some date and this would be a single record in events. But you want Events to be just a list of names? Then someone builds a record in Main that combines event name and a location and a date in any combination? Who will be allowed to create these combinations?

Don't think you don't have enough tables. Consider:

tblEventNames
ID
EventName

tblEvents
ID
EventNameID (or the name itself, selected from combobox that uses tblEventNames as source)
Location
Date/Time

tblParticipants
ID
LastName
FirstName
etc

tblEventParticipants
EventID
ParticipantID

Thanks again. The idea is this:

EventName EventLocation EventDate Participants

Event Name - 1. ER Training
EventLocation - New York
EventDate - 10/15/2012
Participant: 1. John Doe, 2. Jane Doe

EventName - 2. Trauma emergency
EventLocation - Queens
EventDate - 10/16/2012
Participant - 1. Jane Doe

eventName - 3. Adult ER
EventLocation - Brooklyn
EventDate - 10/16/2012
Participant - 2. John Doe

EventName - 4. ER Training (same event form #1 just different date and participants)
EventLocation - New York
EventDate - 10/22/2012
Participant - 1. Joe, 2. Bob, 3. Mario, 4. Victor

[pbaldy]

FYI, I changed your thread title to something more descriptive than "help needed".

[June7]

My suggested structure seems to fit that scenario. Up to you.

[nycman]

My suggested structure seems to fit that scenario. Up to you.

Thanks. I think that with your help I'm getting closer. How do I make a subform that will have a combobox to pick up participant names from the list of the participants (lookup)

[June7]

Main form bound to tblEvents.

Subform bound to the junction table tblEventParticipants. Combobox in the subform to select participant.

Review http://office.microsoft.com/en-us/ac...010098674.aspx

[nycman]

Thank you so much.