Appending 1 record information to new table

[Brookelyn]

Hi,

I am creating a database in which every time an item gets deleted from the main table, it will be added to a different table. The code example I have at the bottom does ALL the files in that table. How do I get only the current record that I am on to move? I have tried do while, with endwith, and all the others and it still moves every record. Please help.

Thanks in advance!!!

Dim intCount As Variant
intCount = Me.ID

With intCount = intCount
SQLArchive = "INSERT INTO [Deleted]" & _
"SELECT [Store Data].ID, [Store Data].Code, [Store Data].Name FROM [Store Data];"
intCount = ""
DoCmd.RunSQL (SQLArchive)
End With

 

[pbaldy]

Your SQL statement needs a WHERE clause.

 

[Brookelyn]

Hi,
Thanks for the advice. I am unsure of what to put in the where condition simply because the data will always be different. Any suggestions?

Thank you very much

 

[pbaldy]

You said you would be on the record, so you just refer to the form control. Presuming ID is a number rather than text:

"...FROM [Store Data] WHERE ID = " & Me.ID & ";"

 

[Brookelyn]

Hi Pbaldy,

I'm sorry to keep bothering you. I used the code that you gave me, but now when it runs, it wants me to enter in the value of the ID field. I would like it to just automatically accept it from the record that it is on. I looked at the .currentrecord, but i'm not sure how to make it work. Any suggestions?

Thanks a bunch for your help

 

[pbaldy]

Double check the name of the ID field in the table and the name of the control on the form that contains it. I assumed both were named ID. Once they are both correct, it should automatically pull the value from the form and use it. If that doesn't fix it, can you post a sample db?

 

[Brookelyn]

I have checked the names of both fields, but I still get the enter parameter. I am not sure how to post a sample db or even create it. But here's how it's set up. I have a table named Store Data that contains EVERYTHING about a product. I then have a Table called deleted that will only contain the name, id, code of a product with a deleted Field that is a yes/no check box. Then there is a form called DeleteProduct (which i created using the wizard and has all the data as Store Data). There are three buttons on this page. There is a Find Record button (used the button wizard to create), a Delete Product, and a back to menu. On this delete Product button, I want to take the name, code, and id of the product that i'm currently viewing and move it to the Deleted Table. Then it will deleted the product from the Store Data Table. Here is the code that I used:
Private Sub DeleteProduct_Click()
On Error GoTo Err_DeleteProduct_Click
Dim SQLArchive As String

SQLArchive = "INSERT INTO [Deleted]" & _
"SELECT [Store Data].ID, [Store Data].Code, [Store Data].Name FROM [Store Data] WHERE [DeleteProduct].ID = [Store Data].ID"
DoCmd.RunSQL (SQLArchive)

If MsgBox("Delete this product?", 289, "Delete Product") = vbOK Then
DoCmd.SetWarnings False
'Delets the Item
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
Else

End If

Exit_DeleteProduct_Click:
Exit Sub

Err_DeleteProduct_Click:
MsgBox Err.Description
Resume Exit_DeleteProduct_Click

End Sub

Does this information help you any?

 

[pbaldy]

Try this in place of the existing line:

" SELECT [Store Data].ID, [Store Data].Code, [Store Data].Name FROM [Store Data] WHERE [Store Data].ID = " & Me.ID

This time, paste it in exactly and see if it works.

 

[Brookelyn]

Now it gives me a datatype mismatch in criteria expression error

 

[Brookelyn]

Okay here's something that might be a problem with that? My id field has - in it. Such as aroma-therapy-foaming-shower-gel could that be a problem? I also tried it on my code(which cannot be duplicated or null) field which is something like MO12345 and it still gave me the error

 

[pbaldy]

I brought this up earlier:

Presuming ID is a number rather than text

For a text datatype try this:

" SELECT [Store Data].ID, [Store Data].Code, [Store Data].Name FROM [Store Data] WHERE [Store Data].ID = '" & Me.ID & "'"

 

[Brookelyn]

Works beautifully. It would have taken me awhile to figure that one out. Thank you so much for your help. You've been great.