filtered subforms 1

[toryee]

I have a form, it has tabs and each tab has a subform. These subforms are of the same kind. The main form data and the subform data have a one-to-many relationship. Instead of using the default Access record selection in the subform, I need to make each record appear in a subform of a tab.

For example, if the main form contains the family information, then the subform in tab1 contains information for Child 1 of the family; the subform in tab2 contains information for Child 2 of the family, and so on. On the main form, there is a field indicating how many children in the family.

My boss asks for this because the tab format is easier for data entry than the arrows at the bottom of the page.

Now I'm stuck. I have made copies of the same subform and put one in each tab, but I don't know how to filter the records so that child1 appear only in tab1/subform1, child2 appear only in tab2/subform2. What's the best way to make this happen? Is VBA necessary? Something like Dlookup? Any guidance is appreciated.

Thanks very much.
toryee

 

[ZmrAbdulla]

I don't think your boss'demand is workable for every record.
If children are fixed to a number then you can have tabs as per the count or else it is difficult to create tabs at runtime.
See Northwind Customer Orders form that has two sub forms. That would be more easier for your situation.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[Remou]

Hi
What happens if there are 10, 11, n children?

 

[ZmrAbdulla]

Remou, is your question to me?


________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[Remou]

Beg pardon ZmrAbdulla, I posted without refreshing, so the question was to toryee, however, your post makes it irrelevant.

 

[toryee]

Not to worry about tabs. I made them hidden initially and make them visible depending on the 'number of children' text box on the main form. I have fifteen tabs, assuming a family has no more than fifteen children. I know it's bad design, but it works mechanically.

For each child record, there is a field indicating his number in the family. I think this way, each subform can look up this field and filter the other fields based on this information. I don't know how to approach this filtering problem. I think that for each subform, the control source is not only the main form, but also the 'child number in the family' field. Each 'child number in the family' number must correspond with its subform/tab.

There is a table of child records and the subforms are based on this table.

Thanks again. Any help is appreciated.
toryee

 

[ZmrAbdulla]

I know it's bad design, but it works mechanically.

Your should think about re-desgining the DB.
Or if you need to keep as it is.. you should have One Master table (Parent) and 15 sub tables (children) and make the subforms with the subtables... TOO BAD DESIGN...


________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[toryee]

How should I redesign everything such that what I want can be achieved? Can VBA be an alternative? By using VBA so every field is linked dynamically?

toryee

 

[ZmrAbdulla]

A master table with fields
[ul]
[li] Family ID (PK)[/li]
[li] Child Count[/li]
[li] Information 1 [/li]
[li] more..[/li]
[/ul]
A Child table with fields
[ul]
[li] Child Family ID (FK)[/li]
[li] Child ID (PK)[/li]
[li] Child Grade[/li]
[li] Information 1[/li]
[li] more.. [/li]
[/ul]
This must be your table setup.
Child Grade is written as per child's age (First, Second, Third...)
Creating Queries from Child Table for each tab filtering with Grade (First for Tab1, Second fro Tab 2....) will be able to bring each child to each subform. (still it is not good way)
Query fields will be
[ul]
[li] Child Family ID [/li]
[li] Child ID[/li]
[li] Child Grade [/li] (This will be filtered with grade)
[li] Information 1 [/li]
[/ul]
So you need 15 queries..
I am not sure if this will bring any problem.
Come back if you need any more help

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[toryee]

Zameer,
Thanks very much for your input.
I think after creating the fifteen queries, I still need to use a separate subform for each query. Am I correct?
toryee

 

[ZmrAbdulla]

Yes.. You need separate subforms for each tab.
You could create a subform, create copies of that and change the RECORDSOURCE of the form to different queries.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[toryee]

Thanks Zameer. Give me a day or two to work on this, and I'll get back to you.
toryee

 

[toryee]

Zameer,
Thank you for your posts. I have implemented the forms as you suggested, and they work well. Having fifteen queries and subforms is, I think, better than having VBA code to do all these.
Thanks again.
toryee