Tab control form linked to two different tables?

[mongous]

Help! I have a two-tab form and it's currently only linked to one table, but I need it to link to another table. I want one tab for one table, the other for the other. I have tried linking each control individually with the expression builder but I get the #name error...

Thanks.

 

[LittleSmudge]

You will.

There's nothing magic about tabs - they do not let you break the rules of standard form design just because you have the controls on a tab.

ALL a tab does is allow you to design forms with a third dimension and hide one set of controls behind another.



So - now on to find a solution for you :-

Are these two tables linked in a relationship ?
Ie. Is one table at the "One" end and the other at the "Many" end of a one to many relationship ?

OR

Are they completely independent ?


If the former - the you could consider combining the relationship in a SQL sring in the form's RecordSource

Otherwise - design the second form as a discrete entity in it's own right and save it, then place a subForm control on the second tab and reference the second form inside that subForm control.


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[mongous]

Yes the tables are linked - one-to-many. However when I first made the form they weren't, so my problem is accessing the other table now that they ARE linked.

I have thought about the subform ides, but just out of curiousity, how can i do it otherwise?

>>mongous

 

[godfrey62]

If I may jump in here, one solution I would have is to put the second table on its own form and then make that second form a subform on the second tab. I'm doing the very same thing with data I am working with.

The subform has its own query/table as a source and you can set up a Master/Child relationship between the main form and the subform which keeps the two tables in relationship.

 

[mongous]

Yeah, I guess that is the way I will do it. It is perfectly fine, I was just getting frustrated that I couldn't 'link' the other table after the fact, but, oh well.

Thanks for the input!

 

[LittleSmudge]

As per my second option - A subForm, in the subform control you put the 1st table's PrimaryKey field in the LinkParentFields property and the 2nd table's Foreign Key in the LinkChildFields property


If, however you want to do it all on one form, in the form's RecordSource property you can replace the table name that the form is bound to with an SQL string
Eg
"SELECT table1.*, table2.* FROM Table1 INNER JOIN table2 ON Table1.PrimeKey = Table2.FKey"

That will do it for you.



'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[mongous]

that will most certainly help. Thanks!