Multiple Record Sources for a form

[rwest]

I was wondering if it is possible to have multiple record sources for a form without creating queries, and if so how do I go about doing that.
Thanks

[Seth Schrock]

You can do it without creating a query object that would appear in the object list, however it would still be a query supporting the form. You would do this by typing the SQL code into the row source property of the form.

Why don't you want to use a query?

[NeoPa]

You can do that (as in you can change the record source on the fly), but most people who have such an interest are just looking for the concept of filtering (See Example Filtering on a Form) without knowing it.

[sierra7]

Hi rwest,
you must give us more information about what you want to achieve.

What immediately comes to (my) mind is the use of sub-forms. The main form could be base on the table tblSalesOrders while the Customer details could be based on the tblCustomers table; Sales Order details on the tblSalesOrderDe tails table etc. Is that the sort of thing you are thinking of?

To achieve this you start by building separate forms for each table then embed one form within the other. (there is a tool on the tool-bar to add a sub-form/sub-report controls.)

Having said that, you should get into the habit of basing you forms on queries not tables.
S7

[rwest]

Would you have an example of the SQL code? The main form's record source is from a table called customers and I want the sub form's record source to be from the tables orders and types. I have a connection between customers and orders, and between orders and types but not between customers and orders. This is why I want 2 record sources for the subform of orders and types. If there is another way I can do this that would be great too.

[Seth Schrock]

If you don't know SQL code, your best bet is to create a query in design view (it is much easier this way for new people). Just include the Customers, Types, and Orders tables and then select the fields that you want out of each. The query will view the relationship and be able to pull the related records and put them together. You can then base the form on that query. The query does the work of combining muliple tables for the form.

I don't have enough information to even guess at what the SQL code that you would need. The basic form of SQL code is:

SELECT ___
FROM___
WHERE ___ (optional)

The following website will help you learn SQL if you want. SQL tutorial

If you really don't want to have a query object as the source (I can't think why), then take the query that you created in the instructions above and then change to SQL view, copy the code and then paste it into the row source of the form.