Prev: Open Excel file with command button?
Next: Multi Search
From: JohnM on 10 May 2010 04:30
Could any one advise me please

I have two tables that contain some fields that are the same.

I have also two forms to enter data into the seperate tables

Is it possible to enter data into a table using the relevant form that shall
also update the same fields in the other table and vice versa

Thanks

--
JohnM
From: XPS35 on 10 May 2010 04:55
=?Utf-8?B?Sm9obk0=?= wrote:

>
> Could any one advise me please
>
> I have two tables that contain some fields that are the same.
>
> I have also two forms to enter data into the seperate tables
>
> Is it possible to enter data into a table using the relevant form that shall
> also update the same fields in the other table and vice versa
>
> Thanks
>
> --
> JohnM

I think that will be possible.

But........
I think it would be better to avoid situations where you store the same
data in two tables.
So consider to re-design your database.

--
Groeten,

Peter
http://access.xps350.com

From: John Spencer on 10 May 2010 08:38
Possible? Yes. Good idea, almost certainly NO.

If for some reason you really need to do this
AND you have some way to associate a record in table 1 with a corresponding
record in table 2
AND you do the data entry through the forms
you can use a VBA routine in the after update event of the form's to update or
create the necessary data in the other table. THIS IS NOT A GOOD IDEA in
almost every case.

You would need to write some VBA routines to do this.

Depending on your situation a much better approach would be to create another
table that holds the common information and link that to your two tables. OR
keep the data in one of your current tables and link the other table to the
first table. That way you would be updating only one table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

JohnM wrote:
> Could any one advise me please
>
> I have two tables that contain some fields that are the same.
>
> I have also two forms to enter data into the seperate tables
>
> Is it possible to enter data into a table using the relevant form that shall
> also update the same fields in the other table and vice versa
>
> Thanks
>
From: JohnM on 10 May 2010 11:21
Thanks for that - I'll read up on how to link then I should be ok


--
JohnM


"John Spencer" wrote:

> Possible? Yes. Good idea, almost certainly NO.
>
> If for some reason you really need to do this
> AND you have some way to associate a record in table 1 with a corresponding
> record in table 2
> AND you do the data entry through the forms
> you can use a VBA routine in the after update event of the form's to update or
> create the necessary data in the other table. THIS IS NOT A GOOD IDEA in
> almost every case.
>
> You would need to write some VBA routines to do this.
>
> Depending on your situation a much better approach would be to create another
> table that holds the common information and link that to your two tables. OR
> keep the data in one of your current tables and link the other table to the
> first table. That way you would be updating only one table.
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> JohnM wrote:
> > Could any one advise me please
> >
> > I have two tables that contain some fields that are the same.
> >
> > I have also two forms to enter data into the seperate tables
> >
> > Is it possible to enter data into a table using the relevant form that shall
> > also update the same fields in the other table and vice versa
> >
> > Thanks
> >
> .
>
From: John W. Vinson on 10 May 2010 11:47
On Mon, 10 May 2010 01:30:04 -0700, JohnM <JohnM(a)discussions.microsoft.com>
wrote:

>Could any one advise me please
>
>I have two tables that contain some fields that are the same.
>
>I have also two forms to enter data into the seperate tables
>
>Is it possible to enter data into a table using the relevant form that shall
>also update the same fields in the other table and vice versa

Why?

That violates the basic fundamental principles of how relational databases
work!

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". You enter the data
pertaining to one table into that table; other data into a second table; and
then use Forms, Queries, and the other tools in Access to display and manage
that data in its two tables.

Could you explain what these two tables are, what information they store, and
why you feel that you need to store the same information redundantly?
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Open Excel file with command button?
Next: Multi Search