Subform Link Issue

[danhartman]

Hello,



This is somewhat tricky to explain, but I would love some help.

I have a master form (frmMaster) with two subforms (frmsub1, frmsub2). After selecting a number in a combo box (combo1) in frmsub1, a text box (text1) in frmMaster is automatically updated with the same number. Then, using the linking ability between the master fields and the child fields, a text box (text2) in frmsub2 is also updated with the same value.

The issue is the following: after selecting the number in combo1, I can see that the same number is populated in frmsub2. The next button I select is Save. What happens, though, is that the information in the main form and frmsub1 is saved - the information in frmsub2 is NOT saved.

Here is when it gets even stranger: If I first select the number in combo1, look down at text2, see that it has the right number, and then RETYPE in the same number into text2 and hit save, it saves in ALL three locations.

This is confusing, sorry, but it is a strange issue and I am not an expert with this.


Thanks,

Dan

[jzwp11]

Welcome to the forum!

The subform acts differently when you type something in a control on the subform versus when something is displayed in the same control because when you physically type something, it activates the form and Access essentially creates the record and thus stores the value. If a control is used to just display something, the form is not actually active so Access does not create the record.

With that said, if you are storing a value in multiple tables which is what is implied by having the value in the master and both subform, that violates good database practices. In a properly structured database, you generally only store the value in one location. (What would happen if you needed to update that value?--You would have to update it in all 3 locations).

In order to help you better, we need to understand what your database is designed to do and what business process you are trying to model. It would also help if you provided your tables and the fields in those tables. That can be done longhand as follows or you can post a copy of the database (with any sensitive data removed)

tablename
-field1name datatype
-field2name datatype
etc.

[danhartman]

jzwp11,

Thanks for the help. I've taken a college-level class on database design, so I understand what you're saying about bad design. I am relatively sure the design is good (we spent a while designing it and our professor gave us the ok to proceed). If you want a LONG explanation, read the next three paragraphs.

The business this is for takes a group of participants (age ranging from kids to adults) through a teamwork-building camp. When participants go through the camp, they generally do so as part of a group (ex: a soccer team, a chess club, an accounting division in a company, etc.). There are times where not all members of the group participate (if someone gets sick or can't make it, for example), but we would still like to track which group they are a part of for future business.

One table (Participant) tracks the participant's information (name, contact info, etc), another table (Membership) tracks which group(s) a person is a part of (each participant can be a part of many groups), and another (Participation) tracks which group a participant was a part of for a particular class. There are 3 forms related to this: (1) a participant who is attending the camp for the first time: it records personal information and the group (when he/she selects a group, it creates a new membership and records which group he/she was a part of for a certain class),(2) a form for a returning participants: does the same thing as (1) except it uses dropdowns, and (3) a form to record membership with a group for a participant who is unable to participate for a certain class or will participate in the future.

It is messy, yes, but it needs to be done this way - therefore, memberships cannot be collected from a query of class participation - it needs to be separate. I would list all the tables and forms but there are a TON of each:

Participant
Part_ID (autonum)
LName (text)
FName (text)
... (There are more but it is not worth typing)
Group
Group_ID (autonum)
GroupName (Text)
...
Class
Class_ID (num)
ClassDate (date)
ClassLocation (text)
Membership
Part_ID (num)
Group_ID (num)
Participation
Part_ID (num)
Group_ID (num)
Class_ID (num)
...

The form I am having issues with is form (1) listed above:
FORM: Participant
Subform: Membership
Subform: Participation

I need the Group_ID field to be populated for both subforms after the participant selects the group in the Membership subform.


So, as I mentioned above, I think that this design is correct. The follow-up question to you, is can I make the subform active instead of simply displaying the number.

THANK YOU!

[jzwp11]

I think your table structure looks OK with the exception of possibly the participation table. But first let's get the main form and the 1 subform issue fixed.

If you set up your relationships in the relationships before you do the forms, Access should take care of linking the forms without you having to copy anything between the form and subform. BTW what would you need to copy from the subform, back to the main form? So, the main form should be based on the participant table. The first subform should be based on the junction table Membership. The link should be created between the part_ID of the participant table and the part_ID of the Membership table. You would use a combo box based on the group table to populate the group_ID field.

Now the participation table. A group can participate in many classes. Will a class only consist of 1 group or many? If a person is a member of a group that is taking a class and they are present and not sick (i.e. not excuse for not taking the class), do they have to attend the class or can they attend another class (as part of another group to which they belong)? If that is the case then you I think your second subform must actually be a subsubform (i.e. a subform in the first subform) and you would need a double link (groupID between subform and subsubform AND part_ID between main form and subsubform). This can be done with the linking of the forms (no copying necessary)

[danhartman]

What I need is to copy the group selection in Membership into the Participation record. Therefore, a when a new participant is entering his/her information, it creates a membership record (Suzie belongs to the Soccer Team Group) and a participation record (Suzie went through a class with the Soccer Team Group).

I did what you suggested. In subform 1, which contains subform 2, I attempted to link the Part_ID and Group_ID fields. I placed subform 1 in the master form and linked the Part_ID of the master to the Part_ID of subform 1. Unfortunately, it didn't work. Apparently Access tried to link the subsubform to the master form.

In the end, I made a fix that didn't fix the actual issue, but it achieves my purpose. They need to sign a consent form when entering their information by checking a box. The consent form is in the Participation subsubform (a consent form must be signed for each time they participate). They have to select the group in the Membership subform. Since they have to select information in each form and subform, it now saves in all three forms.

Thank you so much for your help. This forum is so powerful and there are lots of great people helping out. If you would like to keep working on it thats fine, I'll let you know if I find anything out.

Thanks again!

[jzwp11]

If participation in a class is an individual decision then each participant should be tied to the particular class directly. You will always have the associate between person and the group to which they belong and you can show that with a query.

BTW, if you want me to take a look at the form, can you post your database (with any sensitive data removed)?

[danhartman]

Thank you for your suggestion. It is a individual decision, correct. But we would like to tie participants to a group, even when the participant cannot attend a class. If he/she cannot participate, we would like the option to still connect a person to a group for future purposes.

I will post it in a few days hopefully for you to take a look - pretty busy right now and it needs some work before its in a working order.

Thank jzwp11!

[jzwp11]

Just to be clear, the person can be tied to a group; I have no issue with that. The problem lies with the class. Since it is an individual decision for a person to attend a class irrespective of which group they belong then you would join the person to the class, With respect to the table structure that you provided earlier, I would get rid of the Group_ID field in the Participation table (shown in red below). Additionally, if you include a date field in your membership table you can correlate which group a person belonged to at the time the class was held:

Participant
-Part_ID (autonum)
-LName (text)
-FName (text)
... (There are more but it is not worth typing)


Group
-Group_ID (autonum)
-GroupName (Text)


Class
-Class_ID (num)
-ClassDate (date)
-ClassLocation (text)

Membership
-Part_ID (num)
-Group_ID (num)
-dteMember (date)

Participation
-Part_ID (num)
-Group_ID (num)
-Class_ID (num)