1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2010 - Delete Record If

Discussion in 'Business Applications' started by md91, Aug 25, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. md91

    md91 Thread Starter

    Joined:
    May 30, 2013
    Messages:
    51
    I have a hire/return form/subform. The form is frmHire, the subform is frmHireSub.
    Multiple items can be hired under the same HireID, and I can also return ALL items at once.

    But, different items have different hire periods (over night and weekly) so not all items are due back at once.

    To be able to see what items are currently on hire, when "Hire" is clicked the records are appended to another table "tblOnHire" and when all items are returned they are deleted from "tblOnHire".

    All hire details are retained permanently in tblHire and tblHireDetails. tblHireDetails lists each seperate item that's been hired out and has a yes/no field titled "Returned". There is a yes/no field on frmHireSub which uses the "Returned" field as it's control source.

    I tried using a query to delete the record(s) that had "Return" checked on both frmHireSub and tblHireDetails from tblOnHire but neither worked, they returned an error asking for the table to delete from.

    How do I delete record(s) from one table, based on check boxes on a form that are based on a table?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,830
    Personally I wouldn't delete them from the tblOnHire Table at all I would also mark them as returned as it would provide a good record of how often tools were hired etc.
    But to answer your question, create a query of the tblHireDetails table with the Criteria set for Returned = -1.
    then create a query for the tblOnHire table listing the Key field and add the first query to it linked via the key field.
    This should limit the list to only those that have been returned. Set that query to a delete query.
    That should work.
     
  3. md91

    md91 Thread Starter

    Joined:
    May 30, 2013
    Messages:
    51
    tblOnHire is used solely to show which items are currently on hire, tblHIre and tblHireDetails keep a record of all hires so that you can see how many times something has been hired over certain periods and wether it is popular or not.

    I will try your solution in the morning and let you know how I go, many thanks for all your help with my many troubles.

    Mark
     
  4. md91

    md91 Thread Starter

    Joined:
    May 30, 2013
    Messages:
    51
    I couldn't get your idea to work, maybe I am misunderstanding you. It wouldn't let me reference the first query, nor the subform with the details I want to delete based upon.

    What I have got is this, delete from tblOnHire where tblHireDetails!Returned = -1.
    Viewing the results of the query come back with (basically) what I am after, but when I try to run the query I get "Could not delete from specified tables.", the help tells me that it must be read-only, which it's not?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,830
    Can you post or email me a copy of the database?
    I can always get VBA to delete the records if the query won't work.
     
  6. md91

    md91 Thread Starter

    Joined:
    May 30, 2013
    Messages:
    51
    I got the delete query working, I used delete from tblOnHire.* where tblHireDetails.Returned = -1 with "Unique Records" set to 'Yes'
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,830
    Good, well done.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1106842

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice