Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Unanswered: Can't Delete Record in Access 2007 SubForm

    We upgraded to Access 2007. After the upgrade, we can no longer delete records in subforms. It appears to allow the delete, but when the form is reopened, the data "returns". Watching the table, the data is never actually deleted. We can delete data directly in the table, though. The forms are in a separate database linked to the tables. I converted both the front end and the back end to the new accdb format but the problem remains. Any help would be greatly appreciated. Thanks.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    How is the record being deleted in the subform? Is there a delete button on the form with code behind it? If so, try removing that button and recreating it using the wizard (ie. using the wizard select: Record Operations -> Delete Record.)
    I'm guessing that the code behind the delete button is something like this....
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Or is a Delete query executed when the button is clicked or is the record deleted by right-clicking on the Record Selector on the left side and then selecting delete?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Posts
    8
    The record is being deleted using the delete key or delete command from the ribbon. The right-click option is visible but disabled for unknown reason. Also the delete record command is disabled in the ribbon for unknown reason.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is the subform properties set to Allow Deletions = Yes and Allow Edits = Yes? Also is there any code in the subform which may set this properties to No or false? Or is there any code in the Mainform which sets this property for the subform to false?
    Last edited by pkstormy; 08-08-07 at 13:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also, look at the design view of the main form, click on the subform and see if the Locked property is set to Yes and Enable is set to No. Look for any code which may be setting these properties to Yes, No or False. The subform should have Locked = No and Enabled = Yes.

    If it still does not allow deleting, you can try removing the subform out of the main form and adding it back into the main form.
    Last edited by pkstormy; 08-08-07 at 13:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Aug 2007
    Posts
    8
    Paul,

    I've verified that all of the properties are set as you suggested and that no code is changing these. I may have to rebuild the forms like you suggested with the subform in the main form. The puzzling thing about this is that they worked fine in Access 2000, 2002 and 2003. Nothing changed but they don't work in 2007. And converting them from mdb to accdb did not fix the problem. There may be a better way to build this form, but something seems funky with 2007.

    Thanks.

    Pat

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You might consider calling Microsoft support. You should have 2 free support calls when you bought 2007. I'm assuming it is their fault, and if it is, they will not charge you for the support call. That is, you will keep your 2 free support calls on their books.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Pat1,

    This peaks my interest that you're having this problem specifically with Access 2007 and didn't have this problem when it was a 2000/2002/2003 format. It's got to be something simple. I'm guessing you're not having any other problems such as editing the record in the subform, adding new records, etc...it just won't actually delete the record even though it looks like it's deleted it on the subform. What's strange is that it looks like it's deleting the record but doesn't actually delete the record and you're able to delete the record manually in the table. What's perplexing about this problem is that the "cut"/delete command is available. A couple of questions:

    1. Is this only on certain records or on any/all records?
    2. Are there possibly any relationships on this table which might prevent deleting certain records? I could understand if it was only on certain records which might have a related record in another table preventing the delete for certain records. I've seen this kind of thing before where it looked like it was deleting a record on the form but didn't delete it because of a related record in another table.
    3. Is the default view property of the subform a Continuous form, a Single form, or some other type?
    3. Are the relationships for the tables your typical relationships where you have a Primary key -> Foreign key with the main table and relational table?
    4. If you go to the design view of the subform, view the properties, click on the ... (3 dots) for the Record Source to view the query (if it is a query otherwise it will ask if you if you want to build a query), and then run the query, supplying a parameter for the query such as any record ID, are you able to then delete that record in the query? If you can delete the record in the Record Source query, then it's an issue with the form. If you can't delete the record in the query, then it's a problem with the query, the table, or a related record. Check to make sure the query properties are not set to unique records or unique values. This though wouldn't let you select the "cut" when right-clicking on the ribbon/record selector or let you edit any data in the subform.
    5. Can you try the following:
    Create a button on the subform.
    In the button's onClick event enter the following:

    me.requery

    Then once you "cut" the record and it dissappears from the subform, click the button which has the me.requery command and see if the record re-appears.

    6. Or put a button on the main form and in the onClick event enter the following:

    me.MySubformName.requery

    And then delete the record in the subform and click on the button.

    7. Is there any code in the subform (such as in the On Delete event) or any other events which seem of interest? Is there any code which possibly turns off the warnings such as: docmd.setwarnings False?

    8. Looking at the design view of the main form, if you click on the subform, is there something in the Link Child Fields and Link Master Fields values?

    Please let me know if this happens on other records or only on certain records and if you can or can't edit any data in the subform. I'm guessing that you can edit data in the subform but I'd be interested in knowing more information about this problem.

    Also, if you convert the mdb file back to an older version such as an Access 2002 or 2000 format, does it then do the actual deletion of the record?

    I'd be very interested in seeing a converted to 2002/2000 mdb of this database if you're able to zip it and post it with a few records, especially the records it mimicks deleting if it's only certain records.

    As another possible thing to try, you can try creating a new blank Access 2007 mdb and import all the tables, queries, forms, reports, etc. Or you can delete the linked tables in the front-end and try re-linking the tables into the front-end again. I'd also be curious if you imported all the tables into the front-end verses having them as linked tables if that would make a difference.

    Again, it's probably something simple that Access 2000/2002/2003 let you get away with doing whereas Access 2007 has fixed it with less forgiviness on a problem.
    Last edited by pkstormy; 08-08-07 at 21:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Aug 2007
    Posts
    8

    Sample Databases

    I've included the FE and BE and combined databases in both the mdb and accdb formats. I've deleted all the data except for a test case. Open the From-the-Ground-Up form. The subforms are in datasheet view. If you try to delete a record in the FGU subform (FrmGdUp and FGUDate fields visible), some of the delete options are unavailable and it doesn't ask for a confirmation on deleting. Close and reopen the form and the data has not been removed from the underlying table. This happens with all records. You can add or edit a record without a problem. This only occurs in 2007. Open the mdb in 2000, 2002, or 2003 and no problem.

    I really appreciate you taking a look at it.
    Attached Files Attached Files

  10. #10
    Join Date
    Aug 2008
    Posts
    2
    Recently, while upgrading a database to Access 2007, I also encountered the problem. After trying to solve it for a while, I accidentally came upon the following solution:

    The solution simply (and strangely) is to change the value of the hidden property "AllowDesignChanges" in every broken subform to False (changing them in every form is also a good idea, by the way). Because the property is a hidden property, you have to change it using VBA (run the following VBA code from the immediate pane).

    Code:
    For Each frm in CurrentProject.AllForms: DoCmd.OpenForm frm.Name, acDesign: Forms( frm.Name ).AllowDesignChanges = False: DoCmd.Close acForm, frm.Name, acSaveYes: Next frm
    Hope this helps, and sorry for bringing back up such an old post (this post is #1 in Google for "Access 2007" cannot delete record in datasheet subform).

  11. #11
    Join Date
    Aug 2007
    Posts
    8
    dit890le,

    No need to apologize for bringing this up again.

    About a year ago I took it up with Microsoft Support and they confirmed it was a problem with Access 2007. SP1 did not include a fix for this. I ended up with a less than elegant work around of not using a subform.

    Thanks for the tip. Unfortunately, I'm not having any success with that either.

    Pat

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Pat, as I read this response of yours, an idea went through my head. Could you use a SQL delete of the record you want to delete? By using the OnDelete event, which I will assume actually gets fired when you try to delete from the subform, you could then, using SQL, actually delete the record from the OnDelete code. If you want to persue this, just let me know and I would be happy to work through this with you. Because this seems to be a situation where it does not always fail, I would rather not try it myself. I'm also not using 2007 myself yet.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by Pat1
    ...something seems funky with 2007...
    Couldn't agree more.... however I think this is a very kind assessment of the situation... lots of issues seem to appear when converting databases to 2007.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Aug 2008
    Posts
    2
    Thanks for the tip. Unfortunately, I'm not having any success with that either.
    OK. After examining your database more closely, it appears that your database is actually affected by two different (yet closely related) Access 2007 bugs:

    • Bug #1 (my post above): The "Delete Record" command will always be unavailable (grayed out) if a form is viewed in Datasheet view and its "AllowDesignChanges" property is set to True.

      Workaround: Set the property "AllowDesignChanges" in every broken form to False using VBA.

    • Bug #2: If any "form containment chain" contains the following pattern:
      [... contains] "frmA" contains "frmB" contains "frmC" [contains ...]
      ... where form A is viewed in Single Form, Datasheet, or Layout view, and form B in Datasheet view, attempts to delete records from form C, D, E, etc. will appear to be successful (no confirmation dialog, and rows removed from the form), but will not actually delete the records from the form's underlying table (rows return if the form is reopened).

      Workaround: The workaround suggested by GolferGuy above seems promising.

  15. #15
    Join Date
    Aug 2007
    Posts
    8
    Vic,

    I went with your suggestion and ran a SQL string in the OnDelete event. It deleted the record. Thanks.

    Another problem shows up, though. If I remain in the subform after deleting to continue working entering new records, the records only show in the form and are not written back to the underlying table. I've tried refreshing and requerying the form, but so far closing and reopening the form is the only way I'm able to continue working.

    Pat

Posting Permissions

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