Copy and delete one record and put it on another table using check box event

[misstry]

I am very new to Access and VBA/SQL. I am attempting to copy one record to another table on click event. I then want to delete it from the original record that I am copying from. But first I am having trouble with the insert into statement. The table is not a local table but I do have it as a linked table. This is the code I am using. Could someone please help me with where I am stuck? It is telling me Method or data member not found and highlighting the below. Any help would be appreciated. Thank you in advance.

Private Sub APPROVE_AfterUpdate()

Dim db As Database

Set db = OpenDatabase("ROs To Be Deleted")

Dim strSQL As String

' Create a new record in the ROSTOBEDELETED table.

strSQL = " INSERT INTO ROSTOBEDELETED ([SR CM], AdminName, ADMIN, Vendor, VID, RO, MPN, M&E, [Serial Number], ScrubCode, ScrubComments, [Sr Comments], APPROVE, DENY) " & "SELECT " & Me.[SR CM] & ", " & Me.AdminName & " , " & Me.ADMIN & ", " & Me.Vendor & ", " & Me.VID & ", " & Me.RO & ", " & Me.MPN & ", " & Me.[M&E] & ", " & Me.[Serial Number] & "," & Me.ScrubCode & "," & Me.ScrubComments & "," & Me.[Sr Comments] & ", " & Me.APPROVE & "," & Me.DENY & ";"

End Sub

 

[Micron]

It is telling me Method or data member not found

On what line?
Also, avoid special characters in names, but if you MUST have them, wrap them in square brackets - [M&E] not M&E.
Second, if this sql is in form code, OK, you can use Me. but surely all of these fields are not numbers? You have not embedded quotes for anything (e.g. Me.ScrubComments).
Maybe it's ... Me.ScrubCode & ",'" & Me.ScrubComments & "'," not ... Me.ScrubCode & "," & Me.ScrubComments
Note the red single quotes to wrap text criteria in quotes. Numbers do not need quotes - in fact, using them around number data will cause an error.

 

[misstry]

Micron, it is on this line. Where I have it bolded. Wehere I have select I just want the info copied to the correct field from the table. I do not have a specific term I was filled in there. Do I have to put "SELECT" for each field on the table? I actually want them all. To you question about the fields being numbers. Most are not numbers. In those cases, I need to put the single quote like you listed below? Or do I put it like this me."ScrubComments" ?

"SELECT " & Me.[SR CM] & ", " & Me.AdminName & " , " & Me.ADMIN & ", " & Me.Vendor & ", " & Me.VID & ", " & Me.RO & ", " & Me.MPN & ", " & Me.[M&E] & ", " & Me.[Serial Number] & "," & Me.ScrubCode & "," & Me.ScrubComments & "," & Me.[Sr Comments] & ", " & Me.APPROVE & "," & Me.DENY & ";"

On what line?
Also, avoid special characters in names, but if you MUST have them, wrap them in square brackets - [M&E] not M&E.
Second, if this sql is in form code, OK, you can use Me. but surely all of these fields are not numbers? You have not embedded quotes for anything (e.g. Me.ScrubComments).
Maybe it's ... Me.ScrubCode & ",'" & Me.ScrubComments & "'," not ... Me.ScrubCode & "," & Me.ScrubComments
Note the red single quotes to wrap text criteria in quotes. Numbers do not need quotes - in fact, using them around number data will cause an error.

 

[xenou]

All string literals in raw sql need to have single quotes around them.

You should be able to test your sql by running it in a sql command window:

strSQL = " INSERT INTO ROSTOBEDELETED ([SR CM], AdminName, ADMIN, Vendor, VID, RO, MPN, M&E, [Serial Number], ScrubCode, ScrubComments, [Sr Comments], APPROVE, DENY) " & "SELECT " & Me.[SR CM] & ", " & Me.AdminName & " , " & Me.ADMIN & ", " & Me.Vendor & ", " & Me.VID & ", " & Me.RO & ", " & Me.MPN & ", " & Me.[M&E] & ", " & Me.[Serial Number] & "," & Me.ScrubCode & "," & Me.ScrubComments & "," & Me.[Sr Comments] & ", " & Me.APPROVE & "," & Me.DENY & ";"
[B][COLOR="#FF0000"]DEBUG.PRINT strSQL '//Check it out[/COLOR][/B]

I don't see a FROM clause so the sql looks incomplete.

it might be easier to have field in the table to update, rather then copying the record. Such as an IsRemoved field (True/False).

 

[Micron]

Aside from what Xenou says, the error you're reporting means that a method (which this is not) or data member (which the field name or control name is) cannot be resolved. If it's highlighting [SR CM] then it's telling you that Access doesn't know what that is. Usually this is because it is mis-spelled, often being as subtle as one extra space or character or wrong character. Even if you fix the existing error message, you'll likely encounter issues with what looks like an incorrect sql statement. It looks like you terminate this sql because of the ending semi-colon, yet you're not telling Access where to SELECT the values from (I tend to capitalize Access keywords). You cannot SELECT from a form. Also, even if it were proper, this sql can only be executed by an open form because of the use of the Me keyword, so it should run from the immediate window if the form is open as suggested. However, a common tactic is to copy/paste the sql from the immediate window and dump it into a new query and either switch to datasheet view (so that it doesn't actually run) or just execute it so that you can validate that it works (or not). Either way, if it balks, the offending part is usually highlighted in the sql view. You cannot do this because Me is not valid outside of the form. A proper form reference would be required.

Do I have to put "SELECT" for each field on the table

No, that would cause an error.

Most are not numbers. In those cases, I need to put the single quote like you listed below?

Yes, as Xenou noted.
However, if a form is driving this, I see no need for a FROM clause. Rather, I'd do it this way (I have not properly terminated each line so you cannot copy/paste verbatim without doing so:

strSQL = "INSERT INTO ROSTOBEDELETED ([SR CM], AdminName, ADMIN, Vendor, VID, RO, MPN, [M&E], [Serial Number], 
ScrubCode, ScrubComments, [Sr Comments], APPROVE, DENY) VALUES (" & Me.[SR CM] & ", '" & Me.AdminName & '" , " 
& Me.ADMIN & ", " & Me.Vendor & ", " & Me.VID & ", " & Me.RO & ", " & Me.MPN & ", " & Me.[M&E] & ", " 
& Me.[Serial Number] & "," & Me.ScrubCode & ",'" & Me.ScrubComments & "','" & Me.[Sr Comments] & "', " & 
Me.APPROVE & "," & Me.DENY & ";"

(I have to make assumptions, such as which might be text versus numbers). Only three are wrapped in single quotes. Take care to find which ones, and follow the pattern to add any additional ones. As noted, if you output this to the immediate window, you should easily spot any text values that are not properly wrapped in single quotes. The only reason single quotes are used is because the using doubles would cause premature and incorrect termination of some of the sections of your sql statement. I counted and there seems to be a match between the number of fields in the INSERT part and the VALUES part, which is crucial.