Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete and requery (Access 2000)

    Order does not disappear

    I have a list boc called LstStockreceipt. When i click the order, the order is deleted indeed, but it stays on the list and does not disappear.I have to close the form,and
    open it again, and then the order is not seen .Is it possible to make the deleted order disappear immediately ?

    Dim strSQL As String
    Dim frm As Form
    Set frm = Forms![Frep]
    '*******************************************
    '2. delete the order
    DoCmd.SetWarnings False
    strSQL = "DELETE * FROM Orders WHERE orderID = " & frm!LstStockReceipt & ";"
    DoCmd.RunSQL strSQL
    strSQL = "DELETE * FROM [order details] WHERE orderID = " & frm!LstStockReceipt & ";"
    DoCmd.RunSQL strSQL
    Application.Echo True
    DoCmd.SetWarnings True
    frm.requery
    frm![LstStockReceipt].Requery
    frm![current].Requery

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete and requery (Access 2000)

    <P ID="edit" class=small>(Edited by D Willett on 01-Sep-03 10:30. )</P>Dim strSQL As String
    Dim frm As Form
    Set frm = Forms![Frep]
    '*******************************************
    '2. delete the order
    DoCmd.SetWarnings False
    strSQL = "DELETE * FROM Orders WHERE orderID = " & frm!LstStockReceipt & ";"
    DoCmd.RunSQL strSQL
    strSQL = "DELETE * FROM [order details] WHERE orderID = " & frm!LstStockReceipt & ";"
    DoCmd.RunSQL strSQL
    Application.Echo True
    DoCmd.SetWarnings True
    frm.requery
    Me.LstStockReceipt.Requery


    This may solve your problem.
    Try it without the Setwarnings to see what happens, you can always set them afterwards when you're haapy with the result.
    Perhaps the two instances of "DoCmd.RunSQL strSQL" are conflicting.


    You could try:

    Dim strSQL As String
    Dim frm As Form
    Set frm = Forms![Frep]
    '*******************************************
    '2. delete the order
    DoCmd.SetWarnings False
    strSQL = "DELETE * FROM Orders WHERE orderID = " & frm!LstStockReceipt & ";"
    strSQL2 = "DELETE * FROM [order details] WHERE orderID = " & frm!LstStockReceipt & ";"
    DoCmd.RunSQL strSQL
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RunSQL strSQL2
    DoCmd.RunCommand acCmdSaveRecord
    Application.Echo True
    DoCmd.SetWarnings True
    frm.requery
    Me.LstStockReceipt.Requery

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete and requery (Access 2000)

    If you have referential integrity set for the relationship between Orders and Order Details (and you should, otherwise you run the risk of inconsistencies), there are two possiblities:
    <UL><LI>You have set cascading deletes for the relationship. In that case, deleting a record from Orders will automatically delete related records from Order Details, so the second DELETE is superfluous.
    <LI>You have not set cascading deletes. In that case, the first DELETE will fail because there are related records in Order Details. You should delete the records from Order Details first and then the records from Orders.[/list]I don't know if this will solve the problem, but you should get this right first.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete and requery (Access 2000)

    This is exactly so, as you have written !!! I have followed your advice and i solved the problem !

    Thank you !!!!!!!!

Posting Permissions

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