Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    492

    Unanswered: Delete Subform Item

    Hi all -

    I have what seems like a simple question, but I cannot figure it out for the life of me. I have an employee form, and an assignment subform. Basically the person can have multiple assignments, but only one active one.

    The subform looks like so (assignment_id is hidden, but showing it here for display purposes):

    Code:
    ASSIGNMENT         ASSIGNMENT_ID       START DATE      END DATE
    -----------------------------------------------------------------
    CLERK               1                  1/1/1985           1/1/2000
    SALESMAN            2                  1/2/2000           1/2/2006
    MANAGER             3                  1/3/2006

    Now I have enabled deletes in the subform, and want to only allow deletes if the record is not yet end-dated. I have created a Before Del Confirm trigger, but whats weird is that when you click the delete button on the toolbar, it actually shifts one record down and tries to delete the record below it.

    For example, if i try to delete assignment 1, it actually will try deleting record 2. I know this b/c I msgbox'd the assignment_id, and its always the record below it.

    Any ideas if this is normal, and how to get around it? I can provide more detail if necessary
    Oracle OCPI (Certified Practicing Idiot)

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The first thing I would suspect is that your Before Delete is somehow moving the focus to the next record. Put a msgbox at the begining to tell you which
    record it is looking at. Try it again at the end.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And what is the method you use to perform your delete?
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Geek, Owen

    First off thanks for your help to begin with. I'll try not to make this too long...I am currently using a Before Del Confirm trigger to pop up a custom msgbox depending on whether the assignment is active or not. I have not yet written any code to delete the record, so that may be part of the issue.

    The first line in the trigger is msgbox(me.assignment_id). As I mentioned before, when my cursor is on the first visible record (id = 1), I can see it scroll down once and it msgbox's me with id = 2. I have a lot of code in the other triggers, so this may very well be causing it. I will go through and take that code out to determine whats going on.

    I didn't really want to allow deletes, but unfortunately the Before Insert trigger fires when a person types into a new record instead of when it tries inserting in to the DB. I was getting the case where I had start dated and end dated a person in the same day, and when I tried to change the start date on my new record, my validation kicked in telling me it was still active. I couldnt change the start date on the new record because it kept validating.

    Though in the 20 minutes since I posted, I thought about it some more, and I am going to do this instead:

    1) I dont want people editing "old" records, so if the me.newrecord = false, I am going to disable all the entry fields and not let them change it. I originally was doing this when the assignment_id was null, which worked pretty well. Unfortunately it assigns the assignment_id when a new record is started, instead of when a new record is actually inserted. This way people can't change the start dates of old records. Plus the dates are kind of meaningless - its more just to track whats active.

    2) Instead of automatically disabling the field once the person clicks off the record, I am going to only disable it if the me.newrecord = false. That way if it hasnt yet been inserted, they can edit it.

    3) If the me.newrecord = true, then I am going to allow deletes for that record only so they dont have to end date something on the same day.

    I dont know if any of that made sense. I am actually trying to replicate an existing Oracle form for a totally different purpose, and I'm finding out the subtle differences between when events fire.
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Well after ALL of this work, I just found out when you navigate from record to record on a subform, the records are automatically saved. Not good - its screwing up my logic.

    Is there any way to disable "auto-saving" of this record until a user explicitly commits their changes?

    That doesnt seem very smart to automatically save changes before a user gives their ok.
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    before considering adopting the insanity of my own unbound-form preference where you-the-coder must fill the form as well as saving any edits, a halfway house might be to replace your subform with the list it looks like in your post.

    lists are edit-proof (hence automatically save-proof)!

    maybe a double-click in the list copies the data from the list-entry to an unbound edit-section of the form or a floating unbound edit-form... it's only unbound copy data so it only goes into the table if you-the-coder writes code for a Save button that builds and runs UPDATE sql (and requeries the list of course).

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    Hmmmm that sounds interesting - Let me look into this and see... I really appreciate the advice.
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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