473,897 Members | 3,236 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Record Source (Multiple Tables) No Relationships

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

May 8 '06 #1
6 10506
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.

May 10 '06 #2
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.

May 10 '06 #3
<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

May 10 '06 #4
<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

May 10 '06 #5
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

May 10 '06 #6
"Ap******@gmail .com" <Ap******@gmail .com> wrote in
news:11******** **************@ g10g2000cwb.goo glegroups.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
..AllowDeletion s 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
programmaticall y swap the subforms.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 10 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2772
by: Ryan Hubbard | last post by:
I'm inserting a record into MySQL 4.0 using Visual Basic ADO. When using the AddNew and Update method I am unable to retrieve the value of a Auto incrment field (Yes I know I can MoveLast but this icreases the update time by a factor of 10 and when your talking about 200,000 inserts its way to long). Is there a way to insert the record into multiple tables in one statement where you can use the value of the auto incremented field as the...
2
681
by: RAF | last post by:
i'm trying to make report using dataset having two tables in viewer page iam doing rpt.SetDataSource(ds.Tables(0)) rpt.SetDataSource(ds.Tables(1)) CrystalReportViewer1.ReportSource = rpt but it is not working
8
10432
by: Jason L James | last post by:
Hi all, does anyone know if I can create a dataview from multiple datatables. My dataset is constructed from four separate tables and then the relationships are added that link the tables together. To make data entry easier I would like to combine selected columns from the four tables onto a single dataview that could be used as the datasource for a datagrid control. The distinct updates, insert ans delete
3
3552
by: jpr | last post by:
Hello, I know that this is not the rule but need some help. My datbase has three tables: MASTER TEMPLATES FORMS I have a form which is based on a table named MASTER. I have a primary key set to autonumber and a field SSN which I would
2
3874
by: rlish | last post by:
I have a database with multiple tables that are all linked by a "site id" field. For any given record the site id field is identical within all the tables. Is there anyway to create a form with a textbox that will record the site id to multiple tables?
8
14798
by: fonzie | last post by:
Is it possible to have a data entry form where the information is stored in several different tables (5 or 6)? I have an inventory database where Table1 stores all of the data common to all entries, Table2 stores only the extra info relevant to items that are TYPEA, Table3 stores only the extra info relevant to items that are TYPEB, and so on. All relationships are setup (one-to-one) between the main table (Table1) and all the other...
1
2961
by: access345 | last post by:
I am trying to add a record to multiple tables at the same time. My current Code created multiple records in the same table. I am trying two different methods with no results: Public Sub txtSerialNumber_Enter() Dim txtLotNumber As String Dim txtSerialNumber As String 'Dim db As Database 'Set db = OpenDatabase("Modem.mdb")
2
4101
by: SteveMPullen | last post by:
I have an Access DB with 5 tables. 3 of the tables have a primary key with a field of "Project Name". I have created a form to add a new "Project Name" to the 3 tables. When I add the new "Project Name" the 1st table gets updated but the 2nd and 3rd tables do not get the update. 1st table has 1 field: Project Name - primary key 2nd table has 30 fields: Project Name - primary key 3rd table has 24 fields: Project Name - primary key I...
4
3308
by: knix | last post by:
I have this access project consisting of multiple tables that are linked together in a relationship. I would like to migrate the consolidated information through appending in a datasheet form or importing the gathered information . Can anyone please give solutions with my problem? Can I make a datasheet form (consisting fields from multiple tables that are linked together with relationships) in ms access which I could paste append or insert...
0
9990
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9837
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11250
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10850
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9656
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5873
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6074
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4698
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4293
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.