Results 1 to 11 of 11
  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Deleting records out of the query

    I have a table that is a record of parts sent for "out of plant services" called [tlbSilverPlatePartCache]. This table is related to my main table by the part number. I created another table with the "pricing for the out of plant services" and related it to the main table part number field. I created a query to bring all this information together. Then i built my form off this query to fill out the [tblSilverPlatePartCache]. My problem is if someone wants to delete the record from the query it deletes all records from the [tblSilverPlatePartCache] and the [tblMainTable]. If i go directly to the [tblPlatePartCache] and delete the record there it does fine, moreover if I the relating tables out my query it wont cascade delete. None of my relationships are set to cascade update or delete so I'm stumped. I have attached screen shots of the queries, I have this scenario a couple of times in my database, because of different companies pricing structures(one is on a part to part basis and the other company prices by size) i just copied the first set of queries, tables, and forms, then made some modifications and off i go, except for this problem. If i need to post anymore screen shots let me know and if i explained this wrong i apologize but my brain hurts at this point.
    Attached Thumbnails Attached Thumbnails ThisWorks.jpg   DoesntWork.jpg  

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Queries do not contain any data, so you can't delete records from a query without deleting them from the underlying table(s).

    All you can do is exclude those records with criteria.
    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

  3. #3
    Join Date
    May 2009
    Posts
    104
    Yes i understand that, but i have a forms built off these queries, when use the form to delete a record (or delete records from the query) records get deleted from both tables [tblSilverPlatePartCache] and [tblMainTable] where as the one i labeled "ThisWorks" just deletes records from the [tblCoatedPartCache] table, which is what i want. I cant see the difference in the two that would make one work and the other not. The forms are identical so i don't think this is the problem.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If you delete a record from the QUERY, does it delete from both tables?

    Do you have a 1:1 relationship between any tables?

    TBH, I am at a bit of a loss here... I didn't think that it was possible to delete data from TWO tables with one delete action from a query. But then again, I almost NEVER use 1:1 relationships, which is why I suspect it.

    What is in your "main" table (bad bad name!).
    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

  5. #5
    Join Date
    May 2009
    Posts
    104
    If you delete a record from the QUERY, does it delete from both tables?

    Yes

    Do you have a 1:1 relationship between any tables?

    Yes, the two tables that are in question, tblMainTable and tblSilverPlatePartCache have a one to one relationship.

    And yes i know the tblMainTable is a less than perfect name but it actually is the main data table.

  6. #6
    Join Date
    May 2009
    Posts
    104
    I think i know what the problem is, i used the query to bring in information like standard cost for a particular part but i only use that information to populate a calculated field in the form. The screen shots show this i think, so when i delete the record from the query it deletes all the records pertaining to the information gathered by the query which is, the records from the tblSilverPlatePartCache and information in the tblMainTable that i included. The only way to keep this from happening is to exclude that information. I'm almost certain that this is just a case of my inexperience. I know i could get help here but i'm not sure what to ask for so it may be just something i have to figure out on my own. My database structure may be severely flawed.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    How are you carrying out the deletion? If you're using the native Access "Delete Record" button, it will delete the record from the form's recordset that is currently displayed.

    If you have a form that displays the results from a multi-table query and just want to delete records from one of those tables, you'll need to create your own "Delete" button, and write the relevant VBA/SQL code.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    May 2009
    Posts
    104
    I was using the native delete record button, i think that i should do as you suggest. In the end what i wanted to do is not possible the way i had it set up and was doomed to fail no matter what. I will make a delete button that will delete from the table i want, as i looked more closely i can see where i was in error.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Definitely sounds like your table structure is flawed.

    So what is a "main data table"?? What's in it? Mains? ^^

    I taught Access for 13 years and one of the first lessons is never to have non-descript table names!!
    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

  10. #10
    Join Date
    May 2009
    Posts
    104
    Definitions of "main" on the Web:

    * chief(a): most important element; "the chief aim of living"; "the main doors were of solid glass".


    This is the "Main Table" that all other tables reference to. This table has every order that our company has produced over the last 3 years. I use this to reference a job or track data as far as failures (bad parts produced), cycle times through the manufacturing process. I'm not quit sure my table structure is flawed as much as my use of queries, in this case anyway, but as i don't have 13 years of experience doing this, its more like 6 or 7 months, i yield to the more experienced.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by dbshaft View Post
    I think i know what the problem is, i used the query to bring in information like standard cost for a particular part but i only use that information to populate a calculated field in the form. The screen shots show this i think, so when i delete the record from the query it deletes all the records pertaining to the information gathered by the query which is, the records from the tblSilverPlatePartCache and information in the tblMainTable that i included. The only way to keep this from happening is to exclude that information. I'm almost certain that this is just a case of my inexperience. I know i could get help here but i'm not sure what to ask for so it may be just something i have to figure out on my own. My database structure may be severely flawed.
    To me (and from looking at your relationship schematic), this sounds like you have the relationships setup wrong and that you have the subform setup incorrectly. You need to setup relationships as a 1 to many where the 1 side is the main table and the many side is your relational table. DON'T put in calculations into the recordsource query of your subform but instead, keep your subform ONLY based on the relational table itself (and ONLY the relational table alone) and perform any calculations TO unbound fields on the subform (ie. set the sourceobject of a field on the subform with your calculation - ie....=[MyFieldName]*[OtherFieldName] BUT ONLY in an unbound field and NOT in the recordsource itself!!)

    THEN, if you want to delete a record in the subform, you can easily do it. Otherwise if you put your calculation into the recordsource of the subform, you won't be able to delete the record in the subform.

    Hope that make sense. If you do this correctly, you'll then receive a message box (when you delete the main record on the form), that it will cascade delete other records related to that main form's relational join field.

    - Note: Right-Click on the JOINing line in your relational schematic and then select the type of join (ie. 1 to 1, 1 to many, etc..) where the 1 side is your main table and the many side is your relational table.

    Also, what I often do on subform's is show the navigation bars. If I can't delete/add a record using the navigational bars, I then know that the recordset is not updatable/deletable.
    Last edited by pkstormy; 10-31-09 at 01:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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