can a form have two record sources

[Guest]

Can a form have record sources from two (or more) different tables?
Text Box 1 - record source is Table 1
Text Box 2 - record source is Table 2

 

[Wayne Morgan]

Yes, a form can have two record source, but not at the same time. For
example, if you have two similar record sources, you can change the record
source on the form from one to the other, saving you the trouble of creating
and maintaining two identical forms.

In the scenario you mention, the form doesn't have two record sources,
instead one of the controls on the form will pull data form a different
record source. This can be done by making the control a calculated control
and using a DLookup() statement or one of the other Aggregate Functions.
However, this will not be a bound control. The control will display data,
but you can't edit the data.

Another way to get "two record sources" is to use a query as the record
source for the form. This is actually only one record source, but you can
have more than one table in the query, linking the tables on a common field.
There are restrictions on which fields you'll be able to edit, but you can
include fields from both tables in the queries output.

 

[Guest]

Thanks, Wayne. I made a query and I was able to have various "table sources"
on my form. I have another question though, my goal is really to update
various tables on one form. Is that possible? I'm trying to make my
database more user-friendly for other users to be able to "update" the
records through the form instead of going to the datasheet view of the tables.

 

[Guest]

I forgot to mention that I have cross-reference tables (that is a Lender can
fall into one or more Loan Type category and one or more Property Type
category). Will this be too much to handle for a new user?

 

[Wayne Morgan]

You can do more than one table on a form, but be careful. It is real easy to
make the query "not updateable" if you get it too complicated. If the query
isn't updateable (editable) then the form based on that query won't be
either. Also, the data may no longer be presented in a logical fashion,
making it confusing for the user. It is actually simpler to tell them to go
to this form for this and that form for that. You may also want to look at
subforms, where a form within the main form displays related data for the
item on the main form.

For the Lender falling into more than one category, if the table structure
is correct, this isn't a problem. With what you've described here, you'll
need a table for Lenders, LoanType, and PropertyType. You will also need to
decide what type of "join" you need between the tables. I suspect that in
this case you'll need a many-to-many join type to each of the category
tables. This would mean that more than one lender can have a certain Loan
Type and each lender can have more than one Loan Type. To set this up
requires and extra table, called a linking table, for each link. This
linking table needs a minimum of 2 fields in it to work properly. The two
fields, using the Lender and LoanType tables, would be the LenderID and the
LoanTypeID. You would make both of these fields the Primary Key for this
linking table.

The easiest way to then display this many-to-many relationship is probably
with a form/subform setup. The main form could be set up to display the
lenders and the subform would display the loan types that the displayed
lender supports. You could add a second subform for the property types.

This may give you a good example,
http://msdn.microsoft.com/library/d.../OfficeAccessBuildingApplicationsCh4_Book.asp.
There is a many-to-many relationship between the tblMembers and
tblCommittees tables using tblMemberCommittee as the linking table.