Record Source (Multiple Tables) No Relationships

[ApexData]

I have 2 tables: Table1 and Table2.
Neither one has a primary key because each table will only have
1-record.
My form is a SingleForm unbound with tabs (my desire here).

Using this form, in the Record Source I originally had the statement
TABLE1,
and all worked fine. Now I want to open the second table as well, and
this is where my problem is.

In the Record Source I tried TABLE1,TABLE2, [TABLE1].*,[TABLE2].*,
SELECT [TABLE1].*, [TABLE2].* FROM [TABLE1], [TABLE2];

None of these work! The last example shows the form but with no
controls.
Is there a way to simply open two tables (all fields) using the Record
Source
knowing that each Table will only be 1-record with no relationships???

Thanks Greg

[Jamey Shuemaker]

I honestly don't know why you'd want to do something like that but:

SELECT Table1.* FROM Table1
UNION SELECT Table2.* FROM Table2;

as the RecordSource will work.

Of course, your tables need the same field names or they must be
selected using the AS keyword to make them the same.

[DFS]

Jamey Shuemaker wrote:

I honestly don't know why you'd want to do something like that but:

SELECT Table1.* FROM Table1
UNION SELECT Table2.* FROM Table2;

as the RecordSource will work.

Of course, your tables need the same field names or they must be
selected using the AS keyword to make them the same.

No. All that's required in SELECT * UNION SELECT * is the tables have the
same number of fields - and even that restriction is easily overcome by
specifying the columns and adding something like 'NULL as Field8':

SELECT Field1, Field2, Field3, NULL As Field7, 'Eight' As Field8 FROM Table1
UNION
SELECT Field4, Field5, Field6, Field7, Null As Field8 FROM Table2

Output: Field1, Field2, Field3, Field7, Field8

Every column can be a different data type as well.

[ApexData]

<I honestly don't know why you'd want to do something like that but: >

Thankyou for the reply. I simply need to be
able to reference the 2-Tables mentioned. What method would you
personally choose to make reference to each tables fields knowing that
each table will only have 1-record and they have no relationship to
each other? These single record tables, are being used to store Setup
information for the application.

Greg

[Larry Linson]

<Ap******@gmail.com> wrote

I have 2 tables: Table1 and Table2.
Neither one has a primary key because each table will only have
1-record.
My form is a SingleForm unbound with tabs (my desire here).

Using this form, in the Record Source I originally had the statement
TABLE1,
and all worked fine. Now I want to open the second table as well, and
this is where my problem is.

By definition, an "unbound" Form (tabs or no) does not have a Record Source.
You use code to open a Recordset and move values from the recordset to the
Controls to populate an unbound Form. What you describe is a _bound_ form.

You can accomplish what you want simply by creating a Query, adding each of
your Tables as a data source, not trying to use any Joins, dragging down the
fields you want, and using the Query as your Record Source. That will work
ONLY because you have just one record in each Table. If, perchance,
something happens that one or both Tables have multiple records, you'll get
multiple records in the resulting Recordset.

(Using multiple tables without a Join yields a "cartesian product", that is,
each record of each table is paired with each record of the other table.
When you only have one record in each table that yields only one record. If
you had two records in each table, it would yield four records.)

Another approach would be to add a Key field of the same type to each table,
populate each with the same value, and create a join on that Field.

But, yet another approach seems to have been implied in an earlier
response -- combine the two tables into one single table. That ought to work
just fine, unless you have a total of more than 255 Fields of startup
information.

Larry Linson
Microsoft Access MVP

[ApexData]

Larry

Thank you very much for the insight. I'll mull each consideration and
commit.
I'm leaning toward the KeyFields and the consolidation to a single
table methods.

Greg

[David W. Fenton]

"Ap******@gmail.com" <Ap******@gmail.com> wrote in
news:11**********************@g10g2000cwb.googlegr oups.com:

Thankyou for the reply. I simply need to be
able to reference the 2-Tables mentioned. What method would you
personally choose to make reference to each tables fields knowing
that each table will only have 1-record and they have no
relationship to each other? These single record tables, are being
used to store Setup information for the application.

Make a subform for each table. Put a tab control with two tabs on an
unbound form. Embed each subform in one of the tabs.

You'll probably want to do things like turn off .AllowAdditions and
..AllowDeletions in the subforms themselves.

You could also use a single subform control without the tab and use
some method like an option group (perhaps with toggle buttons) to
programmatically swap the subforms.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/