one mdb form with multiple tables?

[vegathena]

I have inherited an Access front end that is an mdb with a SQL Server
backend.
I need one of the forms to save data to multiple tables. In turn, this
form would also need to load its data from these different tables.
I know that saving/loading data for one form to/from multiple tables
is an easy task with an adp. But is possible to do that with an mdb
form? And how would I go about doing that?

Thanks in advance for your help.

 

[Douglas J. Steele]

The only way a form can interact with more than one table is to use a query
that joins the tables together. Unfortunately, usually doing that results in
a query that's not updatable. See
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx for advise
on how to create an updatable join query.

Another option is to use subforms on the main form, with each subform bound
to a different table.

 

[Albert D. Kallal]

I have inherited an Access front end that is an mdb with a SQL Server
backend.
I need one of the forms to save data to multiple tables. In turn, this
form would also need to load its data from these different tables.
I know that saving/loading data for one form to/from multiple tables
is an easy task with an adp.

It is? I'm not aware of any feature in an ADP that makes this process
easier, or more difficult, or less difficult.

But is possible to do that with an mdb

The amount of effort and ability of MS access to do this task is exactly the
same regardless of using an ADP product or not.

The general approach is to simply use sub forms to model a typical one to
many Relationship.

So, if you have a customer form, and you need to display all of their
invoices (from another table of course), you simply would build a sub form
and place that into the main form. You can pretty much do this for as many
tables as you need and have. Of course, form a program usability point of
view, likely I would have an ability to scroll through all those invoices,
and a button you pressed to launch a invoice form to view the details. Once
again, when viewing the invoice, we would use a sub form to display all of
the invoice details (again, likely another table).

Soo the general approach to dealing with multiple tables, especially related
ones, is to simply use sub forms. The beauty of using some forms also means
that you don't have to write additional code to update, and maintained the
foreign keys in those tables.

You can also use of forms to accomplish other interesting tasks in MS
access, I talk about sub forms in MS access here, it should give you some
ideas.

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

 

[vegathena]

Thank you both for your help! I will then look into using subforms.