A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Updating Data in Multiple Tables



 
 
Thread Tools Display Modes
  #1  
Old May 10th, 2010, 09:30 AM posted to microsoft.public.access.gettingstarted
JohnM
external usenet poster
 
Posts: 75
Default Updating Data in Multiple Tables

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
  #2  
Old May 10th, 2010, 09:55 AM posted to microsoft.public.access.gettingstarted
xps35
external usenet poster
 
Posts: 22
Default Updating Data in Multiple Tables

=?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

  #3  
Old May 10th, 2010, 01:38 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Updating Data in Multiple Tables

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

  #4  
Old May 10th, 2010, 04:21 PM posted to microsoft.public.access.gettingstarted
JohnM
external usenet poster
 
Posts: 75
Default Updating Data in Multiple Tables

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

.

  #5  
Old May 10th, 2010, 04:47 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Updating Data in Multiple Tables

On Mon, 10 May 2010 01:30:04 -0700, 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


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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.