How do I use VBA to Swap Data Between a Field in Two Different Records - Access 2003

[cw11]

How do I create code to swap data in a field for two different records in Access 2003? I want to create a button on a form that will allow me to swap data for a field. For example, if I have a field named asset, I want to swap the asset field information for one record with the asset field information from another record at the same time. record1 asset field information goes to record2 asset field information and record2 asset field information goes to record1. Is this possible?

[nico5038]

For a swap you'll always need a temp storage of the field(s) to be swapped.
You can do this by creating a temp table, or by using VBA and store the temp field in a variable.

Temp table approach is to insert the source table record in the emptied temptable, next "move" the target table to the source and finally move the temp table record to the target table.

Getting the idea?

Nic;o)

[NeoPa]

Yes it's possible.

Without more information in the question though it's hard to offer any meaningful advice. It makes more sense to determine a proper question before posting it.

[cw11]

let me see if I can do a better job. I have a database of computers. On occasion, we will assign a pc to another person from our inventory. I want to take a pc that is assigned to user John and assign it to Frank. At the same time, I want to assign Frank's pc to John. That is what I mean by swap. Thanks in advance

[nico5038]

The "Normalized way to do this is to create a relation table connecting a PC to a user.
A swap than implies inserting two records with the new situation.

I would use a relation table with the fields:
User_ID, PC_ID, DateStartUse

Thus you have a historical view when a PC was used by which user...

Nic;o)

[NeoPa]

It seems I need to make myself clearer. Apologies for the confusion.

I'm not confused about what 'swap' means. I'm short of information that describes the circumstances you envision where you are choosing to swap information between records.

Are you, for instance, displaying both records on a form (not too straightforward in itself) and intending to click a Command Button to swap them? Maybe you have some other method for determining which records are to have their data swapped. Without knowing such important information as this we don't really have much of a question to work on or with.

@Nico
The Normalisation point is always worth making, but must it necessarily require such a separate table if the individual user is considered to have the capacity for only one PC? I would imagine an FK value in the [Person] (My guess at the name of the table referred to in the question) record (in the field named [Asset]) would make normalised sense.

@CW11
If the concept of Normalisation is a new one for you then check out Database Normalisation and Table structures.

As I mentioned earlier, it's certainly possible to do. I would suggest a fuller explanation of exactly what you're dealing with would make it possible to provide more helpful advice on the subject.

[nico5038]

@NeoPa,

I like "general" and "foolproof" solutions. Having a relation table will allow the restraint of one PC per user by adding a "Unique" constraint on the user key, but by removing that restraint you can allow multiple.
More over, you get also the possibility of looking into the history of the PC's and users and e.g. counting the number of swap's per department, etc.

Regards,

Nic;o)

[NeoPa]

I understand, and I don't argue with any of your points :-)

[cw11]

Here is the way the database looks. I have a field username. I have another field PC. I have created a form. In the form I have a field username1 and username2. I have a field Serial1 and Serial2. I will fill in the four fields with the usernames and Serial numbers from combo boxes for those fields. I will choose manually the names and PC that I want to swap based on when I physically move a pc from one office to another. I then will come back to my office and pull up the two user names and PC's and swap only the user names for the two records in question. The combination of Serial number and username makes my argument unique. I then want to create and update query that sap the usernames based on four variables for the swap button. Again the variables created will be for username1, username2, Serial1 and Serial2. This is how I will trap the records that I want to swap.

[NeoPa]

I think I'm getting you now. The terminology of fields for a form is not accurate, but nevertheless unfortunately common, so is not too obscure (Forms have controls that, when bound, reflect the values of fields). I would assume from what you say that you have four unbound ComboBox controls. If they do not already filter depending on selection that would be a good idea to implement anyway (See Example Filtering on a Form).

Supper coming so I hope to come back later and finish off with some suggestions that are actually helpful.

[NeoPa]

For this sort of logic I would actually recommend Recordset processing rather than SQL commands and use a defined transaction to ensure the swap is either done completely or not at all.

The basic logic would be :

1. Open the recordset.
2. Navigate to the first record.
3. Save the value to be swapped in a variable in your code.
4. Navigate to the second record.
5. Save the value to be swapped from this record in a separate variable
6. Begin a transaction.
7. Edit the current record (The second) to contain the first saved value.
8. Save this record.
9. Navigate again to the first record.
10. Edit this record to contain the saved value taken from the second record originally.
11. Save this record.
12. Commit the transaction.
13. Close the recordset.

I'm off now for a week so I hope this is enough for you. If it's not, and no-one else can pad it out in the meantime, I'll see what I can do for you when I get back (but I doubt that will be required).

[cw11]

This looks great. Thanks so much for your help.

[NeoPa]

My pleasure.

Let us know how you get on with this. I'm sure to catch up when I return.

[ADezii]

@cw11: Based on your specifications, the following approach has been tested and will work (Swap User Names only, based on the pairings of 2 Unique Combinations of User and Serial Number):

Dim strUser1 As String
Dim strUser2 As String
Dim strSerialNum1 As String
Dim strSerialNum2 As String
Dim strCriteria1 As String
Dim strCriteria2 As String
 
strUser1 = "ADezii"
strSerialNum1 = "555B7"
strUser2 = "NeoPa"
strSerialNum2 = "KLY76"
 
strCriteria1 = "[User] = '" & strUser1 & "' And [SerialNum] = '" & strSerialNum1 & "'"
strCriteria2 = "[User] = '" & strUser2 & "' And [SerialNum] = '" & strSerialNum2 & "'"
 
'Do PCs even exist for the Unique Combination of User/Serial Number?
If DCount("*", "tblPCs", strCriteria1) > 0 And _
   DCount("*", "tblPCs", strCriteria2) Then
     'Perform the Swapamundo
     CurrentDb.Execute "UPDATE tblPCs SET [User] = '" & strUser2 & "' WHERE " & _
                        strCriteria1, dbFailOnError
     CurrentDb.Execute "UPDATE tblPCs SET [User] = '" & strUser1 & "' WHERE " & _
                        strCriteria2, dbFailOnError
Else
    MsgBox "One or both PCs do not exist for the specified combination of User and " & _
           "Serial Number", vbExclamation, "Swap Aborted"
End If

[NeoPa]

Interesting suggestion ADezii, but doesn't treat the swap as a single transaction. Almost certainly thoroughly adequate of course, but I think you'd find it interesting to pad out my pseudo-code from post #11 into VBA proper using the names of the objects provided by the OP in post #9. If you haven't played with transactions before it's well worth getting into. Just a suggestion of course, and only if it interests you. I'll do it myself when I get back if I find the OP still needs it.

As a side-note - what is going on with you providing a SQL solution instead of a VBA one (:-D)? That's not what I expect from you, but it's good to see nevertheless. It doesn't hurt to see that both approaches can be made to work. See you at the end of next week.

[ADezii]

As a side-note - what is going on with you providing a SQL solution instead of a VBA one (:-D)?

@NeoPa: Don't know what is going on with me - SQL instead of VBA? (LOL).

1. Is encapsulating the Swap in a Transaction really necessary once the following evaluates to True?
   

   'Do PCs even exist for the Unique Combination of User/Serial Number? 
   If DCount("*", "tblPCs", strCriteria1) > 0 And _ 
      DCount("*", "tblPCs", strCriteria2) > 0 Then 

2. Transactions do imply additional overhead, don't they?

[NeoPa]

1. I think so. Yes.
   
   Consider the situation where the power fails after the first record is changed and before the second one is.
   
   
2. Yes. Negligible in this case I would expect, but certainly there.
   
   Actually, probably less of an overhead than using two separate SQL strings instead of a single opening of the table within a VBA recordset (logically - I have no experimental timings to support that but as opening a table is where most of the overhead comes in when accessing it I would expect two of them to take longer than a single one).

[ADezii]

Opening a Recordset, Navigating within that Recordset, then making Edits within the Recordset, all encapsulated in a Transaction was a little tricky and Error prone. I really did not have too much time to investigate, so I took a Hybrid approach which seems to work quite well:

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click
Dim strUser1 As String
Dim strUser2 As String
Dim strSerialNum1 As String
Dim strSerialNum2 As String
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim rst As DAO.Recordset
Dim wrkCurrent As DAO.Workspace
Dim blnInTrans As Boolean       'Are we in a Transaction?
 
strUser1 = "ADezii"
strSerialNum1 = "555B7"
strUser2 = "NeoPa"
strSerialNum2 = "KLY76"
 
strCriteria1 = "[User] = '" & strUser1 & "' And [SerialNum] = '" & strSerialNum1 & "'"
strCriteria2 = "[User] = '" & strUser2 & "' And [SerialNum] = '" & strSerialNum2 & "'"
 
blnInTrans = False              'Not in a Transaction yet
 
'Do PCs even exist for the Unique Combination of User/Serial Number?
If DCount("*", "tblPCs", strCriteria1) > 0 And _
   DCount("*", "tblPCs", strCriteria2) Then
     Set wrkCurrent = DAO.DBEngine.Workspaces(0)
     wrkCurrent.BeginTrans
     blnInTrans = True      'Presently in a Transaction
 
       CurrentDb.Execute "UPDATE tblPCs SET [User] = '" & strUser2 & "' WHERE " & _
                          strCriteria1, dbFailOnError
       'Err.Raise 13        'Rollback Test
       CurrentDb.Execute "UPDATE tblPCs SET [User] = '" & strUser1 & "' WHERE " & _
                          strCriteria2, dbFailOnError
    wrkCurrent.CommitTrans
    blnInTrans = False      'Changes committed without an Error, Transaction is complete
 
    DAO.DBEngine.Workspaces(0).Close
Else
    MsgBox "One or both PCs do not exist for the specified combination of User and " & _
           "Serial Number", vbExclamation, "Swap Aborted"
End If
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  If blnInTrans Then      'Was the Transaction successfully completed, or does it need to be Rolled back?
    If Not wrkCurrent Is Nothing Then wrkCurrent.Rollback
  End If
    If Not wrkCurrent Is Nothing Then
      DAO.DBEngine.Workspaces(0).Close: Set wrkCurrent = Nothing
    End If
    MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
      Resume Exit_cmdTest_Click
End Sub

[NeoPa]

Actually, it seems it is possible to handle this with a single SQL update query. Assuming the code is run from a Command Button - cmdSwap - found on the form then the code could be :

Private Sub cmdSwap_Click()
    Dim strSQL As String
 
    strSQL = "UPDATE [tblUserPC] " & _
             "SET    [UserName] = IIf([UserName] = '%UN1', '%UN2', '%UN1') " & _
             "WHERE  (([UserName] = '%UN1')" & _
             "  AND   ([PC] = '%PC1'))" & _
             "   OR  (([UserName] = '%UN2')" & _
             "  AND   ([PC] = '%PC2'))"
    With Me
        strSQL = Replace(strSQL, "%UN1", .UserName1)
        strSQL = Replace(strSQL, "%UN2", .UserName2)
        strSQL = Replace(strSQL, "%PC1", .Serial1)
        strSQL = Replace(strSQL, "%PC2", .Serial2)
        Call CurrentDb.Execute(strSQL)
    End With
End Sub

PS. As the updates are all carried out in a single process the requirement for transaction handling is obviated.

[NeoPa]

Apologies to anyone trying to make sense of my previous post (#19) prior to my posting this one, for the changes I had to keep trying out in order for what I posted to show as intended. I'd originally tried to use %U1 & % U2 (sans space) as replacement parameters for the two UserName values in the SQL template, but for reasons that are unclear to me, these were automatically edited out by the content parser and the code left behind made no discernable sense. I got around it eventually by using %UN1 & %UN2, which seems to work fine. I also changed %P1 & %P2 to %PC1 & %PC2 simply for consistency.

I hope this didn't disrupt anybody, and I can confirm all seems to be correct now.