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 of time and 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

[NeoPa]

I know you've considered this already Frank, from the comments you've posted, but I wouldn't feel right if I failed even to notify you that your approach, far from making your life easier, will (and clearly already has) introduced new head-aches. Tables should only ever be created to hold data that is in a different format from other tables in the general run of things. When data is in the same format but has to move between states then it makes much better sense to add a state field to handle it. Database Normalisation and Table Structures may well help with your progressing education. I read your explanation, and it indicates careful consideration, but if I may say without intending to insult in any way, it also shows a lack of appreciation of some of the principles. Users, for instance, need never be exposed to the practicalities of how things are managed under the hood. You could easily use two separate queries run on the one, properly organised, table to represent the two sets of disparate data and each would appear to the users as a recordset of the appropriate records.

Anyway. Enough of all that. I know you didn't come here for that so I'll move on.

Frank:
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?

This is certainly just to make life easier yes. The layout and also the easier access to the actual string to be executed can certainly help at the development stage. The Execute command is common to both versions so I have no way to interpret the second part of that question. It's the main way that a SQL action query string is run in Access.

There are a few problems with your code :

1. The SQL command only copies the record across. Nowhere that I can see does it remove it from the original table.
2. In the second set of SQL code creation (using sSQL) the field name has changed and includes an embedded space. This reference will fail unless all such references are enclosed in square brackets [] just as the table names are.
3. The code always seems to take the first (or lowest in order) [Movie Code] item to copy. You don't explain the rationale here and I see none that makes sense. Not that there is none, just that I see none. Maybe worth explaining if there is one that makes sense.
4. Moving data from one dataset to another should really be an indivisible process, so should be handled (by a copy followed by a delete) within a single transaction.

That seems to be enough to be getting along with for now.

PS. The sSQL code could be done as :

Dim sSQL As String
 
sSQL = "INSERT INTO [Used Movie Code Table].[Movie Code] " & _
       "SELECT TOP 1 [Movie Code] " & _
       "FROM   [Unused Movie Code Table] " & _
       "ORDER BY [Movie Code] ASC"

[dsatino]

Ok, I'm not going to get too deep here since I see NeoPa answered extensively and I'm sure I'm just reiterating what he already said.

You have no need for two tables. You just need to add a Yes/No column and store all the numbers in a single table. Not only is this method easier, but if follows the principles of database normalization.

Your method also leaves the possibility your number being in both tables, or neither table.

[soule]

Thanks, NeoPa and dsatino,

After going broad on multiple forum sites, It's obvious I would be a fool to continue on this 2-table/INSERT...DELETE tack. I'm going to a single table with a new "used on" date field that is simply populated by user form entry. Then I can create one query for un-used codes and one for used. Problem diminished. "Brevity is the essence of wit" - I can't remember who said that. :S Thanks, All.

[NeoPa]

I knew you sounded intelligent after the first post of yours I read. It's good to feel vindicated :-D