×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Writing a value from a form into a subform

Writing a value from a form into a subform

Writing a value from a form into a subform

(OP)
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

RE: Writing a value from a form into a subform

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.

RE: Writing a value from a form into a subform

(OP)
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

RE: Writing a value from a form into a subform

Quote (THWatson)

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

RE: Writing a value from a form into a subform

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close