Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append and delete query (Access 2000)

    I have problems with appending and deleting a row from the form.The table i have is called products and the other table ProductsArchive.I want to carry out the actions from my button for finding the products :
    Dim StrProduct As String
    StrProduct = InputBox("Please enter Product number ")
    If StrProduct = "" Then
    Exit Sub
    End If
    Me.RecordsetClone.FindFirst "ProductID = " & StrProduct
    If Me.RecordsetClone.NoMatch Then
    MsgBox "Product " & StrProduct & " Not Found!!"
    Else
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If

    What is the command to append and delete the row with the chosen productid, after i have found it with the above code ?

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

    Re: Append and delete query (Access 2000)

    You could do something like this:

    Dim strSQL As String
    strSQL = "INSERT INTO ProductsArchive SELECT * FROM Products WHERE ProductID = " & strProduct
    CurrentDb.Execute strSQL, dbFailOnError
    strSQL = "DELETE * FROM Products WHERE ProductID = " & strProduct
    CurrentDb.Execute strSQL, dbFailOnError

    (I wouldn't combine this with a search, but you always want to do things differently)

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append and delete query (Access 2000)

    Thank you Hans for your good advice.Before starting to apply it, just to say that i have also a list box on the same form.Since you wouldnt do it with a search, perhaps that is what you mean, to do it from the list ?

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

    Re: Append and delete query (Access 2000)

    That would be possible.

Posting Permissions

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