Re: Copying data between fields

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

"Steve Schapel" wrote:

> 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 Schapel, Microsoft Access MVP
>
>
> Dudley wrote:
> > 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?
>
.