MS Access: Auto update fields in same table

Sorry but I'm not very experienced when it comes to things like this.

I have a table called "Measure Table" with the fields "ID", "Proxy" and "ProxyID".

I created a form based on this table. ID is a label pre-populated from the table开发者_如何学Python. Proxy is a drop down menu with the options "For" or "From". ProxyID contains a drop down with the same numbers as ID.

I would like a user to go to a specific record in the form (say for ID:I800), select "For" from the Proxy drop down and then select ProxyID (lets say L800). For the record for L800, I want it to automatically change the proxy to "From" and the ProxyID to I800.

Is this possible in Access? Thanks so much for any help provided

Here is a visual of what i wnat to happen:

I want the table to look like this before the update(when the user selects "For" and "L800"):

Record# ID Proxy ProxyID

1 I800 For L800

2 L800

Then the table is automaticaly updated to:

Record# ID Proxy ProxyID

1 I800 For L800

2 L800 From I800

Okay, here is the gist of what you need to do to solve your immediate problem (updating the corresponding row in the other table.

Simply add an event handler to the AfterUpdate event of the form to perform the update to the other row. The code should look very similar to this...

Private Sub Form_AfterUpdate()
  Dim RelatedID As String
  Dim Proxy As String

  If (UCase(Me.Form!Proxy) = "FOR") Then
    RelatedID = Me.Form!ProxyID
    CurrentID = Me.Form!ID

    DoCmd.RunSQL ("UPDATE [Measure Table] SET ProxyID='" & CurrentID & "',  Proxy='From' WHERE ID='" & RelatedID & "'")

  End If
End Sub

Caveats:

As I mentioned in the comments, this data structure is a very bad idea and will create a lot of extra work for you to maintain the data integrity according to the implicit rules you are specifying as a matter of course with this design. I realize you have an existing DB to deal with, but frankly it would probably be less work to fix the DB design than maintain this one in the long run.

Some additional considerations you didn't ask about, but are going to need to deal with:

1. What happens if someone updates either of the entries in a pair directly in the table instead of using your form? There really isn't a good way to apply the above logic to run when except in the context of using the form.
2. What happens in this code if the related row doesn't exist for some reason?
3. What happens if the related row "The FROM" row is updated in the form?
4. What happens if either row is deleted from the table?