multi column update code???

[dabc]

I have a table with a from and to column. I need to have it update in the to column for both sides of the connection. For example:

From To
1-1
1-2
1-3
1-4

If the first line of the TO column is updated to 1-4, the fourth line should be updated to 1-1 to match.

From To
1-1 1-4
1-2
1-3
1-4 1-1

[hansup]

I have a table with a from and to column. I need to have it update in the to column for both sides of the connection. For example:

From To
1-1
1-2
1-3
1-4

If the first line of the TO column is updated to 1-4, the fourth line should be updated to 1-1 to match.

From To
1-1 1-4
1-2
1-3
1-4 1-1 I don't understand what you're trying to accomplish. Are you concerned only with the first and last rows?

Or do you also want something to happen if a "To" value is entered in the second row? If so, could "1-1" or "1-4" be valid "To" values in the second row? (Can you ever have the same value more than once in the "To" column?)

Hans

[dabc]

all rows would be involved....you could connect anything from the "from" column to anything in the "to" column....just both sides need to line up....it is for a wiring template....if a is wired to b then b is also wired to a....but this way you don't have to search thru 30000 entries to update the other side of the circuit.

[hansup]

all rows would be involved....you could connect anything from the "from" column to anything in the "to" column....just both sides need to line up....it is for a wiring template....if a is wired to b then b is also wired to a....but this way you don't have to search thru 30000 entries to update the other side of the circuit.
OK. Your situation may be too complex for me, but let me ask you another question in case someone else can help.

If A is wired to B, then B is also wired to A. Can anything else be wired to A besides B? How about B --- anything besides A?

Hans

[CreganTur]

Another question is where is your data coming from? Is it entered by hand, or is being imported from somewhere else?

[dabc]

anything in the (from) column can be connected to anything in the (to) column, but the (to) column does not have to be a value from the (from) column. However, if the value in the (to) column matches a value from the (from) column then one one side is entered the other side should populate as well.

[hansup]

anything in the (from) column can be connected to anything in the (to) column, but the (to) column does not have to be a value from the (from) column. However, if the value in the (to) column matches a value from the (from) column then one one side is entered the other side should populate as well. Please check whether I understand the requirements. Are the following rows valid?

From To
1-1 1-4
1-2
1-3 1-1
1-4 1-1
1-5 1-6
1-6 1-5
1-7
1-6 1-239
1-1 1-240
1-1 1-3

[dabc]

1-1 1-4
1-2
1-3 1-1
1-4 1-1
1-5 1-6
1-6 1-5
1-7
1-6 1-239
1-1 1-240
1-1 1-3

Everything in green is valid...

[hansup]

1-1 1-4
1-2
1-3 1-1
1-4 1-1
1-5 1-6
1-6 1-5
1-7
1-6 1-239
1-1 1-240
1-1 1-3

Everything in green is valid... Good!

The "From" field contains unique values, and Nulls are not allowed. The "To" field may or may not contain a value, but any values present may not be repeated within that column.

However, one point surprised me. Earlier you said "the (to) column does not have to be a value from the (from) column", yet you indicated my sample row "1-6 1-239" is not acceptable. To me, it now appears the "To" value must exist (somewhere) in the "From" column. Please let me know if I'm headed in the wrong direction.

I think you can create a form bound to your table, and use VBA to drive updates based on changes to the "To" field. It might look something similar to this (I used fFrom, fTo, and tblDabc as my field and table names):

Private Sub fTo_AfterUpdate()
Dim strSql As String

strSql = "UPDATE tblDabc SET fTo = '" & Me.fFrom & "' WHERE fFrom = '" & Me.fTo & "';"
Debug.Print strSql
CurrentDb.Execute strSql
End Sub

That was only intended as a rough outline to get started. It doesn't guarantee the new fTo value matches an existing fFrom value. You could attach a check on another event on the fTo control to reject invalid values. A better way might be to create a table, "tblConnectionPoints", and define a relationship which mandates each fTo value exist in tblConnectionPoints. But I won't go into that now in case I'm headed in the wrong direction.

And I'm not sure you need to store a separate row for the same connection in the opposite direction. Once you have stored a "From->To" pair, the converse can be derived on the fly with a query like this:

SELECT a.fTo AS fFrom, a.fFrom AS fTo
FROM tblDabc AS a
WHERE a.fTo In (SELECT fFrom FROM tblDabc);

That's all I got for now. I'm late for lunch.

Hans

[dabc]

I'll give that a shot and see what I come up with, as for the to column not having to be a value from the "from" column....about 99.99% of the time it is...however there are a few exceptions...long story.