How to Swap values between two different records?

[Aarmando]

Hey,

I'am trying to create a code that will exchange the values of the current record (excluding the ID number) with Record B. So far I have been able to exchange values between the first and last record in the RecordSet.

The only other idea that I have is that I temporarily create a second table and then copy the values to the current record, then delete the table. Its extra code that I would want to do only if it my last resort.

[hype261]

Hey,

I'am trying to create a code that will exchange the values of the current record (excluding the ID number) with Record B. So far I have been able to exchange values between the first and last record in the RecordSet.

The only other idea that I have is that I temporarily create a second table and then copy the values to the current record, then delete the table. Its extra code that I would want to do only if it my last resort.

You are going to have to use some sort of intermediary to take care of this. If your table isn't very large you could store all the fields in variables in vba and then swap them over.

With this method there is always some risk down the line. If you add another field to your table you might forget to update the exchange values code.

[ADezii]

Hey,

I'am trying to create a code that will exchange the values of the current record (excluding the ID number) with Record B. So far I have been able to exchange values between the first and last record in the RecordSet.

The only other idea that I have is that I temporarily create a second table and then copy the values to the current record, then delete the table. Its extra code that I would want to do only if it my last resort.

Post the code that you have so far, and explain in greater detail what you are trying to accomplish.

[Aarmando]

The purpose of the code is to Import an Excel file into the database under two different cases. I used a couple lines of code from the CarryOver() procedure thats out there, and been trying to re-arrange it to fit my needs. For now, I am using accmdImportAtta chExcel to prompt the user into importing the Excel file. Here are the two cases

Case 1: Importing a Excel file as "new" record and appending it to an existing table.

Case 2: Importing a Excel file as "existing" record and appending it to an existing table. In which case, the newly imported data will replace the field values in the current record with the newly import data. Finally delete the newly imported data.

Private Sub impCP_Click()
    'Scope: To import data from an Excel file. In cases of importing a Excel file into a existing record, to replace
    '       the contents of the current record with the newly imported contents.
    Dim rs As DAO.Recordset         
    Dim frm As Form
    Dim ctl As Control              
    Dim strControlSource As String  
    Dim strControl As String        
    Dim delsql As String            
    Dim strbk As Variant            
    Dim aryControls As Variant      
    Dim lngKt As Long               

    Set frm = Me.Form
    
    If frm.NewRecord Then
        'Purpose: Allow importing a Excel file if the current record is a "New" Record
        DoCmd.RunCommand acCmdImportAttachExcel
        DoCmd.Requery
        'DoCmd.GoToRecord , , acLast
        
        Else
            'Purpose:Allow importing a Excel file if the current record already has a ID no.
            'Imports the Excel File
            Set rs = frm.RecordsetClone
            strbk = Me.Bookmark
            
            DoCmd.RunCommand acCmdImportAttachExcel
            DoCmd.Requery
            'Set rs = frm.RecordsetClone
            
            'Assigns the current Record's Bookmark value to the String strbk
            With rs
                .FindFirst "CoverID = " & Me.CoverID
                If .NoMatch Then
                    MsgBox "did not work" 'something????
                Else
                    MsgBox "coverid = " & Me.CoverID
                    frm.Bookmark = rs.Bookmark
                    rs.Bookmark = strbk
                End If
            End With
            
            'Moves to last record. Whcih should be the newly imported Excel file
            rs.MoveLast
            
            'Loops through the controls on the form
            For Each ctl In frm.Controls
                strControl = ctl.Name
                
                'Ignores controls that don't have a ControlSource
                If HasProperty(ctl, "ControlSource") Then
                    strControlSource = ctl.ControlSource
                    With rs(strControlSource)
                        'Ignores Autonumber fields
                        If ((.Attributes And dbAutoIncrField) = 0&) Then
                            If ctl.Value = .Value Then
                                ' do nothing
                                
                            Else
                                ctl.Value = .Value
                                lngKt = lngKt + 1&
                            End If
                        End If
                    End With
                End If
            Next
            
        End If
    
Set rs = Nothing
Set frm = Nothing

End Sub

So the code works in a sense: it does replace the first record with the last record.
But more importantly it has to recognize if whether or not the user decided to import a Excel file through the Import Wizard.