VB to Swap in 2 tables 1

[DarkOne72]

I have 2 table that mirror one another so all the fields are the same.
Table Inv1 and Table Inv2
A form with a sub form showing like a split pulling data from both tables. Main form showing Inv1 and subform pulling data fromInv2.

What I am wanting to do is have a button that runs vb code that will move the record it is currently showing from Inv1 to Inv2 and at the same time move the current record showing in the subform which pulls from Inv2 to be moved to Inv1. (Basically it is swapping the two records location.)
In doing this I need to have some of the fields in Inv1 to stay static for the new record coming over; like Name and location.
I am including the code I setup but it isn't working properly maybe someone could assist me in getting this to work properly.

Private Sub Relocate_DblClick(Cancel As Integer)
 If MsgBox("Send to History and Make Changes." _
        & vbCrLf & vbCrLf & "Do you want to Move this record to History and Apply Changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "Inv1 Query"
            DoCmd.OpenQuery "Inv2_Query"
            DomCmd.OpenQuery "Swap-Inv2_Delete"
            DoCmd.Save
            DoCmd.SetWarnings True
  
    Else
            DoCmd.RefreshRecord
    End If
            
 Me![Inv1 subform]![Machine_Name] = Me!Machine_Name
 Me![Inv1 subform]![Seat_Num] = Me!Seat_Num
 Me![Inv1 subform]![Station_Num] = Me!Station_Num
 'Me![Inv1 subform]![Floor] = Me!Floor
 Me![Inv1 subform]![Dual_Monitor] = Me!Dual_Monitor
 Me![Inv1 subform]![Supervisor] = Me!Supervisor
 Me![Inv1 subform]![Monitor_Make] = Me!Monitor_Make
 Me![Inv1 subform]![Monitor_Size] = Me!Monitor_Size
 Me![Inv1 subform]![Monitor_Make2] = Me!Monitor_Make2
 Me![Inv1 subform]![Monitor_Size2] = Me!Monitor_Size2
          
 Me!Service_Tag = Me![Inv1 subform]!Service_Tag
 Me!Model_Num = Me![Inv1 subform]!Model_Num
 Me!Memory = Me![Inv1 subform]!Memory
 Me!Machine_Name = Me![Inv1 subform]!Machine_Name
 Me!Seat_Num = Me![Inv1 subform]!Seat_Num
 Me!Station_Num = Me![Inv1 subform]!Station_Num
 'Me!Floor = Me![Inv1 subform]!Floor
 Me!Dual_Monitor = Me![Inv1 subform]!Dual_Monitor
 Me!Supervisor = Me![Inv1 subform]!Supervisor
 Me!Monitor_Make = Me![Inv1 subform]!Monitor_Make
 Me!Monitor_Size = Me![Inv1 subform]!Monitor_Size
 Me!Monitor_Make2 = Me![Inv1 subform]!Monitor_Make2
 Me!Monitor_Size2 = Me![Inv1 subform]!Monitor_Size2
 Me!Comments = Me![Inv1 subform]!Comments
 Me!Date_of_Deployed = Me![Inv1 subform]!Date_of_Deployed
  DoCmd.Save
     Beep
 MsgBox "Your request has been processed, Thank you!", vbInformation, "Records Swapped"

 DoCmd.Close acForm, "frmSearch_Results"

Thanks in advance.

 

[kjv1611]

What's the reason that you have to run this from 2 tables? If you just need a mirror of one with some other/different data, why won't a basic query work? Then you can not worry about having to maintain 2 tables? Particularly if you're trying to make sure both tables stay 100% in sync. I just cannot think of one good reason for this setup.

If you are wanting to have 2 recordsources, if necessary, you could simply map both the form and subform to the same rowsource.

Maybe you can explain the reasoning behind all of this? Maybe I'm missing something?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[DarkOne72]

I was needing to keep the two table seperate because one is for "out in the field/at desk locations" and the other is for "inventory on hand"

Thanks

 

[kjv1611]

You could change your table structure to simply add 2 fields:
1. "Out In Field"
2. "Inventory On Hand"

Set their values to Numeric - Byte, and use 0 and 1 for values. 0 = "false" or "no", and 1 = "true" or "yes".

Then set each form/subform's row source to only look at those with a 1 or 0 for whichever field you need.

This method works great, and then you don't have to mess with something that is definitely not meeting any sort of standards. MUCH MUCH MUCH less of a headache!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57