Append Fields In Subform To Another Table

[Guest]

I am using Access 2000. I have a subform within a form. The subform is
setup as a continuous form. The form and subform allows me to bring up an
employees inventory record(s). I added two additional ‘unbound’ fields to
the continuous subform. This was done for the purpose of adding additional
information to the record(s) and then later saving all the subform records to
another table. My problem\question is two fold.

First, the problem that I am having is if an employee that I query has
multiple records in the subform and I put the information that’s needed in
the ‘unbound’ field the info. repeats over and over throughout the other
unbound fields (column) in the subform. I need to stop this from happening
of course because each record in the subform needs to be able to hold
different information.

Secondly, I would like the user to be able to press a command button and
have all the information in the subform (all records) and unbound fields be
appended to another existing table. First, I don’t know if it is possible to
do this with a subform and if so, I don’t know how to address the subform in
the code properly. I have used the below code to append directly from a form
into a table before and it has worked OK:

Private Sub Command33_Click()

DoCmd.RunSQL "insert into EMPONTBL (ID, LAST, FIRST, GROUP, CATEGORY,
DESCRIPTION) values (forms!ENTFRM!ID, forms!ENTFRM!LAST, forms!ENTFRM!FIRST,
forms!ENTFRM!GROUP, forms!ENTFRM!CATEGORY,
forms!ENTFRM!DESCRIPTION)"

End Sub

Any ideas or suggestions on how I could modify this code to work with a
subform that has multiple records and to stop the information from repeating
in the unbound fields of the subform will be greatly appreciated.

Thanks in advance.

 

[Rick B]

The most obvious question here is why are you taking records and copying
them to another table? That is duplication of data and not done in
normalized database design.

Rick B

 

[Guest]

Thanks for your reply Rick B. I thought about that but it's the only
solution that I can come up with for the following problem. The table has
two fields in each record that needs to be updated about once a month while
retaining the past information for reporting purposes. For instance, each
month the employee would take a particular training. The supervisor needs to
know the date of training and what type of training the employee took
including all past training info. I figured that inserting the data and
appending it to another table to keep all past records associated with the
employee would do the trick. Is there a better way to retain all the past
data in these two fields?

Thanks.