Solved

How update field value in a table using a form boud to another table

Posted on 2014-02-15
4
2,564 Views
Last Modified: 2014-02-15
On a form bound to table 1 I have a number field.  When the value of the field on the form is changed I want the same field in table 2 to be changed to the same value.  They both have the same field name.

How can I do this in VBA code?

--Steve
0
Comment
Question by:SteveL13
  • 2
4 Comments
 

Author Comment

by:SteveL13
ID: 39861366
Meant to mention... the value has to update the same key record and only that record.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 39861428
Use the AfterUpdate event:


Set rs = db.OpenRecordset("Select YourFieldName From OtherTable Where ID = " & Me!ID & "")
rs.Edit
    rs!Fields(0).Value = Me!txtNumberField.Value
rs.Update
rs.Close

/gustav
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39861433
Sounds like you need to rethink your table design.  Having two tables to hold the same information is poor practice.  When you want to relate tables, store the primary key of the table you want to reference as a foreign key.  So for example, when you look at an order, you want to see all the customer information but if you save it with the order, you will have to change it in every record in the order table if it changes in the client table.  The way relational databases solve that problem is by storing a pointer to the data rather than the data itself.  Then you use a query to join the two tables so you can see all the data at once.
0
 

Author Comment

by:SteveL13
ID: 39861441
I am going to accept multiple solutions.  The first one worked and the second one is great advice.

Thanks to both of you.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Restoring a form to its default settings 3 27
Macro to import XML in Access 2013 2 37
Any Way to Print an Import Spec? 3 29
Switch 5 16
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question