Question on Forms

[mburch2000]

I get that you can have a Form with multiple Pages (tabs). I also understand that you can have Page2 with a Subform pointing to a different table. And I see that on Page2, you can have 2 Subforms.

So, can you have more than 2 Subforms on Page2?

And can you have a Page3 with another Subform pointing to a different table?

Thanks,
Mike

[twinnyfo]

Mike,

The options are pretty limitless..... I've had some forms with five tabs, and four subforms on a tab.... All pointing to different tables/queries. What do you have in mind?

[Seth Schrock]

From what I have found, the limit is 7 levels of subforms, 754 controls/sections over the life of the form. (Limit for subforms?) The tab control doesn't really have any effect on the subform other than visually. I have done a database that had 5 subforms, so your three should be okay.

Update: I just found an article that gives better limitations: Subforms

[mburch2000]

Hey twinnyfo,

Page1: Record Source = tblIntakeMaster

Link Field: IntakeMasterID (PK for tblIntakeMaster )

Page2: Record Source (tblClient) = sfrmClient

Link Field: ClientID (PK for tblClient)

Page3: Record Source (tblAccident) = sfrmAccident

Link Field: AccidentID (PK for tblAccident)


Page3: Record Source (tblFollowUp) = sfrmFollowUp

Link Field: FollowUpID (PK for tblFollowUp)


Basically I think that is it. It seems like either my Link Field is disappearing or when I put it back, my Subform on that Page disappears. I'm not really sure why?

Thanks,
Mike

[twinnyfo]

Mike,

One of the keys for this type of set-up (and Seth, you may have additoinal advice) is that you need to make sure all your keys stay straight and properly linked. In your example, I don't see any keys that would be linked to any of the other subforms.

For example, if you are the Agent, when you choose your AgentID, all the clients on Page 2 that belong to you would be filtered, if the Clients table had a fk for ClientID (just as an example).

So, in essence, when a record is changed on one page, you will want to ensure the Master-Child relationships are clear, so that the forms will update as a whole....

[mburch2000]

Ok twinnyfo,

I am missing something, but believe this is my issue! So below are my main tables in order of my pages on the form. How do I set the keys to the subforms?

tblIntakeMaster : IntakeMasterID= PK

tblClient: ClientID=PK; IntakeMasterID= FK

tblAccident: AccidentID=PK; ClientID=FK

tblFollowUp: FollowUpID=PK; AccidentID=PK

Thanks,
Mike

[mburch2000]

Ok, still trying to figure this out. Does this mean that my 3rd Table (tblAccident) must have a FK for both ClientID & IntakeMasterID? And my 4th Table (tblFollowUp) must have a FK for AccidentID, ClientID & IntakeMasterID?

Thanks,
Mike

[Rabbit]

What's the source and structure of the main form?

[mburch2000]

Main form = tblIntakeMaster

Page2: Record Source (tblClient) = sfrmClient


Page3 has 2 Subforms:Record Source (tblAccident) = sfrmAccident

& Record Source (tblFollowUp) = sfrmFollowUp

[Rabbit]

What about the second part of my question? What's the structure of that table?

[mburch2000]

@Rabbit,

I'm really sorry, but I don't know what that means. What exactly are you looking for?

[mburch2000]

Oops I think you mean the fields, please see below:

tblIntakeMaster
IntakeMasterID = AutoNumber
ContactDate = Date/Time
EmployeeID = Number (linked to tblEmployee to populate drop down box)
IntakeSource = Number (linked to tblIntakeSource to populate drop down box)
ContactMethodID = Number (linked to tblContactMetho d to populate drop down box)
AttorneyID = Number (linked to tblAttorney to populate drop down box)
DateAdded

I guess I'm new at this, so I hope this is what you need. Thanks

[Rabbit]

How can you even link your subforms? Your intake is missing ClientID, AccidentID, and FollowupID. The parent source needs those fields if you are to link to its direct child. It sounds like your form structure looks like this:

           intake
    _________|________
   |         |        |
client   accident   followup

If that's how you're setting it up, then intake needs client id, accident id, and followup id to link on. But you're missing all three so I don't know how you can even link them.

[mburch2000]

@Rabbit, thank you! I didn't realize I needed the other fields in my main table. I will fix and see what happens. I appreciate your help.

Thanks,
Mike

[Rabbit]

Well, you only need them if that's how you're trying to set up your forms. If however you're trying to set it up in a cascade formation, i.e. a subform of a subform of a subform, then the main intake only needs the one directly below. And the one below the main form only needs the one directly below it.