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
SteveL13Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
SteveL13Author Commented:
Meant to mention... the value has to update the same key record and only that record.
0
 
PatHartmanConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.