Copying data between fields

[Guest]

I have created a database in Access XP to record details about companies, and
I would like to have command buttons on the data entry form to copy data
where the same person occupies more than one position (to avoid having to
repeatedly enter the same data). For example after entering company secretary
details, clicking 'Also Director' would copy secretary forename field to
director forename field, surname to director surname etc.

Can anyone tell me the VBA code for copying data between fields in a form
please?

 

[Douglas J. Steele]

Me.ToField = Me.FromField

 

[Guest]

There are two ways:

A. You can use it on existed records also, there few stages to follow:
1. create varible in the form declaration
dim MyField1 as string,MyField2 as number, MyField3 as string .... (as many
that you need)
2. on the copy button assign the values from the fields in the form to the
variable you created

MyField1=me.Field1
MyField2=me.Field2
MyField3=me.Field3

you can copy only the records that are relevant

3. on the paste button enter the code.

DoCmd.GoToRecord , , acNewRec
me.Field1=MyField1
me.Field2=MyField2
me.Field3=MyField3

that way you can repeat the paste as many time that you would like with the
same values.

Or
B. To copy all the fields to a new record, and then remove the un wanted ones

' To copy the current record to a new record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
' To remove the values from the Fields
Me.Field1= ""
Me.Field2= 0
Me.Field3= NULL

 

[Steve Schapel]

Dudley,

In addition to the excellent advice proffered by Mr Steele, concerning
the "how" aspect, allow me to make a comment on the "why" aspect.

One of the great benefits of Access is that it uses Relational Data
concepts. This means that generally, you only ever have to enter one
piece of information in one place, as long as you design the database
according to relational principles. In your example, this would mean
that the people occupying the various positions in the company would not
be stored in the same table as the company's details. Basically, it is
not really good design to have a secretary forename field and a director
forename field, etc. This is sometimes known as the "fields as data"
trap. Database thinking about this, is that there is a one-to-many
relationship between the Company entity and the Positions entity. As
such, there should be a separate table for Position Occupancy, where you
have a field to identify the Position, and a field to identify the
Person, and each record in this table relates to the person in each
position. The field that identifies the person is possibly a PersonID
field or some such, that relates the Positions table to the Persons
table. In the Persons table, you have the person's name and other
details, and so in this model you only ever need to enter the person's
forename and surname etc once, in the Persons table, no matter how many
positions in the Company they occupy, and no matter when they move from
one position to another or whatever. At first sight, this may seem more
complicated than the way you have got it now, but I assure you it is
actually simpler in the end. If you are able to revise your database
structure, I would suggest you consider this.

 

[Steve]

If you are copying in a Datasheet View or in a multi-record form, CTRL-'
(Control apostrophe) will copy the data from the record above it. If
you are in a date field, CTRL-; (control semicolon) will insert today's
date. Just a small bit of trivia it took me 10 years of using Access to
learn!!! <g>

 

[Guest]

Steve,

Many thanks to you and the others who have replied so promptly with
extremely helpful advice. Me.ToField = Me.FromField - so simple yet I could
not find it through Help or by googling. I will also find Ctrl-; for today's
date very useful.

Regarding your comments about database design, I can see that I have not
designed it the proper way, but I do not think my Access skills are up to
making the proper way work for what I am doing. I am a British company
formation agent, and I use each record one off to create an email and
attached Memorandum and Articles formatted in accordance with the Companies
House spec for incorporating companies electronically.

To do this we enter the data, then open a mail merge copy of the Memorandum
& Articles, and print to file as "xxx.mem" (the quotes are needed because we
have to send them as a print file with the suffix .mem, and Word insists on
..prn unless the file name is in quotes). Then we open a mail merge doc with
the text required for the email linked to a Access query (Word if/then
instructions delete any officers not required in that particular case), and
use a macro to strip out the blank lines and copy the result. We then open
Outlook Express, paste the result into the email, attach the Mem & Arts, and
send the email.

This is all very clumsy, and the limit of 255 fields restricts me to 4
directors and shareholders (although it is extremely rare that clients want
more), but it does produce a result which is acceptable to Companies House.
Any advice on improving the system gratefully received.

Dudley

 

[Guest]

Hi Steve

Although I know the CTRL-' trick, is there a way to do this in code not
using SendKeys as this has shortcommings. Ie: I have a subfrom datasheet
and generally a particlar field will repeat but not always, so I would like
on entry of that field for it to copy the line above. Yet still allow them
to change if they need to. The subset of records in the datasheet may have
20 records and this field may change only 2 or 3 times through the 20

Thanks in advance
Louis