Creating a Multi Tab Form

[PeterB]

I don't think I can explain it better than I have?


I have Customers who may deal with one of 6 companies for my services, I then sell products from each company and want to track the billing & marketing info so I can track such things Birthday reminders, Customers I haven't contacted in a while etcetera. The main question though is WHY ALL the Tabbed forms don't all connect to the same Customer when I switch between Tabs?

[June7]

tblCustomers does not have defined primary/foreign key relationship with tblMarketing and tblPayments. You are trying to control linking through intermediate table. That does not work with form/subform relationship.

A main and subform with the same datasource (tblCustomers) is not a usual arrangement. This is what a Split form accomplishes. Emulating a split form is tricky.

[PeterB]

Thanks June7,
You keep advising me what's wrong, but I need to know what's right.

[PeterB]

To explain again, I have 4 tables that I want to place into a Tabbed Control form and the tables are Customers, Billing, Marketing and Payments.
So I want to have 4 Tabs, one for each table and have each tab open for the same Customer & Customer for all Tabs.
Can you or anyone else help me with this?

[June7]

First, I would not have a subform for tblCustomers, I would just put controls directly on Page1 of Tab Control.

Then I would not use queries joining tables as RecordSource for other forms.

I am still trying to grasp data relationships and still confused by tblMarketing and tblPayments.

Why does tblPayments have multiple credit card fields as well as fields for Cash, Paypal, etc?

Why is there not a tblCompanies?

[PeterB]

Thanks June7,
I've made some changes based on your response.
This better but the Company ID is not being picked up when a new record is added?
I've created a tbl_Companies table but I'm unsure if I've joined it correctly?
Would you be able to take a look and suggest an answer?
Thanks Peter

[PeterB]

I think I used a subform for the tbl_Customers so I've changed that in this attached file, records are still not updating the Company and I'm unable to add records for each page?

[June7]

You have the form setup for tblCustomers and tblBillings backwards. tblCustomers should be on main and tblBillings should be dependent subform because tblCustomers primary key is saved as foreign key in tblBillings.

Why is CustomerID in tblCompanies?

If you want both CustomerID and CompanyID to automatically populate fields in dependent table, that would involve a compound key. I try to avoid them.

I am still not understanding purpose of tblPayments and tblMarket.

Third time asking: Why does tblPayments have multiple credit card fields as well as fields for Cash, Paypal, etc?

[PeterB]

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 ...

[June7]

I can't offer a specific fix until I understand your data and business process. That's why I ask you questions.

I thought I did give clear explanation for what is wrong with tblCustomers and tblBilling forms and how to fix.

What do you not know about - what primary and foreign keys are?

I believe I have also given this a really good try but as they say, "can't save everyone". Wish you luck and hope you find the guidance you need.

[PeterB]

Thanks anyway June7, I can't believe that it's so hard to create a tabbed form when it's offered as an option in Access?
Forget my existing design, I just need to know how the Tabbed Pages connect to each other.
I could learn from an existing Tabbed DB that has more than 2 tabbed forms to add to a Tabbed control.

[accesstos]

Hi Peter!

Forget my existing design, I just need to know how the Tabbed Pages connect to each other.

By a common field (cboPersons in my previous sample) into the main form (tab control container).
Take a look in attachment (page "All"). Check the "Link Master Fields"/"Link Child Fields" of subforms of frmMain and sfrmAll.

Beyond that, you have to redesign your database and I suggest to open a new thread about her structure.

[ArviLaanemets]

Post #3 in thread https://www.accessforums.net/showthread.php?t=78698 has my example of continous form in tab control having linked subforms (a continous one in same page of tab control, and single one on 2nd page). Additionaly the continuous subform on 1st page has a linked single subform on 3rd page.

[June7]

Thanks anyway June7, I can't believe that it's so hard to create a tabbed form when it's offered as an option in Access?
Forget my existing design, I just need to know how the Tabbed Pages connect to each other.
I could learn from an existing Tabbed DB that has more than 2 tabbed forms to add to a Tabbed control.

The tab control has nothing to do with difficulty of linking your forms. Even if tab control were removed, issue would be same. Problem is with data structure. tblCustomers and tblBilling is rather straightforward - tblCustomers belongs on main form and tblBilling would be dependent subform. I already gave this instruction in post 20 which you did not follow.

Difficulty is with tblPayments and tblMarket. Since I still do not quite understand their purpose, hard to advise, especially since you have not answered questions I posed.

[mike60smart]

Hi Peter

See the attached

I removed spaces in some field names in all tables. Mainly the Primary & Related Foreign Keys.

You do not need to include all 3 tables in the Record Source of the Forms. You base the Forms on the actual Tables.

In the example attached I have colour coded the linking Unbound Control above the Tabbed Control. This Unbound Control uses the Form Reference Method to obtain the Primary Key from frmCustomers.

This is named txtCustomerID

This named Control is used as the Linked Master Fields
The Linked Child Fields is CustomerID