By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,256 Members | 1,663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,256 IT Pros & Developers. It's quick & easy.

How to Swap values between two different records?

P: 7

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.
Jul 28 '10 #1
Share this Question
Share on Google+
3 Replies

P: 207
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.
Jul 28 '10 #2

Expert 5K+
P: 8,698
Post the code that you have so far, and explain in greater detail what you are trying to accomplish.
Jul 28 '10 #3

P: 7
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 accmdImportAttachExcel 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub impCP_Click()
  2.     'Scope: To import data from an Excel file. In cases of importing a Excel file into a existing record, to replace
  3.     '       the contents of the current record with the newly imported contents.
  4.     Dim rs As DAO.Recordset         
  5.     Dim frm As Form
  6.     Dim ctl As Control              
  7.     Dim strControlSource As String  
  8.     Dim strControl As String        
  9.     Dim delsql As String            
  10.     Dim strbk As Variant            
  11.     Dim aryControls As Variant      
  12.     Dim lngKt As Long               
  14.     Set frm = Me.Form
  16.     If frm.NewRecord Then
  17.         'Purpose: Allow importing a Excel file if the current record is a "New" Record
  18.         DoCmd.RunCommand acCmdImportAttachExcel
  19.         DoCmd.Requery
  20.         'DoCmd.GoToRecord , , acLast
  22.         Else
  23.             'Purpose:Allow importing a Excel file if the current record already has a ID no.
  24.             'Imports the Excel File
  25.             Set rs = frm.RecordsetClone
  26.             strbk = Me.Bookmark
  28.             DoCmd.RunCommand acCmdImportAttachExcel
  29.             DoCmd.Requery
  30.             'Set rs = frm.RecordsetClone
  32.             'Assigns the current Record's Bookmark value to the String strbk
  33.             With rs
  34.                 .FindFirst "CoverID = " & Me.CoverID
  35.                 If .NoMatch Then
  36.                     MsgBox "did not work" 'something????
  37.                 Else
  38.                     MsgBox "coverid = " & Me.CoverID
  39.                     frm.Bookmark = rs.Bookmark
  40.                     rs.Bookmark = strbk
  41.                 End If
  42.             End With
  44.             'Moves to last record. Whcih should be the newly imported Excel file
  45.             rs.MoveLast
  47.             'Loops through the controls on the form
  48.             For Each ctl In frm.Controls
  49.                 strControl = ctl.Name
  51.                 'Ignores controls that don't have a ControlSource
  52.                 If HasProperty(ctl, "ControlSource") Then
  53.                     strControlSource = ctl.ControlSource
  54.                     With rs(strControlSource)
  55.                         'Ignores Autonumber fields
  56.                         If ((.Attributes And dbAutoIncrField) = 0&) Then
  57.                             If ctl.Value = .Value Then
  58.                                 ' do nothing
  60.                             Else
  61.                                 ctl.Value = .Value
  62.                                 lngKt = lngKt + 1&
  63.                             End If
  64.                         End If
  65.                     End With
  66.                 End If
  67.             Next
  69.         End If
  71. Set rs = Nothing
  72. Set frm = Nothing
  74. 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.
Jul 28 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.