Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2008
    Posts
    21

    Unanswered: Delete Query - Two questions on how to automate

    So, I've only posted one other time. Assuming you haven't read my other post, here's some background:

    I'm doing a recipe database in access where you store the recipes and ingredients and then weekly create your menu. You can then print your weekly menu as well as a shopping list based on the menu. I'm pretty novice with Access and am doing most of it without VB code or SQL. (I did manage to do a union query with SQL, though.)

    I'm using the switchboard manager function and would like a way to have a function to delete out the prior week's menu. It's something you'd just write over each week. I've created the delete query for it, but I don't know how to make it easy for another user. I was hoping it would just be a switchboard menu choice, but it's not. Any thoughts on how best to do that?

    The other question I had was around if someone wanted to delete a recipe. I wanted a way where you could choose a recipe name from the list and it would delete (the recipes are set up with the main information as well as a subform with the ingredients, so I'd need that recipe and its related ingredients deleted from 2 tables). Can someone help with some guidance on how to get started there?

    I did an attempt at searching for these things. If you know it's already been covered, please just point me in the right direction. Please also let me know if you need more info on how my DB is set up.

    Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's hard to answer this because you are going to need SQL and VBA to do it.

    I basically never use the switchboard manager thing either, so that kinda makes it difficult... at least for me. I always make my own "switchboard".

    One way would be to make a custom dialog box with a combo box on it to select a recipe/menu and a delete button that deletes that recipe/menu after a confirmation. The button would need to call VBA or a macro that runs the necessary delete queries.

    Either that or make it so the act of creating a new menu automatically delete what needs to be deleted.

    Not sure how much this will help :/

    Cheers

    ST
    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
    Mar 2008
    Posts
    21
    ST - That is helpful. Anything is helpful at this point.

    So, if I'm really determined to make this thing work well, first of all would I need BOTH VBA and SQL? Does anyone know of a decent online tutorial in whichever one I need or both to get me started?

    Also, I assume that you'd need to know VBA and/or SQL to create your own switchboard, right?

    Thanks - sorry if this is annoyingly elementary.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by webee33
    ST - That is helpful. Anything is helpful at this point.
    Good

    Quote Originally Posted by webee33
    So, if I'm really determined to make this thing work well, first of all would I need BOTH VBA and SQL?
    Well, yes and no. You could achieve it with Queries and Macros too, but it would be more "clunky" and less flexible. For example, a form with buttons which then run macros that either runs a delete query or open forms. This doesn't require any VBA and very little SQL.

    However, it should be noted that every query is essentially made up of SQL. An SQL statement saved with a name is the definition of an Access Query.

    For example, you could write a query in Access that shows the record(s) that you want to delete and then just turn it into a delete query by choosing Query - Delete Query and saving it. You can then open that query with a macro. You can run the macro from a Command Button on a form.

    Quote Originally Posted by webee33
    Does anyone know of a decent online tutorial in whichever one I need or both to get me started?
    No I. I am self-taught from reading the original Access user manuals that came with Access 1.1 and 2.0

    Quote Originally Posted by webee33
    Thanks - sorry if this is annoyingly elementary.
    Not a problem!
    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
    Sep 2007
    Location
    Global Village
    Posts
    185
    Hi,
    it is not necessary to execute your delete query by using a button in switchboard, simply add a button to your form and set its caption for example "Delete Old Data" and set the following code under its ON CLICK event:
    Lets say your button name is Command1
    PHP Code:
    Private Sub Private Sub Command1_Click()
    DoCmd.SetWarnings False
    DoCmd
    .OpenQuery "your delete query name goes here"
    DoCmd.SetWarnings True
    End Sub 
    for the last question there is several ways but the simplest way is to put a button in your form and set the following code under its ON CLICK event:
    PHP Code:
    If (Not Form.NewRecordThen
            
    If MsgBox("Please Confirm Current Record to be Deleted "vbInformation vbYesNo) = vbYes Then
                DoCmd
    .SetWarnings False
                DoCmd
    .RunCommand acCmdDeleteRecord
                DoCmd
    .SetWarnings True
                Me
    .Form.Recalc
            End 
    If
    End if 
    I have a good e-book and I can e-mail it for you if you want.
    Cheers

  6. #6
    Join Date
    Mar 2008
    Posts
    21
    Aran1,

    Thanks so much for the code. On the second one, I'm having trouble getting it to work. The one thing I did differently is that I made it a check box instead of a button - could that be what's causing the problem?

    I've come up with another conundrum that is related but probably different enough to do a new post. So, feel free to respond here or there.

    Oh - and I'd LOVE the ebook! I'll check my profile and make sure I'm email-able or whatever.

  7. #7
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    webee33,
    I think you mean selecting multiple recipes for deletion by using check box, in this case also you need a button to execute the deletion because if you put the code under after update event of check box or on click then as soon as you check each box it will be deleted and no time to check other boxes.
    if you use check box for multi deletion then you should change your delete query to filter only checked boxes.

  8. #8
    Join Date
    Mar 2008
    Posts
    21
    Okay - that makes sense. I was thinking my remaining questions would make sense in a new thread, but on second thought they're all related to the above, so I'll expound a bit.

    For the first one, where I just run the delete query with the button using the code you gave me, it works, but I have to close and re-open the form before the records are gone. Is there a way to avoid having to do that? Some sort of refresh option or something?

    I'm actually trying to do two things with your code on the second one, which is probably what got me on the wrong track. My original question was how to delete a recipe including the ingredients list which is in a sub-form and a separate table. At first I wasn't sure which form to put the button on, but I'm assuming now I should put the button on the recipe entry form, right? I'll try that.

    My third conundrum is that based on the recipes for the week and any other grocery items that I've checked as needing, the database then generates a shopping list. Because it drives off of the recipes I'm making for the week, it includes all of the ingredients in that recipe. Well, I may have some of those ingredients on hand, so I was trying to create a form off of the query that combines all of the shopping list items that had a check box for "Already on Hand". Then, I could generate another query to exclude those records and run the shopping list report off of that. The query I have that joins the shopping lists is a SQL union query. I created a form off of that and added the checkbox (which is tied to the ingredients base table), but it won't let me check the check box or write to the table. I tried to do a 'Make Table' query off of the shopping list union query, but that's erroring out. Is there a way to do this that I'm not thinking of?

    Thanks again for all the help!!!

  9. #9
    Join Date
    Mar 2008
    Posts
    21
    Quote Originally Posted by webee33
    I'm actually trying to do two things with your code on the second one, which is probably what got me on the wrong track. My original question was how to delete a recipe including the ingredients list which is in a sub-form and a separate table. At first I wasn't sure which form to put the button on, but I'm assuming now I should put the button on the recipe entry form, right? I'll try that.
    Update - I tried that but am still getting the error that there are records in the sub-form that have to be deleted before I can delete the main recipe record. Is there a way to add to the code to call a delete query based on the recipe ID of the record I'm on? Does that make sense?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how have you defined the relationship between recipie and the child records (ingredients etc..)

    if you define the relationship using RI and specify delete cascade then the SQL engine handles the delete for you. be warned however that delete cascade isn't always a 'good' thing.

    intrinsically there is nothing to stop you placing some code in the appropriate event in the form.. I'm not to sure what event that may be.. you may need to do some exploring using a series of message boxes telling you inwaht sequence the events fire. the code should issue an SQL delete sentence using something like execute sql

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would have thought he didn't have Cascade Delete on since if he did, the problem wouldn't exist. And therein lies my recommendation. Turn on Cascade Delete Related Records for the relationship between the Recipe and Ingredients tables. Doing so will mean that Ingredients will be automatically deleted when you delete a recipe.

    Quote Originally Posted by webee33
    Is there a way to add to the code to call a delete query based on the recipe ID of the record I'm on? Does that make sense?
    Yes and yes! First consider the Delete Cascade option though.

    be warned however that delete cascade isn't always a 'good' thing.
    I found that out the hard way ^^
    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

  12. #12
    Join Date
    Mar 2008
    Posts
    21
    Excellent - thanks. The cascading delete thing works. I know it's risky, but the underlying database exists ONLY to hold the recipes by ingredient. The entire ingredient list is in another table, so I'm hoping I'll be safe there.

    So, my other two questions remain, then. Any ideas on these:

    Quote Originally Posted by webee33
    For the first one, where I just run the delete query with the button using the code you gave me, it works, but I have to close and re-open the form before the records are gone. Is there a way to avoid having to do that? Some sort of refresh option or something?
    Quote Originally Posted by webee33
    My third conundrum is that based on the recipes for the week and any other grocery items that I've checked as needing, the database then generates a shopping list. Because it drives off of the recipes I'm making for the week, it includes all of the ingredients in that recipe. Well, I may have some of those ingredients on hand, so I was trying to create a form off of the query that combines all of the shopping list items and adding a check box for "Already on Hand". Then, I could generate another query to exclude those records and run the shopping list report off of that. The query I have that joins the shopping lists is a SQL union query. I created a form off of that and added the checkbox (which is tied to the ingredients base table), but it won't let me check the check box or write to the table. I tried to do a 'Make Table' query off of the shopping list union query, but that's erroring out. Is there a way to do this that I'm not thinking of?

    Thanks as always!

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, you can run a requery macro or a requery method in VBA to refresh the records.

    As for the checking off thing, no you can't write back to a union query. You should be able to make a table from it though,... not sure why you can't. In any case, you will need a yes/no field in a table to store your selection of whether the item is on hand or not in order to base a query/report from it, so I don't see an easy way to achieve the goal with the implied table structure.

    Can you post a screenshot of your table design (JPG)?
    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
    Sep 2007
    Location
    Global Village
    Posts
    185
    Copy your form and related tables in a new db and share it in order to find out more abut what you want to do and solve the problem faster

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    add a refresh to the existing code
    its soemthing like me.recordsource.refresh


    as to the ingredients on habd

    there is nothing stopping you jong you exisitng query to the stock on hand, and subtracting stock on hand from quantity rrequired, and excluding and excluding any rows in the shopping list which have a zero or negative balance. If after subtracting he stockon hand from the quantity required then you know you have at least enough of that item in already.

    HTH

Posting Permissions

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