Not Sure If Tab Ctrl Is the Right Way

[painterz]

Hi All,

I have a working db, forms, and code with a minor glitch. I have a main form for street address and subform for the family. A problem arose when I had 2 families at one address; also, when I have an owner and renter--so multiple families per address is causing me a problem.

I thought I'd use a tab control to show each family at the address and designate Family1, Family2, Family3, and Family4--with a flag on the subform to determine owner or renter. Hide the unused tabs and load only the tab chosen.

The tab control doesn't seem to have a record source--I thought I'd be able to limit the display of families at this level using a query. I added a subform container and set the parent/child relationship. I thought I could control the record source at this level for families at this level.

It seems there should be a record source at the level (tab control) that is "grouping" the data. Am I missing something here? I'm using Access 2010, so navigation form doesn't seem to be available. Am I going in the right direction using a tab control? Is there a way to make the tabs less ugly? Is there a better way to do this?

Thanks

 

[Ranman256]

Address shouldn't be a problem. Just show a query where 1 address has many families.

Tabs just hold the sub form. The subForm changes when the tab does.

 

[MarkK]

You are describing the problem in terms of the user interface, but to deal with the problem at its root, we need to understand how the tables work, and how they are related to each other.

 

[painterz]

Table structure:

Address-->Family-->People-->…beyond

I have designed my underlying queries to be one to many between Address and Family, then Family and People, and People and beyond.

I had thought the tab control would act as the intermediary (Family) between Address and People but I think I'm missing something here.

 

[MarkK]

So one Address can have many Families? What is the thinking behind that?

I would expect one family to have one address, and in this case the Address might even be in the same table as the Family. If not, I would expect the Family to be on the One side of the relationship, and the Address on the Many side, so . . .

Family -> Address

What is your thinking behind doing this the other way around?

 

[painterz]

Sorry for the slow response. When I originally designed this db, I was short sighted and saw it as one house to many people. Then one family's child moved back home with her family and I now realize it should be one house to many families then the many people. I've attached a copy of my fundamental tables.

I set this up as one address to many people bc, the # of addresses in the neighborhood is fixed, so the address became my main form, and the people became my subform. I've attached a screen shot of my current form (the one that works). This is my original design--one address and many people.

Now, integrating families, I think I need a tab control so I can show one family per tab. However, the calls from the OnLoad on the main form to the subform no longer work even though they haven't been renamed. I've included a screen shot of this form too.

Thanks

 

[MarkK]

I recommend fixing the table relationships before the user interface. If the data model is not sufficient, everything will remain a struggle to implement; everything will be a work-around.

 

[painterz]

I think my underlying tables are sound. The underlying query to the subform works. I haven't been able to find much info on tab controls to know if it's the right direction for me to pursue and I haven't found many examples that fit my project. Everything I've seen is sales related and each tab has different forms rather than the same form with different info.

This is my event procedure for the OnOpen (sorry, not OnLoad as I previously stated) that is failing:

If Forms![Neighborhood Input Form].[Neighbors subform].[PKRelationship] = 3 Then 'viewing a child's info
Form![Neighbors subform]!TeenJobs.Form.Visible = True
Form![Neighbors subform]!ckParentOK.Visible = True
Form![Neighbors subform]!txtchildphone.Visible = True
Form![Neighbors subform]!lblchildphone.Visible = True
Form![Neighbors subform]!lblvolunteer.Visible = False
Form![Neighbors subform]!ckvolunteer.Visible = False
Form![Neighbors subform]!lblTT.Visible = False
Form![Neighbors subform]!ckTT.Visible = False
Form![Neighbors subform]!Newsletter.Visible = False
Form![Neighbors subform]!lblnewsletter.Visible = False

Else
Form![Neighbors subform]!TeenJobs.Form.Visible = False
Form![Neighbors subform]!ckParentOK.Visible = False
Form![Neighbors subform]!txtchildphone.Visible = False
Form![Neighbors subform]!lblchildphone.Visible = False
Form![Neighbors subform]!lblvolunteer.Visible = True
Form![Neighbors subform]!ckvolunteer.Visible = True
Form![Neighbors subform]!lblTT.Visible = True
Form![Neighbors subform]!ckTT.Visible = True
Form![Neighbors subform]!Newsletter.Visible = True
Form![Neighbors subform]!lblnewsletter.Visible = True
End If

It keeps getting hung up on the first line of the If statement and I've made adjustments to the reference with no change. If I can get this working then I can get to the next hurtle.

 

[MarkK]

But Address->Family where address is one, and family is many, can't be right. A family does not belong to an address, and address belongs to a family.

 

[painterz]

I see it as the address never changes but the families come and go. In a company, I think the db would be driven by the people table but this is a neighborhood, so I see the db being driven by the address table.

 

[painterz]

BIG HEAD THUNK!!! My "Neighbors Subform" is sitting in my "ChildFamily1" subform container. I've made the reference change and now my code works! Now onward to functioning tabs…this is progress! Fingers crossed

 

[MarkK]

Nice job!!!