Table Relationship / subform

[Jerry8989]

I have two tables called
Tab1 with ID and Name
Tab2 with ID, value, text
Within the table def I made both Id's the primary keys.
I went to the table relationships and dragged from Tab1 ID field to Tab2 ID field.

Now what I want to do is have a subform from TAB1 and a subform for TAB2.
When I load I only want values in TAB2 that have the ID of the selected row in subform for TAB1.

I have tried this with two subforms but when I select a different row in TAB1, TAB2 doesn't change it still shows everything. I thought creating the relationship would enforce this?

If i'm confusing you i'm sorry

Any help would be greatly appreciated

 

[Scooterbug]

You have to link the subform with the form via the ID fields. If you used the Wizard, it should of prompted you to define the Parent/Child Link. If not, you can manually add it through the subform properties.

 

[Jerry8989]

Scooter,
Thank you for your reply. The only way I can do it is with the a main form and sub form?
I can't have 2 subforms(1 master, 1 child) that I can link without having a main and sub?

I have to lay it out like this

TAB1 TAB2
ID NAME ID Value Text
1 TEST 1 1 Test
2 2 Test2
2 Second
3 Third
4 Fourth

This should display when row 1 in TAB1 is selected
Can I do this nicely with a main form sub form?
Thank you for your help

 

[Scooterbug]

Not sure that I follow...but here is an example:

Say Table1 contains general information on a person. Their Name, Address, Phone Number, Date of Birth, etc.

Now, say Table2 contains data on their shopping habits. The persons ID number (This is your foreign key...it will equal the value of the Primary key for the person doing the shopping) Where they shop, When they shopped, how much they spent. Now, since the person can shop multiple times, this is considered a One-to-Many Relationship.

With a Form/Subform situation, the Person's information would go on the main form. You can add a subform with all the shopping habit data. The Parent or Master field would be the PersonID from the main form, and the Child field will be the PersonID from the subform.

So in your case, you would have table1 as the form, and table2 would be the subform. The linking fields would be the Primary key of table1 and the foreign key stored in table2.

Am I close?

 

[Jerry8989]

Scooter,
Again thank you for taking the time out to help me.

Based on your example i'm required to show all people in one subform and their habits depending which row has focus on the first subform within subform two. This layout is based on my bosses layout. He wants to see everything from table 1 then when he selects a row the data from tab2 will appear.

Thanks

 

[Scooterbug]

Ok...in that case here is what I would do:

I would use a list box for the list of people. The bound column will be the ID of the person. You can then put a subform on the form with the records for table2. The Master Link field will be the list box and the Child link field will be the PersonID from table2. In the On_Click event for the list box, put the following:

me.SubFormContainerName.requery

Where SubFormContainerName is the name of the container that holds the subform...not the name of the actual subform.

 

[Jerry8989]

Scooter,
Thanks for the idea. It was perfect till I realized the user has to be able to add new people into the person table and new shopping habbits for the new person. So close.

Is parent child my only option? I can't do continous form like that so it's one record at a time from TAB1. That won't work.

Thank You so much

 

[Scooterbug]

Whenever you utilize a subform and want it to relate to something else you need to establish the Parent/Child relationship. Without it, the subform will display all the records.

As for having to have the ability to add records for both the Persons and Shopping habits it's a matter of having 2 subforms and the listbox. Each Subform will relate to their respective tables (Table1 and Table2). You can keep the list box and actually use that as the Master Field for both of the subforms.

 

[Jerry8989]

Scooter,
Thank you so much for your suggestions. I will try it out and let you know.

Thank you again for all your help

 

[Jerry8989]

Scooter,
How can I tie the listbox to the subforms?

Thank You

 

[Scooterbug]

Have the bound column for the listbox be the ID of the person. You can then use the list box as the parent field and the ID field of the subform as the child field.

Or use a query for the source of the subform, with the listbox as the criteria (forms!FormName!ListBoxName) for that query. Then, on the On Click event for the listbox, requery (me.SubFormContainerName.requery) the subform.

 

[Jerry8989]

Scooter,
That was it. I used a text box that I would fill from the person subform within the Current event. Then the shopping habbits subform link master field was the text box.

I ran into this site and it said pretty much what you told me.
http://www.tek-tips.com/viewthread.cfm?qid=1548442&page=9

Thank you so much for all your help.

 

[Scooterbug]

Scooter,
I used a text box that I would fill from the person subform within the Current event. Then the shopping habbits subform link master field was the text box.

I've done that as well in the past. The key is to remember to requery

Glad it worked out for ya.

 

[Jerry8989]

Scooter,
When should I be requering?

I notice an issue when I add a new row to the parent table. When I add a new row the ID (autonumber field) goes up by 1 and I enter my text but on the child subform it's blank and the id field isn't populated. I have to click back on the row I just added for the child subform to show me a blank row with the ID field populated with the correct ID.

Thanks

 

[Scooterbug]

When should I be requering?

I would requery on the On Click event of the listbox.

I notice an issue when I add a new row to the parent table. When I add a new row the ID (autonumber field) goes up by 1 and I enter my text but on the child subform it's blank and the id field isn't populated. I have to click back on the row I just added for the child subform to show me a blank row with the ID field populated with the correct ID.

Thanks

Do you mean the Parent Form? Data shouldn't be entered directly into the table. If it's the parent form, then you can do the requery on the After Update event.

 

[Jerry8989]

Scooter,
I'm not using my list box anymore. I'm just using two subforms with a textbox on the main form. The oncurrent event on the Parent subform fills the textbox and the Link Master field of the child subform is equal to that text box.

On the parent I have two columns ID & Name. ID is an Autonumber field.
So when I put in a new name say I get ID 6. My text box should now have 6 in it and my child subform should show 6 as the ID value for any new rows.

Thanks for your patience

 

[Jerry8989]

Scooter,
I got it working by setting my textbox equal to my id and then requerying the child subform within the After Update event.

Thanks so much so far it seems to be doing what I want.

Thank You
Jerry

 

[Scooterbug]

Ok, let me see if it got it now.

You have a form that contains the following controls:

Text Box (used to store the ID for the Name)
Subform 1 (Shows the list of names and the ID number)
Subform 2 (Shows attributes related to a person)

You should put the requery on the After Update event of Subform1. This way, when you add a new record, when it updates it populates the text box. You should then put in a requery for Subform2, since the ID number in the text box had changed.

 

[Jerry8989]

Scooter,
That is pretty much it.

On my after update event within subform1 I do the following
Me.Parent.txt = Me.id
Me.Parent.frmSub2.Requery

I don't have any code within subform2

Thank you