Creating a Multi Tab Form

[PeterB]

Thanks mike60smart,
I see what you've done there & I appreciate your input.
Using your database, how do I get the Market Form update the same Customer & Company?
Using your DB as is, when I select the 'Page 12' Tab (frm_Market) & try to add info, I get #Name? populated in various fields.
I've tried using the =[frmCustomers].[Form]![CustomerID] as the source for a new TextBox on the Frm_Market form but I'm still getting errors.
I need the Customer ID & Company ID to sync across all pages.
Thanks again Peter

[PeterB]

Thanks accesstos,
I'd rather keep this all in one post, I think the best starting point is the relationships between the tables.
The database will be used to control meetings with Customers and there are several companies that a Customer may use.
It also will calculate a billing rate that is different for each Company.
I've attached the ERD. Thanks

[ssanfu]

Thanks for help June7, but each of your posts just criticises stuff I don't know about, This is why I am on a Forum asking questions. I have given this a really good try & I'm looking for help.
If you can't point out the problems & have a fix in mind to step me through, then let me know, otherwise, I'll persist on the internet, or give up ...

I don't think June7 was criticizing. Those were the same questions I would have asked.
I don't know how much experience you have is dB design, but it seems (to me) you do not have much experience (yet).

Since you asked to have problems pointed out, I'm going to. But please do not get offended - my comments are meant to help you.

Do you know anything about "The Normal Forms" with regards to database table design?
What about Normalization?
Here are some links for you to read:

The Normal Forms
=========================
The Normal Forms: Introduction
The Normal Forms: First Normal Form (1NF)
The Normal Forms: Second Normal Form (2NF)
The Normal Forms: Third Normal Form (3NF)
The Normal Forms: In a Nutshell


What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization?
What Is Normalization, Part II: Break it up.
What Is Normalization: Part III: Putting It Back Together
What is Normalization: Part IV: More Relationships
What Is Normalization: Part V: Many-to-Many Relationships


Here is your original design


Here is your second design

Better, but still has problems.

My changes to your design



Setting up a tabbed control is easy, but what you envision happening is complicated.
I like have the "main data" (customers) in the main form.
Linking the customer PK to the Billing sub form is easy. Linking the Billing sub form to the Payment sub form and the Marketing dub form is a lot harder.


The first step is to get the "CustomerID_PK" liked to the "CustomerID_FK in the Billing sub form. Easy....
Now you must get the value from the BillingID_PK and connect it to the BillingID_FK on the Payment sub form.
Once that is worked out, it is easy to use the same method to link to the Marketing sub form.

You know about the linking fields Master Field and the Child Field to keep the data displaying the proper records?

If you see any controls that have a yellow background, that is what I use to denote hidden fields. (PK and FK fields should not be seen on forms).

So tear into the db "Tabbed_db_2". I hope this will help you move forward on your project. Any questions, be sure and ask..

[PeterB]

Hi ssanfu,
Thanks SOOOO much for this, I've found that connecting a 3rd & 4th subform seems to be an issue with most tutorials.
I am familiar with normal forms but I added fields in Payments & Marketing in an effort to help me join the subforms to the other forms.
One question though, I can't see the Customer Name, DoB, & Age in the Form Header of frm_main, just the headings?
I'll study what you have done & come back to if I may.
Thanks again. Peter

[ssanfu]

One question though, I can't see the Customer Name, DoB, & Age in the Form Header of frm_main, just the headings?

I prefer overlapping windows. And I have dual 27" monitors. Maybe you have a smaller monitor. You might try making the main form longer.

I've found that connecting a 3rd & 4th subform seems to be an issue with most tutorials.
I am familiar with normal forms but I added fields in Payments & Marketing in an effort to help me join the subforms to the other forms.

It all depends on the tables and relationships.
Customers table is linked (1-to-many) with Billings. This is easy to set up as Main form and Sub form. But Billing is (1-to-many) with table Payments AND (1-to-many) with table Marketing.
So you could have frmPayments as a sub sub form on form Billings and form Marketing as a sub sub form also on form Billing. But things get really crowded on the form.

I created an ALTernate set of forms as a demo. (attached below)
frm_Main is a main form with 3 sub forms

frm_main_ALT is a main form with a sub form. That sub form has 2 sub sub forms. Compare the linking fields between the two main forms.

[PeterB]

Thanks Steve,
This is great although I'm getting a VBA error when I double click on the Last Name field to add a new Customer?
Just tried it again & it's all good! Disregard this post please.
Thanks

[ssanfu]

OK...

Remember this is only a demo and the way I *might* handle this dB. I don't know the requirements.

And I am still not sure about some of the fields. Why is there a salary field in table Payments?

Enjoy....

[PeterB]

Thanks again, some fields still need to be rearranged, I was mostly concerned with it working correctly first up.
Peter

[ssanfu]

Sounds good. Post back or PM me if you have more questions about what I did.


Good luck with your project.

[PeterB]

Hi Steve,
I'm just doing some testing and I noticed that If I add a new record for an exisitng Customer on the Billing, Market & Payments tab, they don't seem to be linked to the same Company & Customer? I'm getting the Table pop up asking me which table to select for Parent/Chilid relationships?
Are you able to add some records to Version 3 & let me know what I should choose?
Thanks Peter

[PeterB]

Hi Again Steve,
I'm going to add a new post called "Rearranging Tabbed Forms" base on this last DB.
It would be great if you had an opinion.
Peter