Swap data between fields in a different table 1

[colinmitton]

I need to know how to 'swap' data between two different fields each in there own table. The fields have the same settings I just need to be able to swap the data from Field1 in table1 to Field3 in table2.

I'm going to use a command button do the switch but I'm getting confused with using recordsets and the correct terminology to overwrite the fields in the relevent records? do I need to set up a temporary Table to acheive this?

Thanks

 

[MajP]

you would likely use an update query to do this. However, please provide more information. This suggests to me that your database is not properly designed. Why do you have two tables with similar data and need to "swap" data? Makes no sense.

 

[colinmitton]

Thanks, My database design does have a floor which I'm addressing in my next release, I did not get the full specifications while I was designing the DB initially this requirement was added after! Hence I'm trying to set up a quick fix for now. I know what I need to do to sort this out, its just getting this fix on-line!

 

[dhookom]

An update query could possibly work but would depend on primary and foreign key fields which you haven't identified.

Duane
Hook'D on Access
MS Access MVP

 

[colinmitton]

Each table has a primary key which acts as a unique reference I'm able to open the recordsets but at current I only open one at a time in different sub-routines. I'm selecting the correct records its just the matter of copying (swapping) the data around.

 

[randy700]

"Swapping data" seems to imply that something is moving from Table1 to Table2 while something else is moving from Table2 to Table1. So, create the new fields in each table, use update queries to move the data to the proper table, and delete queries to remove it from it's original location after the swap.


Randy

 

[colinmitton]

Thanks, I'll give it a go.

 

[dhookom]

Delete queries will delete entire records. Update queries can delete or update values in fields. I don't think I read anything from colinmitton that suggested the deletion of records.

Duane
Hook'D on Access
MS Access MVP

 

[colinmitton]

I found that out! I have just sorted it out though using a combination of suggestions here.

    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    
    ' from listbox in popup form
    Dim strTable1ID As String
    Dim strfield1 As String

    ' from form in background
    Dim strContactID As String
    Dim strContactfield3 As String
    
    Dim strWhere As String
    
    'pick up data to be copied from form in background   
    strContactID = [Forms]![frm_Contacts_Mgt]![C_ID]
    strContactfield3 = [Forms]![frm_Contacts_Mgt]![C_field3]
    
    ' get record ID for Table1
    If Me!List_All_Contacts.ItemsSelected.Count = 0 Then Exit Sub
    For Each varItem In Me!List_All_Contacts.ItemsSelected
        strTable1ID = strTable1ID & Me!List_All_Contacts.Column(3, varItem) & ","
    Next varItem
    
    strTable1ID = Left$(strTable1ID, Len(strTable1ID) - 1)
    
    ' get field1 to copy to contactfield3
    If Me!List_All_Contacts.ItemsSelected.Count = 0 Then Exit Sub
    For Each varItem In Me!List_All_Contacts.ItemsSelected
        strfield1 = strfield1 & Me!List_All_Contacts.Column(0, varItem) & ","
    Next varItem
    
    strfield1 = Left$(strfield1, Len(strfield1) - 1)
    

    ' open contacts table to copy in field1 from table1
    Set rst2 = db.OpenRecordset("tbl_Contacts", dbOpenDynaset)
With rst2
    .FindFirst "C_ID = " & strContactID
    If Not .NoMatch Then
        .Edit
        !C_field3 = strfield1
        .Update
    End If
End With

' open table 1 to copy field3 from the contact table       
Set rst1 = db.OpenRecordset("tbl_Table1", dbOpenDynaset)
With rst1
    .FindFirst "Table1_ID = " & strTable1ID
    If Not .NoMatch Then
        .Edit
        !Field1 = strContactfield3
        .Update
    End If
End With

    Me!List_All_Contacts.Requery
    Forms.frm_Contacts_Mgt.Requery

Set db = Nothing
Set rst1 = Nothing
Set rst2 = Nothing

I've then used the 'delete' which was suggested to remove old / unwanted records else where in my updated app.

Thanks folks.