Moving Code Between Data Tables Problem

[Soule]

Hi, Everyone,

I'm trying to adapt code for an .accdb form button in the main form class module that moves a piece of data from a field in one table to the same named field in another table. Not copies it, but actually moves it...as if it were being cut and pasted. The first table is full of "unused" movie codes, and I want the second to hold them as "used". I want to keep them separated with "a table between them" because I don't want any of these movie codes wasted (they cost money). I understand that programming between two tables instead of one is more complex, but from the user-end point of view, I think one table could get messy much more easier. Also, having the data move from table to table will simply be more aesthetic for my users who don't know much about Access. I understand that I could add a movie code "Status" field to the first table and write code that populates it as "used" once I have done doing with it what I'm going to, but I really, really want to move it between the two tables. I can't find public code that can execute this move, so I'm trying to adapt code that just copies between tables. So far, the block of code I have to "cut/paste" move it is as follows:

Option Explicit
Option Compare Database, Private Sub, etc. .....
 
' This block moves (not copies) that same movie code from "Unused Movie Code Table" to "Used Movie Code Table".
 
If gcfHandleErrors Then On Error GoTo PROC_ERR
Public Const gcfHandleErrors As Boolean = False
 
CurrentDb.Execute "INSERT INTO [Used Movie Code Table].MovieCode" & "SELECT TOP 1 [Unused Movie Code Table].MovieCode" & "FROM [Unused Movie Code Table]" & "ORDER BY MovieCode ASC", 
        
Debug.Print ("Move moviecode from Unused to Used table")
 
PROC_EXIT:
Exit Sub
 
PROC_ERR:
MsgBox ("Error moving movie code from Unused to Used table." & Err.Number & ") " & Err.Description, vbExclamation + vbOKOnly, _”Move Movie Code Between Tables”
 
Resume PROC_EXIT

Would this code move it or just copy it? Or not work at all? (I can't test it because it's a block in the middle of a bunch of other button automation code).

Alternately, I was advised to set-up the above code using sSQL statements first, followed by the CurrentDb.Execute statement...

' This block moves (not copies) that same movie code from "Unused Movie Code Table" to "Used Movie Code Table".
 
sSQL = "INSERT INTO [Used Movie Code Table].Movie Code"
sSQL = sSQL & " SELECT TOP 1 [Unused Movie Code Table].Movie Code"
sSQL = sSQL & " FROM [Unused Movie Code Table]"
sSQL = sSQL & " ORDER BY Movie Code ASC"
 
Debug.Print ("Move moviecode from Unused to Used table")
 
CurrentDb.Execute sSQL
 
PROC_EXIT:
Exit Sub
 
PROC_ERR:
MsgBox ("Error moving movie code from Unused to Used table." & Err.Number & ") " & Err.Description, vbExclamation + vbOKOnly, _”Move Movie Code Between Tables”
 
Resume PROC_EXIT

Is this just a layout thing to read the code easier? Will the code run better using this sSQL structure with the Execute command at the end? I thought that method was outmoded?

Thank you so much for taking the time to look at this code problem. You guys have been consistantly educating me in the past month, and saving my neck on a surprise coding project that is a true trial by fire. You'll never know how your unselfishness with your knowledge is appreciated. Any thoughts on this are welcomed.

Frank

[June7]

There is no 'move' action. You must INSERT into one, then DELETE from the other. That requires two separate SQL action statements.

[pbaldy]

That would copy it. You would need to run a delete query after that to delete it from the first table. Using a string variable makes it much easier to debug the SQL if it gets complicated:

http://www.baldyweb.com/ImmediateWindow.htm

If it was something simple, I would do it your first way:

CurrentDb.Execute "DELETE * FROM TableName"

Further, in your original code:

CurrentDb.Execute "INSERT INTO [Used Movie Code Table].MovieCode" & "SELECT TOP 1..."

The concatenation in red is not only unnecessary and inefficient, you introduced an error. You would end up with:

"INSERT INTO [Used Movie Code Table].MovieCodeSELECT..."

Note the lack of a space before SELECT, which would have caused a syntax error.

[Soule]

Thanks, Ya'll. I'm going to ditch this 2-table move entirely and go with a simpler single table with a flag field that populates with a date the user enters on the form. That will both keep the code cleaner and shorter. The users don't need Db aesthetics. Ha!

[pbaldy]

That would be the more normalized design anyway, so I think that's a good idea.

[June7]

I agree, better design.

Maybe don't even need code. Could have the field default to Date() or Now() to automate the timestamp.

[Soule]

I agree, better design.

...Could have the field default to Date() or Now() to automate the timestamp.

Yeah, I could do that with another lostfocus out of the date/time control. Less room for error with the user. Thanks!