Junction Table is Not Populating

Wyldhare

New member
Local time
Today, 05:49
Joined
Apr 17, 2009
Messages
6
Hello,

I am fairly new to all this and I am trying to create a database in Access 2003.

I have three tables:

EVENTS - Primary Key EVENT_ID
PEOPLE- Primary Key PERSON_ID
JUNCTION - Primary Keys EVENT_ID & PERSON_ID

I can have many people associated to one event. And I can have many events associated to one person. So I think I have done this part right.

For relationships, EVENTS has a one-to-many relationship with JUNCTION.
PEOPE has a one-to-many relationship with JUNCTION

I created a form with EVENTS as the main form, then a subform of the JUNCTION and inside the junction subform, is a subform of PEOPLE.

When I try to input a new event and person, that data is saved to the EVENTS table and the PEOPLE table, but nothing happens to the JUNCTION table.

Help! What am I doing wrong? Why is the JUNCTION table not populating and how do I fix this! :eek:

Thank you so much for the help!!
-Jane
 
Welcome to the site. The table structure looks fine, but why is people a subform of the junction form? If the junction form stood alone, I would expect combos from each of the source tables, each with its rowsource from the source table but bound to the junction table. I suspect you don't have a control bound to junction/people in your form.
 
So PEOPLE should be a subform of EVENTS and I just leave out JUNCTION?

If so, then how does JUNCTION get populated?

And yes, I don't have a control binding JUNCTION and PEOPLE.

I hope I don't sound like a complete newbie, but I pretty much am!

Again, thank you for your guidance!

-Jane
 
No, I don't think you should have a people subform on the junction form. Just a combo bound to that field. I can see junction as a subform of either events or people, with master/child links keeping them in sync. Whichever it's a subform of, the other should just be a combo on the junction form, not another subform. Post a sample db if you get stuck.
 
Thank you Paul. I think I will have to read up on Combos, I have not done anything with them yet. I suspect once I understand them, I will understand what you are suggesting.

I think you are suggesting EVENTS as main form, with PEOPLE as subform of EVENTS. Then, make JUNCTION a subform of PEOPLE? Ugh, I think I am sounding as stupid as I feel right now! :o

This gets easier right?
-Jane
 
Yes Jane, it does get easier. :p

The key about combos that's relevant here is they can get their selections from one table (row source), but save the selected item to another (control source).

It might be simplest to do this: create a stand alone form bound to the junction table, with the 2 combos I described. You probably want it in continuous forms view. You can use the combo wizard to create the combos, which should step you through the process. That form should let you add records to the junction table.

Once you have that working, if for presentation purposes you want to have that form as a subform of either an event form or a people form, you can add it. In either case, people and events will never be form/subform with each other, because they have no direct relationship. You will either have people/junction or events/junction.

Clear as mud now?
 
Oh actually that made more than a few lightbulbs turn on! Thank you! I will go back and beat on this some more. Thank you again and I hope you have a wonderful weekend!
 
You too Jane! Post back if you get stuck.
 
Okay, so my user is going to have to:
1. Go to the EVENT form and input the event information
2. Go to the PEOPLE form and input the info on each person who attended.
3. Then, go to the JUNCTION form and using a pair of combo boxes, tie the PEOPLE and EVENT to each other, as appropriate

It is clunky, but I guess it will work. I was trying to do something with an easier GUI. I had hoped to have the user input the event info, then add all the people who went, on one form (well to the end user, it would seem like one form.)

Do I seem to have it, or am I still off?

-Jane
 
There are numerous ways to skin a cat, much to the cat's chagrin. :p

Are the people they would enter already in the people table as a rule, or not? If so, I'd have an event form with the junction table as a subform. Master/child links would keep them in sync, and the user could just add people in the subform. You can use the Not in List event of the people combo to add the occasional person who isn't already in the table.

You can also add people in bulk with a multiselect listbox, like this:

http://www.baldyweb.com/MultiselectAppend.htm

In that example, the other value would be your event. If the user is entering a lot of people for the first time, those methods might prove too clunky, and we can dream up another one.

I'm about to take off, but I'll try to check in later.
 
Hmm. No. I think you missed some of what Paul was saying.

You have a main form: Either bound to events, or to People. Let's say you bind it to Events.

You have a subform bound to Junction, inside the form which is bound to events.

Now, there is an EventID field in both Events, and in Junction. That field is the key between your main form and your subform. Make sure that both the main form and subform both have a text box bound to that field: you can set these to be invisible. Whenever you create a record in the subform, the record will automatically receive the same EventID as is shown in the mainform.

The remaining information, the personID, needs to be entered by the user.

To do this, create a combobox control in the subform. This combo stores the selected value in the PeopleID field of the Junction table (this is the combo's control source). But what the user SEES when they are looking at the combo is a list of names wof the people who you already have stored in your People table.

To do this, the combo contains a built-in query (or a list of hard-coded values) in a property known as the row source.

This query essentially draws one or more columns of data from your people table. The first column is the PeopleID value. The second column might be a concatenation of the person's surname and first name etc. (Thus it is a calculated field in the query)

A combo control has properties relating to these columns of information in the row source.
One property (Column Count) defines the number of columns of data being returned to the control.
Another property (Column Widths) sets the display width of each column. The combo control displays the first visible column in the row source.
A third property (Bound Column) determines which column in the row source contains the value which is actually stored by the control in the field that it is bound to.

So, in the case of your subform, there are two columns in the row source. The first column has a width of 0 and is therefore invisible to the user. The second column which contains the name of the person gets a width like 2 inches (or whatever) and is therefore visible. The 'Bound Column' property will be set to 1, which means that the value in the first column (People ID) is what is actually stored when the user selects a row from the list.

This means that the user sees a name, but actually stores an id. The id is selected from a list provided by information in the people table, but stored in the junction table.


Now, you can set the subform to be in continuous form view. This means that you can add as many records in the subform as you wish.

Hopefully that helps clarify the issue. :)
 
Thank you Craig for the additional input, it was really helpful. Sometimes just hearing the same information in someone elses words can be a huge help.

I am back at work today, so I hope to take all this combined info and make another stab at this thing!

I really appreciate your time and help!

Best,
-Jane
 
Hi,

I'm trying to tackle a similar problem.

Difference is that in my subform I want to be able not only to select existing people, but also add people that are not yet included in the people table.

Is there a way to accomplish this without having to use a popup showing a separate form linked to the people table?

Thanks in advance.

Kris
 

Users who are viewing this thread

Back
Top Bottom