Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    move subform records to new main form record (2000

    The main form is frmMaynooth, based on tblMaynooth
    There can be either one or two persons showing on the main form (either Joseph as Person1 and Mary Greene as Person2, or Edith Brown as Person1, no Person2)

    It has a subform called "[tblChildren subform]" based on tblChildren. The tables are linked by UniqueID on tblMaynooth with MemberID on tblChildren

    There are 5 fields on the subform - ChildName, ChildLastName, DateOfBirth, Baptism, Confirmation

    The subform may or may not have records.

    The following is an example...
    Joseph and Mary Greene are Person1 and Person2 on the main form
    The [tblChildren subform] subform shows Jimmy Greene as the first record, and Sally Greene as the second record.

    My problem is this...
    I want, upon click of a RemovePerson1 box on the main form, to remove Joseph Greene from the record. The simplest, and most straightforward way, is to move Mary Greene to a new record, and also move the Children, Jimmy and Sally, to the new record so that they stay with Mary Greene and are removed from the record for Joseph Greene.

    What is the process to accomplish the moving of the children to the new record created for Mary Greene?

    Thanks.

    Tom

  2. #2
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    I figured out that I can do what I want if there is only one child in the family. The method would be to retrieve the new UniqueID value, from Mary Green's newly created record, go back to the original record for Joseph Greene, go to the subform and change the value of MemberID to match Mary Green's new record.

    However, testing shows that this only works if there is one child to be moved. If there are 2 or more children, the first child is moved to the new record and others stay put.

    Tom

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: move subform records to new main form record (

    I'd try to avoid creating a new record for Mary Green. Can't you change some characteristic of her existing record instead?

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    Hans
    Ideally that might be the case.

    However, the problem is that, at the outset, there are two people on the record, Joseph and Mary Greene, and both share the same UniqueID number. If one of them dies, or they divorce, it is no longer desirable to carry the other one on the same record.

    ...unless you had something else in mind.

    Tom

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: move subform records to new main form record (

    Tom

    I hope you don't mind but I'll jump in here. I would suggest that there is a redesign of your tables then. Instead of the master/child relationship be Parent(1)/Child(many). I would swap it around to...and I will say this in words

    One child can have more than one parent (Ma and pa), however a mother and father can have more than one child so I feel that there is a many to many relationship here.

    I suppose you can then take a parent off the list then...what are your thoughts on that view of things?
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: move subform records to new main form record (

    I don't know about the structure of your tables, but I'd try to set it up in such a way that you only have to change a value when someone dies or divorces.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    Jerry
    There may be different ways of designing the tables. And I get your point about the children/parent relationships. However, the vast number of the records, probably 90%, will have no children in the household unit. The primary records are adults - some 2 adult persons in the family, some with only one adult. So it's not clear to me how designing the tables around the children would solve anything. Unless I am missing the point.
    - - - - - - - - - - - - - -
    Hans
    You say, "I'd try to set it up in such a way that you only have to change a value when someone dies or divorces." That's fine. However, I am still faced with the same problem of moving one record off to a new record. We wouldn't leave the deceased person showing on the record with his widow, and we wouldn't show the divorced husband on the record with his divorced spouse.

    Tom

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    Do you think the following is an acceptable way of doing it?

    Me.tblChildren_subform.SetFocus
    If IsNull(Forms!frmMaynooth![tblChildren subform].Form!ChildName) Then
    GoTo UpdateForm
    Else
    Set rst = Forms!frmMaynooth![tblChildren subform].Form.RecordsetClone
    With rst
    .MoveFirst
    If .RecordCount = 0 Then GoTo UpdateForm
    Do Until .EOF
    .Edit
    !MemberID = lngNewUniqueID 'This is the UniqueID number from the newly created record
    .Update
    .MoveNext
    Loop
    End With
    Set rst = Nothing
    End If


    ' Update form
    UpdateForm:
    Me.Requery

    Tom

  9. #9
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    Sounds like a church database to me. The way I have set up the one I designed for the church I work for is as follows:
    tblIndividuals---tblIndividualFamilyJoin---tblFamily

    In the Family table is info unique to the family unit which we have defined as being based on mailing address. All individual information - baptism, confirmation, DOB, etc is recorded with the individuals. In addition, things like work phone numbers are assigned to individuals. Individuals are assigned to the family through the join table.

    When a divorce happens, you merely remove the appropriate individual from the family unit. If both parties remain members of the church, you set up a new family for the one who moved out.

    We got around the father, mother stuff in defining families by using the rather prosaic titles first adult householder and second adult householder.(There can only be one first adult householder but more than one second adult householder and allows us to distinguish between adult and minor children living in the household.) This deals with, for instance, the family unit where an adult single son lives with his mother. This is also used as a filter for things like printing membership lists where the wife is a member but the husband is not. Since she is the one giving money, the tax receipt can go in her name. It gives us great flexibility in not assuming a traditional mom, dad and 2.3 children.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    Peter
    You got it. It's a church database.

    Some questions, if you don't mind...
    1. With your tblIndividuals--tblIndividualFamilyJoin--tblFamily approach, is the tblIndividualFamilyJoin merely a linking table, which creates a many-to-many relationship? Or something different? Can you say a little more about the design?
    2. Say that Sam and Mary Jones divorce, is it merely a matter of removing Sam? Don't you have some way of maintaining his records, even though he has gone, as you wouldn't want to lose track of his history?

    Your explanation of how you get around the father/mother stuff started off pretty much like the approach I was following. I use Person1 to indicate the primary person on the record and Person2 to indicate the additional person on the record. However, I was thinking in terms of keeping a separate record for the adult son living with his mother.

    I'll try to get through my head the way you have approached this. I wish that I had access to a model of how you have set things up.

    Peter, I am not a professional programmer. The programming that I do is as a retired volunteer, unpaid.

    Tom

  11. #11
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    1) You are correct it is a many to many relationship. This allows for flexibility in how the family is defined. In essence, any individual can be assigned to any family. In addition, when the individual is assigned to the family, their relationship, child, adult, grandparent is also assigned. In a practical sense it can allow for the possibility of children who divide their time between two parents and for reasons germane to that family they can be assigned to both parents (say joint custody and both parents remain in the church - unusual but possible)

    Individuals are assigned to a family on a subform and removing the assignment - death, divorce - is a simple as deleting that person from the subform. They still exist as individuals, they simply are no longer assigned to that family. Which leads me to...

    The definition of a family: In the model we use, a family is any group of people at the same mailing address. That's it.

    2) I've somewhat answered that above but not completely. In terms of the family definition, you remove him because he is no longer at that address. If you want to keep some information (because he remains a member) you set up a new family (of one) for him at his new address and assign him to it. If you want the children's relationship you can add them in. In terms of history, his donation envelope number is assigned to his identity as an individual, it is not assigned to the family. When we confirm kids at 14, they get envelopes of their own. Donations are assigned to the envelope number ,not the family.

    What that means is there is also an envelope assignment many to many table. This allows for a couple to share an envelope number or each to have their own. Duplicate receipts are eliminated with various queries.

    This also accounts for the adult son living with the mother. They both donate separately so they both have their own envelope number but since they live at the same address, they are in the same family and data normalization is respected. It also allows for the scenario in the opposite direction where a young adult is at university and wants to receive the Newsletter by mail. Their university address is a new family unit and we can activate it during the school year and turn it off when home for the summer. The student is treated as a family when at school and re-integrated into the local family unit when home for the summer.

    I will try to post a screen shot or a stripped down version of the backend for you tomorrow when I am at work. We've been using this model very well now for 3 or 4 years and it has stood the test of time. The nice thing for the parish is that since I work there, when new scenarios arise, I can adapt the database. The hardest to deal with has been the reassignment of envelope numbers as people switch to pre-authorized remittance and we print fewer boxes of envelopes. The database was never designed with that in mind and we have had to do some workarounds.

  12. #12
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    Peter
    Thanks very much for your comments. And I look forward to seeing a screen shot, or a stripped down version of the backend - that will be really helpful.

    I think I understand what you have done. Not all the intricacies, for sure, but the basic idea.

    In this particular database, I don't need to worry about financial contributions. There are 5 rural congregations that make up a Northern Ontario pastoral charge. Each church keeps its own financial records, and each then contributes a specified % of the pastoral charge's expenses.

    The database will keep track of people, their membership/adherent details, etc. for the entire pastoral charge, but each person will be a member of a specific congregation.

    I know about the "reassignment of envelope" problem. I designed the database for the church at which I was minister prior to retirement. I solved that one by having a different numerical series for (a) 1-699 for persons who brought a printed envelope to church with them, ([img]/forums/images/smilies/cool.gif[/img] 700-899 for PAR contributors, 900 and over for "occasional" or "yearly lump sum" contributors. Envelope numbers had a StartDate and an EndDate and so could be reassigned as necessary.
    That database has been functioning well for a few years now. The interesting further aspect of it is that the Historic Roll database was developed independently, and then I integrated things about 15 months ago. It could be done better if built together, but so far I have resisted the temptation to rebuild the whole thing, choosing to follow the "if it ain't broke don't fix it" route.

    The one upon which I am currently working, I want to do differently - to bring historic membership data and current people information together in one place...and probably having a better product in the end. At such time as I get this new one working properly then I might consider rebuilding the other one.

    Thanks again for your input. Wonderfully helpful.

    Tom

  13. #13
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    As promised, here is a jpeg of the relationships. Some comments about the schema:
    1) Family/Individual Join Primary is for the nuclear family, Family/Inidivdual Secondary is to record relationships such as cousins, grandparents etc. We have a large proportion of the membership that are related. Fortunately, the parish is big enough that it's not starting to show yet... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    2)Par and Par Details (Pre-Authorized Remittance) is a set of storage tables to automatically enter these donations when they go into our bank account. The secretary records any changes needed for that month and then hits OK. Everything in Par is copied and inserted as a deposit into the donations. Saves about an hour of entering time per month.
    3) The delimeter fields you see in the committees table is to allow for various contradictory definitions (from a database point of view) of committees. With a lot of code, Age-related groups (Sunday school classes), voluntary groups (women's auxiliaries) and parents of age-related groups can all be defined in one place and show up in one set of listings. What this means is any defined group in the church can be treated as a committee. If they are age-related they are calculated on the fly. If they are voluntary, they are assigned individually. There are several posts under my name from 2003 and 2004 that cover the machinations I went through to make this work with a lot of help from several lounge heros. This committee facility allows for printing reports, labels and combinations of the two.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move subform records to new main form record (

    Thanks, Peter.

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •