How to swap fields of two records.

[Fuzzy Logic]

How do I get access to do the following:

I have a table with multiple records and fields. I wish to swap the value of
a particular field of two records. This field should have a unique value so
I don't need to worry about two records with the same value. For example:

record1: 0403,Jim
record2: 0408,George
record3: 0411,Jeff

So for example I would like to go from the above to:

record1: 0411,Jim
record2: 0408,George
record3: 0403,Jeff

I obviously need to prompt for the values of the two records I want to
change, find them, and make the changes but I'm at a loss as to how to do
this. Any help would be greatly appreciated. TIA

 

[Rick B]

I'm confused. What are the numbers you list? What is 0403? Why would they
be entered with the wrong record? Seems like you would just go in and fix
them, not build some ellaborate scheme to swap values from one record to
another.

 

[John Nurick]

Hi Fuzz,

You say the field whose values you want to swap should have unique
values. If by that you mean that it is the primary key of the table, the
simplest thing is to leave that field alone and swap the values of all
the others.

If the field has a unique index but is not part of the primary key, the
idea is something like this:

Store the two values in variables.
Set the field in one record to NULL.
Update the field in the other record with the stored value
from the first.
Update the field in the first record with the stored value
from the second.

One way of doing this in practice would be to open two recordsets, one
containing each record roughly along these lines

Dim rsOne As DAO.Recordset
Dim rsTwo As DAO.Recordset
Dim lngOne As Long
Dim lngTwo As Long

'Open two recordsets. In real life you'd have these queries get
'the primary key values of the relevant fields from the form where
'the user has selected them.
Set rsOne = CurrentDb.OpenRecordset(_
"SELECT TheField FROM TheTable WHERE PrimaryKey = 'Jim';")
Set rsTwo = ...

'Store values
lngOne = rsOne.Fields(0).Value
lngTwo = ...

'Swap the values
rsOne.Fields(0).Value = Null
rsOne.Update
rsTwo.Fields(0).Value = lngOne
rsTwo.Update
rsOne.Fields(0).Value = lngTwo
rsOne.Update

rsOne.Close
rsTwo.Close