Passing data from subform to main form

[hunarch]

I have a mainForm (for tblMain) and a subForm (for tblComments).

A text field (control source "Comment") in the subForm populates tblComments with that text (comments). The keyed autonumbering extends from 1 to as many comments as there are in the database, of which tblMain is but one.

A number field in mainForm populates tblMain with numerals from 1 to 5 (their control sources being "Cmnt1" to "Cmnt5". Hence, for each record in subForm, up to 5 comments can be made (actually, at this stage in my programming, more can be made but they won't get recognised).

For each record in mainForm, as I enter comments in subForm, I want the entries to be assigned a number (from 1 to 5 in order of entry) i.e. corresponding with the subForm record numbers, and for those numbers to then be passed to the corresponding Cmnt1 - Cmnt5 fields in mainForm - and thus to tblMain.

How can this be achieved?

I have pored over similar postings, but my skills are too rudimentary to recognise their relevance!

RodH

 

[MajP]

Sounds to me like your data base is not normalized. Why do you have 5 comment fields in the main table? You should have a table called comments with a foriegn key relating back to table Main.

tblComments
autoCommentID
memoComment
intMainID

No need to assign numbers you can sort the comment based on auto number. In a report you can just print a number next to the comment based on sort order.

 

[hunarch]

Thanks for your reply MajP.

The 5 comment fields were provided so that some of the "Main" tables (of which there are approx 20) could share the same comments. E.g. some comments are unique to some tables, but various combinations of other comments are shared by various other tables. I must say that I am now a bit hazy about the reasoning. The relationships are set up correctly with primary and foreign keys.

Assuming that provision for the 5 comment fields was sensible, does your suggested code still apply? If so, what do I do with it!?

Your reference to the report procedure suggests manual intervention, whereas I have reports set up to be fully automated.

I appreciate that my inexpert explanation of my problem makes assistance difficult, but I hope I might be prodded further along the track!