Moving Code Between Data Tables Problem

[fnj6]

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

 

[GhostWolf]

I believe what you're going to need is two steps:
- insert new "used" movie code;
- delete that code from the "unused" table.

As far as whether the second method is outmoded: nope! It's just a question of preference.

 

[MajP]

Sorry, but your reasons for desiring to do this are wrong and or bad.

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).

Unless there is some security implemented that denies access to a table this makes no sense. Not sure how seperate tables avoids the wasting of codes.

I understand that programming between two tables instead of one is more complex, but from the user-end point of view,

Yes it requires you to synch two things, but not that big of a deal
As stated it is a delete query on one table and an insert query on the other.

I think one table could get messy much more easier.

No. It is much easier to manage and ensure data integrity in a single table.

Also, having the data move from table to table will simply be more aesthetic for my users who don't know much about Access.

NO! A "user" should never ever have direct access to a table. They should use forms based off of queries. The structure of the table has little direct relation to the presentation of the data.

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.

"really, really" wanting to do things wrong is not a good reason for doing something wrong.

 

[MarineMike1985]

fnj6,
I have to agree with MajP. your backend database should be designed in such as way to make YOUR life as a programmer easier. You design your forms (frontend) to make your user's life easier.
You can also reuse the same form for different views for different users, by redefining the query "Record Source" for that form when it is opened.

i.e.
if ButtonNewMovies is pressed
Record Source = "SELECT * FROM table WHERE movieStatus = "NEW CODE";
else
Record Source = "SELECT * FROM table WHERE movieStatus = "USED CODE";

Hope this helps. Good luck!