Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3263
  • Last Modified:

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

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
SteveL13
Asked:
SteveL13
  • 2
2 Solutions
 
SteveL13Author Commented:
Meant to mention... the value has to update the same key record and only that record.
0
 
Gustav BrockCIOCommented:
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
 
PatHartmanCommented:
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
 
SteveL13Author Commented:
I am going to accept multiple solutions.  The first one worked and the second one is great advice.

Thanks to both of you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now