Writing a value from a form into a subform

[THWatson]

This is a database that tracks information for members of a Kiwanis Club.

On frmMembers, there are five tabs, two of which are "Membership Data" and "Legion of Honour."

The Membership Data tab has a number of controls, plus a subform "fsubAwardsGiven." The subform has four controls: Award, DateGiven, AwardID, and MemberID.
The values in the subform are written to "tblAwardGiven"
Note that a Member can receive any of 6 awards, one of which is "Legion of Honour".

The "Legion of Honour" tab has three controls: LegionofHonour (records number of years to be awarded), LegionOfHonourYear (tracks the date of the current award), and #ofYearsinKiwanis (this last field is automatically filled based on DLookUp("[LengthMember]","qryYearsInClubOVER25","[MemberID]=Forms!frmMembers!MemberID").
The "Legion of Honour" award is given in 5 year increments after a member has been in the club for 25 or more years.

Issue to be solved:
The person who keeps the database goes to the "Legion of Honour" tab in frmMembers, enters (1) the Number of Years awarded and (2) the date of the award.
What would be the best way for that information to automatically be written to the subform "fsubAwardsGiven"? This would alleviate the necessity of going also to the "Membership Data" tab and entering the data again in a new row in the subform.

Thanks for any assistance that can be provided.

Tom

 

[Darrylles]

Sounds like your design is wrong.
Why are you storing exactly the same values in different tables?
I assume you are, because your form object copies are obviously not pointing to one, single data source.

 

[THWatson]

Darrylles
Could be the case, but I can't seem to rectify what the problem is.

One table is tblMembers. It keeps track of various pieces of data regarding members, such as MemberID, LastName, FirstName, (plus other biographical data), LegionofHonour, LegionofHonourYear.

tblAwards is simply a container for the various possible awards. It has 6 fields: Kiwanian of the Year, Mel Osborne Fellow, Life Member, Honourary Member, Legion of Honour, Governor's Pin.

tblAwardGiven tracks the awards that have been given to members, and is linked to tblMembers by MemberID

tblAwards is linked to tblAwardGiven by AwardID

I explained in the original post what happens in the tabs on the form. It seems to me that if an Award is added in the Legion of Honour tab it should write automatically into the subform on the Membership Data tab but it doesn't.

Tom

 

[dhookom]

It has 6 fields: Kiwanian of the Year, Mel Osborne Fellow, Life Member, Honourary Member, Legion of Honour, Governor's Pin.

Do you really have field names that are names of awards? What happens if you add another award type?

I would take awards out of tblMembers and place them as records in a junction table between a table of award types and tblMembers.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[THWatson]

Thanks, Duane
The six fields in tblAwards are the awards that the Kiwanis Club has available to present. An additional award could be added but, historically, these are the awards. The most common is "Legion of Honour" which is first given when a member has been in the Club for 25 years and is given at each additional 5 years of membership.

I'll take a run at your suggestion.
Tom