Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Command (Access 2000)

    Delete Command does not work if preceded by un update function.

    My function for deleting an object does NOT work properly if it consists of 2 parts, first update and then delete. On the other side, the update function
    works excellent if used alone . The delete comamnd also works if used alone. .But in combination of the two i have a problem.


    I have a very big and difficult problem with deleting a record.It is very complicated and i will try to make it as short as possible.
    I have a database for issuing orders.On the subform i choose different products For a given order i may choose several products.Now i may wanrt to delete

    only one product, that is to delete only one row and not all the rows.
    In this case, before deleting the product, i must cary out an update query in order to return the product back to the warehosue.

    So in short my function consists of 2 parts:

    Public Function MyFunction
    DoCmd.RunSQL strSQLReturnTheQuantity ( this is an update query that increases the quantity (
    RunCommand acCmdDeleteRecord
    End Function

    First the function updates the quantity, i.e. returns the cartons back to the warehouse,however the product does not disappear from the row
    and stays there. After that the function deletes the record. If i remove the update query for the function, and if i leave only the
    delete command, then the product disappears from the subform.

    I did try with the command DoCmd.GoToControl "Cartons" but the error says object does not exist.

    So in short, if my function consists only of the command for deleting, then everything is OK, but if before the delete command i have the update query,
    then the product does not disappear from the row and stays there.(the update query is ok in both cases)

    I will be very grateful if somebody may wish to help me.Perhaps i should answer some aditional questions.This problem is so important to me.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Delete Command (Access 2000)

    I'm not sure I understand your table structure. I suspect you have a table for orders that the main form is bound to, and you have a table for line-items that has a one-to-many relationship with the orders table. A person enters a product and quantity in the subform, and that subtracts the quantity from a WareHouse Inventory table that tracks the total quantity on hand for each product. When a person makes a mistake and selects the wrong product, then you have to reverse the quantity reduction in the WareHouse Inventory table for that product, and then delete the LineItem record from the order. If that scenario is correct, it seems it should work. However it may not be an optimal design. You might want to go to a design where you track uncommitted quantity and committed-but-not-shipped quantity. You might also want to use a deleted flag in the LineItem table to avoid the complications of actually deleting a record, and not display records where the deleted flag is set. You will need to Requery the subform in any event so that you see the changes in LineItems. Hope this makes some sense - if I've got the table structure all wrong, please post the actual structure.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Command (Access 2000)

    Thank you very much indeed for your kindness and suggestion to help.I have found out that the reason for the problem with the
    delete command is my effort to simplify my update command.Because with my previous code all worked well. First let me explain what this improved update

    command is.I have different branches, e.g. branch0,branch1,branch2,branch3 etc,tilll branch 12.Then for each office i used the following query:

    Example for Office Number 2,that has a field branch1:

    MySubform![branch1].Value = MySubform![branch1].Value + MySubform![cartons].Value
    RunCommand acCmdDeleteRecord

    In this old case everythink is ok, and the delete command works properly.


    After that i have received a very clever suggestion to simplify my coding. This siggestion was made also by this Forum, ,and i made all my work much easier.I recommend it to eveybody.
    It runs like that:

    strSQL = "UPDATE Products SET " & _
    " products.branch" & City & " = products.branch" & City & " + " & StrCartons & strWhere


    This code works excellent in my database.However after this code the delete command does not work.That is to say,
    strSQL = "UPDATE Products SET " & _
    " products.branch" & City & " = products.branch" & City & " + " & StrCartons & strWhere
    RunCommand acCmdDeleteRecord

    In this case the delete command does not work properly, and the product is not deleted,or at least stays at the same place.

    May be the reason is the name Value which is lacking in my second code?
    I will be so greatful for any help


    Bellow is my full code:

    Public Function deletearecord()

    'dimming****************************************** *
    City = Forms![FOrderInformation]![office] - 1
    Dim MySubform As Form
    Dim StrCartons As String
    Dim strQuantity As String
    Dim intAnswer As Integer
    Dim strSQL As String
    Dim strWhere As String, strCondition As String

    'setting *******************************************

    Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
    StrCartons = MySubform![cartons]
    strQuantity = MySubform![Quantity]
    strCondition = "ProductID=" & MySubform.productid
    strWhere = " WHERE " & strCondition
    strSQL = "UPDATE Products SET " & _
    " products.branch" & City & " = products.branch" & City & " + " & StrCartons & strWhere
    intAnswer = MsgBox(" The item will be deleted. Are you sure ? ", _
    vbQuestion + vbYesNo)

    'actions *******************************************

    If intAnswer = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    ' the function works if i replace DoCmd.RunSQL strSQL it with the following line: MySubform![branch1].Value = MySubform![branch1].Value +

    'MySubform![cartons].Value
    Forms![FOrderInformation]![current].Requery
    RunCommand acCmdDeleteRecord
    DoCmd.GoToControl "productid"
    End If
    End Function

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Delete Command (Access 2000)

    You should think carefully about what Wendell has said, particularly:

    <<You might also want to use a deleted flag in the LineItem table to avoid the complications of actually deleting a record, and not display records where the deleted flag is set. You will need to Requery the subform in any event so that you see the changes in LineItems. >>

    This makes a lot of sense, particularly when you are considering deleting records, because once deleted you cannot get them back, but once a record has been flagged as deleted then it doesn't matter. Also these records can be physically deleted at a later time.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Command (Access 2000)

    Thank you for your reply. My question is the following: why the function does not work in the first case and not in the second case?
    The first case is :
    DoCmd.RunSQL strSQL
    the second case is :
    MySubform![branch1].Value = MySubform![branch1].Value + 'MySubform![cartons].Value

    So once again the two cases

    1. Case 1, when the delete command does not work:


    DoCmd.RunSQL strSQL
    Forms![FOrderInformation]![current].Requery
    RunCommand acCmdDeleteRecord
    DoCmd.GoToControl "productid"

    Case 2, when the delte command does work.

    MySubform![branch1].Value = MySubform![branch1].Value + 'MySubform![cartons].Value
    Forms![FOrderInformation]![current].Requery
    RunCommand acCmdDeleteRecord
    DoCmd.GoToControl "productid"

    I think the problem is with the global variable, when i do not use a specific branch but refer to it through "city".
    What is interesting is that actually when used separately, each command works.

    I will be grateful to any help in this respect

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete Command (Access 2000)

    I am totally baffled by what you are doing. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> The line "RunCommand acCmdDeleteRecord" deletes the current record. If it isn't working, there is a reason. What *is* the current record in each case? Are you deleting a record from the form or subform?

    You seem to have a form and subform, and you're updating a record somewhere and then deleting a record from somewhere (somewhere else?), but since you haven't specified where each piece of code code you're trying to run is running from (form, subform or elsewhere), it is difficult to see exactly what the problem might be. You tossed out a reference to a "global" variable but there was no indication of what specifically that refers to. Please include the necessary details so someone can answer your question. Just posting a snippet of code doesn't provide enough detail, and you can't expect others to know the background of your question--each thread has to stand on its own in the Lounge or you have to provide a link back to a thread that does provide the background.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Command (Access 2000)

    Thank you to all of you who have responded. I am sending a zipped file of database, of course abridged( 80KB) since otherwise it will be
    unnecccessary lengthy.

    There, when choosing a product, and selecting the number of cartons, you go to the next line.Then you decide to cancel the product.
    You receive the message "The item will be deleted are you sure", but after confirmation the product stays.
    In the following case the product stays and is not deleted. The product is updated,but not deleted.

    If intAnswer = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    RunCommand acCmdDeleteRecord
    DoCmd.GoToControl "productid"
    End If
    End Function

    On the other hand, if i use the following code, then the product disappears.The product is updated and then is deleted

    If intAnswer = vbYes Then
    DoCmd.SetWarnings False
    MySubform![branch1].Value = MySubform![branch1].Value + MySubform![cartons].Value
    RunCommand acCmdDeleteRecord
    DoCmd.GoToControl "productid"
    End If
    End Function


    Please note i have a control in the main form called office. Then i refer to the branches not by their number, but with
    Dim City As Long
    City = Forms![FOrderInformation]![office] - 1
    With this approach i save a lot of work,and also i add on flexibility.

    it is namely by this approach that i cannot delete after i use it.Otherwise, if i use separate branches,then the product disappears.
    But then mu function is not optimal,i have to write for each separate office
    Also, the update function by itself works,but a delete statement after it does not work.

    P.S. i have decribed the whole function in my first email, but you can find it in the module of the database.
    Attached Files Attached Files

Posting Permissions

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