Form with mutliple record source (s0

[Lou_Reed]

I have a form that needs to have more that one record source. Some of the information on the form can only be found in an
additional table - a second table. Using just one table will not do it. So can a form have more than one table as its record source?

Any help appreciated. Thanks in advance.



Respectfully,


Lou_Reed

[JoeM]

Create a query, linking the different tables you need, selecting the appropriate fields you need from each table.
Then use the query as the Control Source of your form (note that queries can be sure as the Control Source for things like Forms and Reports, just as easily as Table can). Most Forms/Reports I have are based on queries, as you typically want to do most of the calculations and record selection in the query.

[CJ_London]

So can a form have more than one table as its record source?

yes it can, but it might not be editable or able to insert new records. It is better to keep one table, one form and have associated tables on subform(s). e.g. mainform > invoice header, subform >invoice line details.

The other thing to consider is whether you need to bring through the other table, just use a combobox e.g. mainform > invoice header, customer name> combobox bound to customerid

[JoeM]

I am not instructing you to have more than one Table as its Control Source (each object, i.e. Form or SubForm, can have only one Control Source).
I am saying to make a Query (out of your multiple tables), and use that as the Control Source.

As Ajax pointed out, depending on the nature of your Query, some queries are editable, and some aren't (i.e. Aggregate Queries or many-to-many Queries are not editable). In those cases, you might need to use a Subform, like they recommended.

[John_G]

Just to add to what the others have said -

As they said, in order for one form to use data from more than one table, you have to able to join the tabes with a relationship in a query, and base the form on that. If the tables cannot be related, then the answer to your original question is No.

If you have several tables with the exact same structure (field names etc), then you can use VBA to switch the form's record source from table one to another.

[Lou_Reed]

Create a query, linking the different tables you need, selecting the appropriate fields you need from each table.
Then use the query as the Control Source of your form (note that queries can be sure as the Control Source for things like Forms and Reports, just as easily as Table can). Most Forms/Reports I have are based on queries, as you typically want to do most of the calculations and record selection in the query.

Can you give me an example of doing this?

Is there an example at a website. M

y books on Microsoft Access 2010 had to go back to the library so I do not have them now.

I just need to see an example before I can be confident doing one myself.

Any help appreciated.

Respectfully,


Lou_Reed

[June7]

What exactly do you not understand about building a query?

Use query builder and save as an object. Then in form RecordSource reference that query instead of table.

Or

In the form RecordSource property click the ellipsis (...) to open query builder. This will generate an SQL statement that will be stored directly in the property. No object saved.