none
In Access 2010 I can delete a record but when I reopen the database the record is still there RRS feed

  • Question

  • My database only has two tables. One table has people's names and the other one has inventory items. For some reason if I delete a record It gives me the warning and deletes it. However, when I reopen the database the record is still there and only the person's name has been deleted from the record.  Things I have already tried:

    Made sure allow deletions was yes, tried changing the relationship between the tables with all possible combinations, compact and repair has no effect, loaded first backup I ever made that only has 4 records in it and it does the same thing.  The old backup has zero VBA code.  I read somewhere that this is a known issue and that unchecking enable layout view in Access options under current database corrects the problem.  I tried that and it does not fix the problem.  I have tried deleting the record using the record selector and hitting delete, I have tried deleting the record with VBA code embedded into a button on the form.  If I delete the record from the table itself, it stays deleted.  Another article I read said the issue was with my query not being an open query.  I have tried different queries.  I have tried combinations of all the things I have listed above over and over again.  I have added the directory where I keep the database to list of trusted locations and when I check I have all permissions as well.

    The only other thing I can think of would be to delete the record from the table using VBA code but when I try to do that I have no luck.  I am probably not using the correct syntax and am not even sure if that could be done.

    My tables are Assets and Household Member and there is a space between Household and Member.  Both tables have an Asset ID field, the relationship is based on those fields.  Assets is on the one side and household member is on the many side of the one to many relationship.  Join type is option 2.  Names are optional in the asset record.  As I said before this even happens in my first backup that has no VBA code in it at all so I don't think the problem is with the VBA code I have created in my latest version.  Every thing in the database works fine accept I can't delete a record.  It seems like such a basic function to delete a record. 

    There is only one form called Record Assets. 

    If anyone has any ideas I would greatly appreciate your input.  Thank you. 

    Wednesday, February 28, 2018 8:18 PM

All replies

  • Hi,

    I didn't see you mention it but are you using a split configuration? In other words, is the form on one file and the data (tables) in another? If so, are you able to open the data file and manually delete records there?

    Wednesday, February 28, 2018 8:23 PM
  • How are you deleting the records exactly?  Directly through the table, a form? 

    I think, based on the above, you're using a form, so what is the form''s record source?

    The easiest way to help you would be if you could supply us with a sample of your database to quickly review.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, February 28, 2018 8:26 PM
  • My form, data and tables are all under one database file.  If I pull up the assets table and delete a record, it deletes it permanently so when I reopen the database the record is gone.  It just will not let me delete it with record selector or with VBA code.  It will delete it but when I reopen the database the record is still there and the only thing deleted is the person's name which is stored under household member table.

    I am deleting through the form when I have the problem.

    My record source in the query builder has the following record source:

    SELECT Assets.[Item Name], Assets.Manufacturer, Assets.[Model Number], Assets.[Serial Number], Assets.[Asset Description], Assets.[Asset Image], Assets.Category, Assets.Location, Assets.[Date of Purchase], Assets.[Place of Purchase], Assets.[New or Used], Assets.[Purchase Price], Assets.[Receipt Image], [Household Member].[First Name], [Household Member].[Last Name], Assets.[Manufacturer's Website], [Household Member].[Household Member Name] FROM Assets LEFT JOIN [Household Member] ON Assets.[Asset ID] = [Household Member].[Asset ID];

    I am new at this. If you need other information let me know. I see another reply saying provide a sample. Is the information I provided enough or do you need more?


    Since my account on msdn is new, it will not let me attach images or screen shots until my account has been verified.
    • Edited by Nightslider Wednesday, February 28, 2018 9:03 PM
    Wednesday, February 28, 2018 8:41 PM
  • Hi,

    A LEFT JOIN sometimes result in a "read-only" data set. Are you trying to delete all the records from both of the tables? Also, does the Household Member table have a primary key field? If so, is this field included in the query for the form?

    Wednesday, February 28, 2018 8:57 PM
  • The Household Member table does have a primary key called Household Member ID, it is not part of the record source.

    When you say am I trying to delete the records from both of the tables, some of the records have the same household member name listed in the asset record.  I am trying to delete the asset itself from the inventory, and would prefer that the name on that record be deleted with it, but not deleted out of the other records with the same person's name.  So if John Smith is the name assigned for the tv, xbox, and couch, when I delete the xbox, I would still want the tv and couch to say John Smith.

    Wednesday, February 28, 2018 9:16 PM
  • My tables are Assets and Household Member and there is a space between Household and Member.  Both tables have an Asset ID field, the relationship is based on those fields.  Assets is on the one side and household member is on the many side of the one to many relationship. 

    Am I right in thinking that, when you attempt to delete a record on the form, the [Household Member] record is deleted?

    Generally, when you have a query that joins tables in a one-to-many relationship, and then you delete a record from it, only the record on the "many" side is deleted.  It sounds to me like this is what is happening, and if you think about it, it makes sense.  It's much less common to delete the "one" side record from such a relationship, because (even with cascading deletes) that implies that all the records on the many-side should be deleted (or at least have their Asset ID fields set to Null. effectively orphaning them). 

    From your description of the relationship, is each Asset owned by multiple Household Members?  If so, what exactly do you want to have happen when you delete from this form?  Do you want all the    associated [Household Members] records to be deleted?

    Or should the relationship go the other way, so any one Household Member may own multiple Assets?  If so, your relationship is backwards.

    You can even represent a relationship where any Asset may be held by multiple Household Members, and any Household Member may hold multiple Assets.  That's not uncommon, but a bit more complicated to set up.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, February 28, 2018 9:32 PM
  • Hi,

    In your original post, you said "Assets is on the one side and household member is on the many side of the one to many relationship.", which tells me an xbox can be assigned to John, Jack, and Jason at the same time. So, if you delete xbox, did you want to delete all three records?

    If you actually can only assign xbox to a singler household member, then your relationship may be different than what we are understanding from your description.

    Are you able to share your database or post some screenshots to help us get a clearer picture of the situation?

    Wednesday, February 28, 2018 9:38 PM
  • Yes, when I delete a record, the household member name is deleted from the record but not the asset.  If I look at the Household Member table the name that was associated with that record still appears in other records with the same Household Member name.  I used to have the relationship set up differently but when I did I had other problems.  The way I was trying to make it work was an asset could have a Household Member name assigned to it or not have a name assigned.  One Household member name could appear on multiple assets, I did not plan to have multiple names assigned to one asset. 

    So if I need to change my relationship, I am not sure what to change without affecting the other fields.

    Currently the relationship is setup as one to many.  Enforce Referential Integrity and Cascade Update Related Fields are checked, and the join type is option 2 - Include all records from Assets and only those records from Household Member where the joined fields are equal.

    Wednesday, February 28, 2018 9:47 PM
  • In your original post, you said "Assets is on the one side and household member is on the many side of the one to many relationship.", which tells me an xbox can be assigned to John, Jack, and Jason at the same time.

    Don't you think Jane, Jeanne, and Juliet may want one, too?  Sexist!


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, February 28, 2018 9:50 PM
  • Hi,

    When you say "when I delete a record," which table does that record belong? In other words, which table's record are you trying to delete? Can you post some sample data, so we better understand your table relationship? Thanks.

    Wednesday, February 28, 2018 9:52 PM
  • Yes, when I delete a record, the household member name is deleted from the record but not the asset.  If I look at the Household Member table the name that was associated with that record still appears in other records with the same Household Member name.  I used to have the relationship set up differently but when I did I had other problems.  The way I was trying to make it work was an asset could have a Household Member name assigned to it or not have a name assigned.  One Household member name could appear on multiple assets, I did not plan to have multiple names assigned to one asset. 

    This a one-to-many relationship between [Household Members] and [Assets], and the relationship you say you set up was the other way around.  That will give you nothing but problems -- the relationship *must* reflect reality, you can't change reality to reflect the relationship you set up.

    You said, "I used to have the relationship set up differently but when I did I had other problems."  Without knowing what those problems were, we can't address them, but it seems clear that you have to correct the relationship first, so that you enforce the correctness of database operations, and then we can figure out what's behind the problems you had before.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, February 28, 2018 9:58 PM
  • The way my form is setup, there is only one first name and last name field.  Once I enter the name and save the record, a calculated field displays the full name and the data entry fields are not visible.  When I try to paste in screen shot images the site will not let me submit my post.  Funny, no there is a Sally Smith in the database to.  It would be fine with me if I could change it to accept multiple names for each asset but I am not sure how.  I am new at this, learning about Access.  This may not be as easy to fix as I would have hoped.  I am just frustrated because I have worked very hard on this database and can't even delete a stupid record.
    Wednesday, February 28, 2018 10:01 PM
  • When I delete the record, it belongs to the Assets table. When you say post sample data I will see what I can do. For some reason this website will not let me paste a screen shot in and hit submit. It says my account has to be verified first.
    Wednesday, February 28, 2018 10:03 PM
  • Hi,

    If you want to share your database, you can also upload it to a file sharing service/site like OneDrive or Dropbox and post the download link here. We'll be able to help you better if we can see what you're seeing.

    Cheers!

    Wednesday, February 28, 2018 10:09 PM
  • If Household Members are primary, in a one-to-many relationship with Assets, then you could easily set it up as a main form/subform arrangement like this:

    If Assets were in a one-to-many relationship with Household Members instead, you could have the same setup, but with Assets as the main form, and household members as the subform.

    The main form/subform arrangement is the most common way of presenting a one-to-many relationship on a form.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, February 28, 2018 10:19 PM
  • Are you sure that the relationship type is not many-to-many, i.e. each asset might be allocated to one or more household member, and each household member might have access to one or more assets?  If so, you need three tables, in broad outline as below:

    Assets
    ....AssetID  (PK)
    ....Asset

    HouseholdMembers
    ....HouseholdMemberID  (PK)
    ....FirstName
    ....LastName

    And to model the relationship type:

    Allocations
    ....AssetID  (FK)
    ....HouseholdMemberID  (FK)

    The last table is 'all key', i.e. its primary key is a composite one of the two foreign keys.

    A form/subform interface, as described by Dirk, would normally be used, but the subform would be now be based on the Allocations table.  The parent form would be based on either the HouseholdMembers or Assets table, depending on whether you wish to see assets per household member, or household members per asset.  You'll find an example of a basic binary many-to-many relationship type like this, and how to represent it in forms/subforms from both directions in ParentActivities.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    With an interface like this you can delete the allocation of an asset to a member by deleting the relevant row in the subform.  If you wish to be able delete an asset from the database, along with all allocations of assets to it, and/or to delete a household member and all of their allocations, then you should enforce 'cascade deletes' in the two relationships.

    Ken Sheridan, Stafford, England

    Thursday, March 1, 2018 12:12 AM
  • I have new information about the problem I was having.  When I had the relationship set as one to many with assets on the one side and household member on the many side, the database would work but would not let me delete records. I changed the primary key in the household member table to asset id which matches the primary key in the asset id table. When I did that, Access changed the relationship type to one to one.

    Now when I delete a record, it deletes correctly.  Perhaps this is because each asset record will only accept one household member name and each household member name record only has one asset id? Also I have a new question.  When I designed the database originally I did not think about assigning multiple names to one asset.  Now I would like to change the database to support that functionality.  However, when I change the relationship back to a one to many with one on the assets side and the many on the household member side, the problem with deleting records comes back.  I have also tried changing the relationship to many to many but I still can't delete records. If there is any other details I can post to help describe the problem let me know.  I would appreciate anyone's ideas or input.  Thank you.

    Friday, March 23, 2018 4:04 PM
  • However, when I change the relationship back to a one to many with one on the assets side and the many on the household member side, the problem with deleting records comes back. 

    Hi Nightslider,

    What happens when in a one-to-many relation you first delete the "many" records, and then the "one" record?

    Imb.

    Friday, March 23, 2018 4:30 PM
  • My database has two tables.  When I select a record with the record selector and hit delete, the record seems to be deleted but if I reopen the database the record is back.  It deletes the name on the record but not the asset record.  If I change the relationship type to one to one, it deletes fine.  But I want to be able to use a one to many relationship so I can assign multiple names to one asset but still be able to delete records correctly.
    Friday, March 23, 2018 4:45 PM
  • If each household member can have multiple assets, but each asset can be assigned to more than one household member the relationship type should be one-to-many, so the Assets table should have a HouseholdMemberID foreign key of long integer data type referencing the autonumber HouseholdMemberID primary key of HouseholdMembers.

    Assuming that referential integrity is enforced, you will be able to delete rows from Assets, but only rows from HouseholdMembers if no assets have been assigned to the member, unless you have also enforced cascade deletes, in which case, deleting a row from HouseholdMembers  will also delete all asses assigned to the member in question.

    If an Asset can be assigned to multiple members, but each member can have only one asset assigned to them, then the reverse would be the case, the HouseholdMembers table having an AssetID foreign key column.  In this case the reverse of the above would apply as regards deletions.

    When you say 'It deletes the name on the record but not the asset record', that suggests that you are in fact setting the value of the non-key column to Null, rather than deleting the row from the table.

    What do you mean by 'When I designed the database originally I did not think about assigning multiple names to one asset.  Now I would like to change the database to support that functionality'?

    You talk about switching a relationship from many-to-many to one-to-many, but there is no such thing as a many-to-many relationship object, only a many-to-many relationship type, which requires three tables, as I described earlier.

    You are clearly having problems grasping how relationship types work.  I think it would help if you gave us a real world example of *what* you are trying to achieve rather than how you are trying to achieve it in database terms.  You might also like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file explains, amongst other things, the different relationship types, and how they are modelled, with some simple examples.

    Ken Sheridan, Stafford, England


    Friday, March 23, 2018 5:03 PM