How can I transfer from one field to another with a command button

[Guest]

Simply stated, on a Form, I want to be able to use a command button (or other
method) to tranfer data from one field to another (preferably between
different tables).

In more detail: my Form shows various information in several fields about:
1) a Client, and 2) the client's Spouse, drawn from a Table in the same
Database. From time to time, it will be necessary to "switch" the Client and
Spouse, so that all Client information is shown in the Spouse fields, and
vice versa. Rather than copy-paste all the information from the various
fields by hand, I wanted to create a Command Button in my Form that will make
the switch automatically, by doing the following:

1) Copying all the Client-related fields to temporary storage fields,
2) Copying all the Spouse-related fields into the Client-related fields,
3) Copying all the temporary storage fields into the Spouse-related fields,

Hence switching the Client and the Spouse. Easy concept, but I can't figure
out how to set up the Command Button. Should I use a macro? If so, which
commands? Should I use queries? And, most important, I want to make sure
the transfer is limited to the currently-viewed Record; it should not be a
global switch across the entire Table (obviously).

Can someone lend me some help?

 

[DL]

If the client can be either the husband or wife, or perhaps both, then
perhaps you should ammend your db design
ie Client table linked to Names table

 

[Guest]

*rolls eyes*

For reasons I will not expound upon, the database must exist in its current
format.

If you are going to respond, could you please address the question? It
seems straightforward enough...

Cheers.

 

[John Vinson]

You'll need to do it in VBA code; Air code here, untested, an needing
to be adapted to your needs. I'm imagining a command button named
cmdSwitch and plausible form control names. This is inelegant,
brute-force programming; a more refined design would use an array and
looping, but this will work.

Private Sub cmdSwitch_Click()
Dim vHold As Variant
vHold = Me!txtClientLastName
Me!txtClientLastName = Me!txtSpouseLastName
Me!txtSpouseLastName = vHold

vHold = Me!txtClientFirstName
Me!txtClientFirstName = Me!txtSpouseLastName
Me!txtSpouseLastName = vHold

< etc. etc. >

End Sub

John W. Vinson[MVP]

 

[Guest]

John,

Thank you, thank you, thank you! This is EXACTLY what I need. I didn't
know the VBA reference "Me!" with which a viewed Record in a Form refers to
itself. Works like a charm.