Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    lost record in part of a relationship (xp)

    wow I really have a problem now! A week ago, the database did this and I lost the whole days work in getting it straightened out-- am hoping there is a better way. When entering a family into records of services; I realized there was a need to change some information about the whole family and used a macro to go to the family record and make the changes-- unfortunately, I had made changes on both tables and needed to drop changes in one record which I did-- here is the bad part-- the linking information for the two tables disappeared and would not let me correct it-- that was NOT the data that was to be dropped-- so now, I have a family (the members of) with all their identifying numbers who are no longer linked to their family data and am also not able to sync the replicas *sigh* -- since this is the second time this has happened in the last week, I am feeling really worried that I will have half the 1700 children entered for Christmas and suddenly loss the whole thing-- please tell me there is a fix.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lost record in part of a relationship (xp)

    I can't figure out what you're describing. What do you mean you changed data and the linking information disappeared? If you have orphan records, the only thing you can do is create a new family record for the family and then link the family members to the new family record. I don't understand what you mean by a need to change some information about the whole family. Family information should be in the family table, not in the individual tables, so what information were you changing that caused this problem. To avoid it in the future, you need to be clear about how your data is structured and what can be safely changed. This is one of the big arguments for autonumbers as keys like a family ID, since there would never be a need to change that number. Meaningful information may require changes and that can get you into exactly this kind of situation. When you say you used a macro, are you talking about an Access macro or a code routine? I can't think of anything you could do with a macro that could cause this except to run queries, and it would be safer to run them from the interface.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lost record in part of a relationship (xp)

    Charlotte-- my total frustration sure showed in that post-- and I can see that it makes no sense at all--so I will start again. I am having all kinds of problems with this database--- and I know that I have put it off far too long-- I MUST redo it after Christmas. But here is what has happened twice in a week. I have auto numbers for the family ID and for the personal ID all other tables are linked back with these two numbers. I have a different table (and entry form) for each service that the center offers families. At the moment, the ones that are the most used are "aid" and "Christmas 2004" (not the actual names but that is the idea. To enter a service in "aid" the form shows the family data and the person data and the subform shows the actual service. Sometimes, our families make changes (the most challanging is when members of one family are now living with another family-- happens fairly often). I can normally use the micro button to open the family records where the sub form shows all family members and make whatever changes are necessary. And this works well, unless I am not paying attention and make a change on the form/sub form "aid" click the micro and make another change in
    "family"-- I get an error message that the table was being edited somewhere else (by me) and changes can not be made-- no problem, I just cancel out one of the changes, close and redo the change. HOWEVER twice in the last week, one of the auto numbers (remember these link the data) just blanks out! and it can not be added again since it is an auto number.... today I was successful using compact and repair and do not think I lost any work--but last week I lost the entire days work (cant figure out how--but trust me it was gone) The most frustrating part was in not being able to restore the lost data through sync ing the replicas--- the sync just failed until the repair was accomplished and all the work was gone. Maybe I have done a better job now that I am a little calmer... please forgive my panicked note.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lost record in part of a relationship (xp)

    "Whatever changes necessary" is still pretty vague. It sounds like you have a situation where you are editing in such a way that you actually delete a family record. Is that the case? You say you have two autonumbers, family and personal IDs, but I can't figure out which one is "blanking out". Do you have referential integrity enforced between the tables? If so, do you hafe cascading updates turned on?
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lost record in part of a relationship (xp)

    no, not deleting the record-- I did not make the change, but somehow, while telling the program to not save the changes-- so that it stopped giving me the error message, the family id number disappeared. (the form with the family was still on the screen--but the family number was gone and it will not let it be added since it is an auto number) I dont ever change a family ID number or a personal ID number. If those are changed or deleted; so are all the records that are related. The kinds of changes I was making are the usual, -- addresses or correcting errors in DOB or adding a qualifying date that a USDA form was attached... the kinds of changes are many but they are not actually related to the problem-- I have never had a number just disappear and I have used this database since 97! But there have been many weird happenings this year and I know that I must grab the data and start over... have just been putting it off. The part I dont understand is that when I used compact and repair-- I lost an entire days work-- it doesnt seem possible since each record saves as you move to the next entry-- but I had to redo every bit of the days work (hey, it could have been worse, the whole thing could have gone corrupt on me)-- I would just like to figure out what happened so I dont have that happen on a 20 hour data entry day. This may be a problem that you cant get a handle on without looking at the dratted problem on a screen. Thank you for taking your time to try.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lost record in part of a relationship (xp)

    Let's back up. For the sake of this discussion, let's assume you have a tblFamily with an autonumber FamilyID and a tblMember with an autonumber MemberID. There would be two ways to link them, one through a foreign key in tblMember that held a Long Integer value that was a valid FamilyID value. The other would be through a join table tblFamilyMember, which would have two foreign keys, a FamilyID and a MemberID. Is one of these the way you have your relationships set up?

    The way you're describing the problem sounds like a situation where the data is still there but the forms are not relating correctly. If you look at the tables, is the family ID still in the family member table or not? One thing that could cause this is an improper link between the form and subform and another is accidentally moving out of the correct sequence of records (i.e., hitting page down and moving to the next record or a new record). Given what you describe, I would suspect the former prolem but it is hard to tell from a description alone. Is there any way you can post a stripped down copy of the tables and forms involved with some phoney data to demonstrate the relationships?
    Charlotte

Posting Permissions

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