Transfer data from main form to subform

I am redesigning a Vendor Directory database that I put together a few years
ago before I knew much about design. In brief, one or more certificates are
associated with each vendor, so rather than having Cert1Type,
Cert1ExpirationDate, Cert2, etc. fields on the main forms (and nowhere to
put Cert3) I am putting the information into a related table by way of a
subform. My question is how to go about transferring the data from the main
form to the subform. My first thought is to have a (temporary) command
button run code to copy the information from the main form fields to the
subform fields. When I am done I can delete the unneeded fields from the
main form's record source. There are about 130 main form records, and I
will probably need to do some editing after transferring the data, so one at
a time is not necessarily a bad way to go, but I wonder if there is a more
convenient way (if not for this project then for future reference).

I'm not sure why you are concerned about transferring data between "forms".
You need to append records/values from your main table to your related
table. I would do this with a union query:

SELECT VendorID, Cert1Type as CertType, Cert1ExpirationDate as
ExpirationDate
FROM tblVendors
WHERE Cert1Type Is Not Null
UNION ALL
SELECT VendorID, Cert2Type, Cert2ExpirationDate
FROM tblVendors
WHERE Cert2Type Is Not Null
UNION ALL
SELECT VendorID, Cert3Type, Cert3ExpirationDate
FROM tblVendors
WHERE Cert3Type Is Not Null;

Use the union query as the source for an append query that appends to your
related table.

--
Duane Hookom
MS Access MVP



"BruceM" wrote in message
...
I am redesigning a Vendor Directory database that I put together a few
years ago before I knew much about design. In brief, one or more
certificates are associated with each vendor, so rather than having
Cert1Type, Cert1ExpirationDate, Cert2, etc. fields on the main forms (and
nowhere to put Cert3) I am putting the information into a related table by
way of a subform. My question is how to go about transferring the data
from the main form to the subform. My first thought is to have a
(temporary) command button run code to copy the information from the main
form fields to the subform fields. When I am done I can delete the
unneeded fields from the main form's record source. There are about 130
main form records, and I will probably need to do some editing after
transferring the data, so one at a time is not necessarily a bad way to go,
but I wonder if there is a more convenient way (if not for this project
then for future reference).

Thanks for the reply. I am not concerned about transferring data between
forms, but rather with transferring the data in the underlying tables. I
should have said I need to move data from the main form's record source to
the subform's record source, rather than attempting shorthand and muddying
the waters.
Looks like a good time to learn about union queries, now that I know that is
the way to proceed. Thanks for pointing me in the right direction. It
really helps when I can narrow my focus like that.

"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
I'm not sure why you are concerned about transferring data between
"forms". You need to append records/values from your main table to your
related table. I would do this with a union query:

SELECT VendorID, Cert1Type as CertType, Cert1ExpirationDate as
ExpirationDate
FROM tblVendors
WHERE Cert1Type Is Not Null
UNION ALL
SELECT VendorID, Cert2Type, Cert2ExpirationDate
FROM tblVendors
WHERE Cert2Type Is Not Null
UNION ALL
SELECT VendorID, Cert3Type, Cert3ExpirationDate
FROM tblVendors
WHERE Cert3Type Is Not Null;

Use the union query as the source for an append query that appends to your
related table.

--
Duane Hookom
MS Access MVP



"BruceM" wrote in message
...
I am redesigning a Vendor Directory database that I put together a few
years ago before I knew much about design. In brief, one or more
certificates are associated with each vendor, so rather than having
Cert1Type, Cert1ExpirationDate, Cert2, etc. fields on the main forms (and
nowhere to put Cert3) I am putting the information into a related table by
way of a subform. My question is how to go about transferring the data
from the main form to the subform. My first thought is to have a
(temporary) command button run code to copy the information from the main
form fields to the subform fields. When I am done I can delete the
unneeded fields from the main form's record source. There are about 130
main form records, and I will probably need to do some editing after
transferring the data, so one at a time is not necessarily a bad way to
go, but I wonder if there is a more convenient way (if not for this
project then for future reference).