Swap data in same table 1

[DarkOne72]

Good Day!
I was wondering if someone could assist me in a query to swap data in the same table.
Basically I have one table "Inv1" that houses records of floor information and computer information.
So lets say I have a record to where its serial number is 12345 and it is in column "Serial#" located on the 4th floor; now I have another record in the same table that its serial number is 987662 and it is located on the 5th floor. What I need the query to do is when your on the form looking at one of those records you click a button on the form it prompts you for the computer you want to swap it with and it will take the serial numbers from each record and swap it automatically but not touching any of the other information.

Example Before clicking button:

Serial# Floor Name Sup
----------------------------------
12345 4th John Shelli
987662 5th Dawn Joe

Example After button click and query ran:

Serial# Floor Name Sup
----------------------------------
987662 4th John Shelli
12345 5th Dawn Joe

I have tried building it with a query and a query that makes a table but I am not able to get anything to work.
Can someone please tell me if there is a way of doing this, I have racked my brain on this for a few days.

Thanks in advance!

 

[Andrzejek]

What is the Primary Key field in your table "Inv1"?

Have fun.

---- Andy

 

[DarkOne72]

It is an ID autonumber but the serial# filed can't have duplicates or be null.

 

[Andrzejek]

Inv1 table:

[pre]
ID Serial# Floor Name Sup
----------------------------
7 12345 4th John Shelli
8 987662 5th Dawn Joe
[/pre]
so you want ID 7 to have Serial# 987662
and ID 8 to have 12345

Assuming you do not have any records with the Serial# of 0 (zero) or -1
[pre]
UPDATE Inv1 SET Serial# = 0 WHERE ID = 7
UPDATE Inv1 SET Serial# = -1 WHERE ID = 8

UPDATE Inv1 SET Serial# = 987662 WHERE ID = 7
UPDATE Inv1 SET Serial# = 12345 WHERE ID = 8

ID Serial# Floor Name Sup
----------------------------------[blue]
7 987662 [/blue] 4th John Shelli[blue]
8 12345[/blue] 5th Dawn Joe
[/pre]


Have fun.

---- Andy

 

[DarkOne72]

Wow thanks for the fast reply, but I am abit confised. How do you make that work when your looking at the record in form view and you hit a button and it asks you which serial you want to swap with it (in a msg popup from the query) and it then it swaps it automatically?

 

[Andrzejek]

I just showed you one of the ways this can be done. Just the ‘over all’ logic.
How you implement this, is up to you.

I don’t know anything about ‘form view’ or ‘a msg popup’ or anything that happens ‘automatically’.


Have fun.

---- Andy

 

[DarkOne72]

Andy, I appreciate your insight on this however that is kind of tha place where I was stuck at.
Is there someone who could assist me in getting something that works please?

Thanks in advance.

 

[MajP]

something like this. Assuming you are on a current record and have a popup that you pick a record to swap. Step one is to be able to return a value from a popup especially if it is dialog.

Public Function getValueFromPopUp(formName As String, PopUpControlName As String) As Variant
  Dim frm As Access.Form
  DoCmd.OpenForm formName, , , , , acDialog
  'wait until form is closed or hidden
  If CurrentProject.AllForms(formName).IsLoaded Then
    Set frm = Forms(formName)
    getValueFromPopUp = frm.Controls(PopUpControlName).Value
    DoCmd.Close acForm, formName
  End If
End Function

On your pop up form the "Ok" button needs to have this code

Me.visible = false

The cancel button should just close the form.

so assume your popup form has a combo box "cmboID" that returns an ID. You main form needs to get call the popup and get the ID to swap.

Dim ID1 as long
Dim Serial1 as string
Dim ID2 as long
Dim serial2 as string
dim strSql as string

ID1 = me.ID ' the current record
Serial1 = me.Serial

'get id to swap with
ID2 = getValueFromPopUp("yourPopUpName", "cmboID")
'since the form is pop up execution stops until the form closes
Serial2 = dlookup("SerialField","SomeTable","ID = " & ID2)
StrSql = "UPDATE Inv1 SET Serial# = '" & Serial1 & " WHERE ID = " ID2
currentDb.execute StrSql
StrSql = "UPDATE Inv1 SET Serial# = '" & Serial2 & " WHERE ID = " ID1
CurrentDb.execute StrSql
Me.requery

However, I am guessing that you have made the serial a unique constraint. In that case you will have to set one of the IDs first to a dummy value or null, basically using the same method. If not when you try to swap it will not let you because you will have 2 IDs with the same serial.

 

[DarkOne72]

MajP --

You rock! I appreciate the code and while going through I noticed that you said a popup form; the popup i was using was in a query in the condition part that prompted you the name to swap with.
I do have the ID field as the unique identifier and I have the serial number unique so it cant have two in the same table as you stated.
Is it possible to maybe have an example db uploaded as zip of how you get it to work since I apparently went the wrong way on it; sorry!
I am trying to get the method above working but failing.

Thanks again for all your help!

 

[MajP]

If you have a db, I can take a look at it and modify it. I do not mind writing code, but that would require building forms and guessing how you use them.

 

[PHV]

the popup i was using was in a query in the condition part
Have a look here:
faq701-6763

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886