Help With Forms Please 1

[CarolOB]

I have a main form called members and a subform called family. On the main form, two of the fields will have a value of either $0.00 or $10.00. The two fields are named Locker Fee and GHIN.
The subform will have information about each of the additional family members and, of course, two of the fields are also named Locker Fee and Ghin. (The forms are joined by a member ID number)
What I'd like to do is to have the two fields Locker Fee and GHIN on the main form (ie members) automatically show the correct total once the entry is made in the subform.
Example: member form currently shows $10.00 for Locker Fee and $10.00 for GHIN. If I enter a spouse's info. in the subform and I put $10.00 for the Locker Fee, I want the main form to now show $20.00 as the current total for Locker Fees. (There can be any number of family members in the subform and they will have either $0.00 or $10.00 entered in these two fields).
Please advise how this can be done.
Thanks a million! CarolOB

 

[hsitz]

Here's one way to do it, by placing two additional text boxes on the main form:

1. create two textboxes on the main form. One will be used for the locker fee total and one for the GHIN total.

2. In the locker fee total textbox put this:

=[locker fee] + dsum("[locker fee]", "family", "[memberid] = " & memberid)

3. in the GHIN total textbox put this:

=[GHIN] + dsum("[GHIN]", "family", "[memberid] = " & memberid)

NOTE: You might have to change those to match your table and field names exactly.

Also, the reason you need to create separate "total" textboxes is that any total textbox is going to be uneditable.

You might want to consider "normalizing" your data by having detail information about ALL family members -- including the actual member of your organization -- in the subtable/subform. That would involve adding a yes/no field to the subtable/subform of "member", which would be checked if that family member was the main member of your organization. Then you would just record the common information for the family in the main table (address, phone, etc.) This will pay off likely pay off by making things simpler down the road. For example, if you had it set up this way, then your total boxes on the main form would use the simpler formulas:


=dsum("[locker fee]", "family", "[memberid] = " & memberid)

=dsum("[GHIN]", "family", "[memberid] = " & memberid)

You would avoid similar future complexities by normalizing your data at the start. -- Herb

 

[CarolOB]

Thank you so much - that works great! The only thing I have to figure out now is how to make the GHIN Total textbox display the correct amount if the member doesn't have any family members.
(ie the member's GHIN will ALWAYS be either $10.00 or $0.00 and I'd like that to display in the GHIN total textbox rather than have it be blank when there aren't any additional family members.

Thanks again for your prompt response. CarolOB

 

[hsitz]

Try this in the total textbox:


=iif(isnull([GHIN]), 0,[GHIN]) + iif(isnull(dsum("[GHIN]", "family", "[memberid] = " & memberid)),0,dsum("[GHIN]", "family", "[memberid] = " & memberid))

 

[CarolOB]

That works perfectly - thank you so much!!!! CarolOB