Using multiple Record sources for one form

[chasmith17]

Hello,
I cannot figure out how to use 2 different tables for one form. I have a tab control form with 3 different pages and I can only get 2 of the pages on the form from one table. The 3rd page needs to use another table but I can only set the record source to one table at a time. Is this possible without the use of programming?

 

[ssatech]

Is there any relationship between these (2) tables?
Will this form be used for data entry with the recordsource from these (2) tables?

 

[chasmith17]

Currently there is no relationship between the tables, although I have tried and still didn't get the results I am after. The form is for a service procedure. It is very long and will not fit on one form therefore I had to use the tab control option to make 3 pages on that one form. I have tried just using 3 different forms and linking them to the tables directly but I don't like doing it that way. To answer your question, I do enter data into this form and all I need to do is go back and look at the individual records later on. Seems so simple but those darn tables won't hold as many fields as I would like. Hope I have explained my problem clearly. Thanks for your help.

 

[ssatech]

I do enter data into this form and all I need to do is go back and look at the individual records later on"

Here's my suggestion - try to link or relate these (2) tables...

 

[chasmith17]

ssatech,
I have linked the tables together using 2 different fields and it still didn't work. Are you saying that if I have one form and I set the record source to table 1 that it will switch over to table 2 when I get to the fields that are stored on table 2?

 

[ssatech]

First of all, how will these (2) tables be related? e.g.

tblCustomers
CustomerID.....Autonumber **Primary Key
etc


tblOrders
OrderID.......Autonumber ***Primary Key
CustomerID....Number

These (2) tables are linked in a 1:Many Relationship (meaning a customer could have more then one orders) Or
is your second table a continuation of table 1? If so, create a 1:1 relationship. Then its just a matter of creating query as the recordsource for your form.

This is just an example...

 

[chasmith17]

ssatech,
The second table is a continuation of table 1. There will be none of the same data. How do you create a 1:1 relationship in this situation, what fields do I tie together if none are the same? Also if I try to make a query it gives me an error message saying that there are too many fields. I am probably doing something wrong when I build my query, Any suggestions. Thanks alot for the help ssatech.

 

[ItIsHardToProgram]

Chasmith, creating the 1:1 relationship does it by itself when you have 2 tables with numbers/chars what ever that are index'd and when they can't be doubled (by default in access) basicaly, setting 2 primary keys togeter creates a 1:1 relationship, you only need to set integrety in your relationship (when you created ur relationship, check the boxes bellow.

 

[chasmith17]

ssatech,
Your right about the 1:1 relationship but still having trouble getting the query in the recordsource to work. It keeps saying too many fields. In your previous post you say to "check the boxes bellow" what do you mean?

 

[ssatech]

Just out of curiosity how many fields do you have in table 1 and in table 2?

 

[chasmith17]

They are both maxed out, I counted 158 for each table. Have I gone as far as I can go without the use of code?

 

[ssatech]

Yikes!! Thats whole lotta field's in a table.
You could split your data into several tables and link them in a 1:1 relationship.

Table1
ServiceID...Autonumber **Primary Key
Field1
Field2

Table2
ServiceID...Autonumber **Primary Key
Field3
Field4

Table3
ServiceID...Autonumber **Primary Key
Field5
Field6

Right-click your mouse in the database container, Click Relationship, Show Tables, Add your tables, Put your mouse on ServiceID on Table1 and drag over to ServiceID of Table2, click Create, click box "Enforce Referential Intgrity". Do the same with Table3.

Then when you create your form using tab control form with 3 different pages the recordsource of this main form will be Table1. On the second tab use the wizard to create a subform using Existing tables/queries - choose Table2. The wizard will do the rest. Do the same for third tab page.

 

[PHV]

those darn tables won't hold as many fields as I would like
They are both maxed out, I counted 158 for each table
I strongly suggest you read the following:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chasmith17]

PHV,
Thanks for the reply. I realize that Microsoft access is a relational database but my project is not relational. There are probably better programs out there for what I am trying to do but I have Microsoft Access on my PC so that's what I'm going to use. Right now my service record database is working on three different forms just fine, I just wanted to combine them to just one form using tab control.