Append and delete query (Access 2000)

Topic

New Reply

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 ?

Reply | Quote

Replies

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)

Reply | Quote

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 ?

Reply | Quote

That would be possible.

Reply | Quote