Trying to move record from one table to another!

[adrian84]

Hello!

First let me say, I have seen a ton of forums on this topic and I have even seen a million answers and people posting code... After viewing and TRYING the solutions (SQL, Queries, VB) I have arrived at that I am an epic failure lol...

It is such a simple concept! A simple single record move!

So let me give you a little details and hopefully someone out there can hook me up!

Story: I work as a contractor for a branch of the Military and I manage servers. Managing servers is my duty. We go through stages of replacing servers. To better track the servers and all the details about them, i.e. IP, MAC, S/N, warranties, Repair history, etc., I have created an MS Access 2007 DB. The DB works amazingly well and is very helpful. However, we decommission servers and still want to keep record of them. Hence some database modifications..

Situation: I need to be able to move 1 record from one table to another table in the same DB. Before you say "why would you do it that way", let me first say that this is just the way it needs to be. The DB started off as my personal tracking system that I shared with my team, today it is widespread across the entire department. With that, my leads and all have more say of how it is to be done. So...hence 2 separate tables.

Details: As I have said the objective is to get 1 record from one table to another. My records are all servers. So when we decommission a server, I have to be able to hit a button on my form that says "Archive" and then that record or "server" will leave the current table and then be placed in our "Decommissioned Servers" table. So I got two tables that I need to be able to transfer single records between via a button on a form.

I am experimenting all the solutions I see on a back up copy of the main DB. So I can screw it up all day and not care. With that, it may not be the exact naming conventions in the main db, but being as how I created it, I can just change the names out. My issue is my naive self trying to understand the code and what not. I am so close! But I just cannot grasp it to apply a solution.

So if I can get a few bites to this post, I will give you more details on the names of the tables and so on...or even post a back up copy of the DB if need be, minus the data of course

Anyway, seems like a great forum, just signed up yesterday. Hope to hear from y'all soon!



Thank you for your time,

Adrian

[khalid]

Hi Adrian,
Welcome to the Forum,

Whatever your concern about the situation, you just create a button on the form and on the button Click Event Property:

Private sub Button1_Click()

Dim strSQL as String
strSQL= "INSERT INTO table1 (Field1, Field2, Field3,... )
strSQL = strSQL + " SELECT table2.Field1, table2.Field2, table2.Field3,... "
strSQL = strSQL + " WHERE table1.ID= )" & Me.yourRecordID

DoCmd.SetWarnings False
Docmd.RunSQL (strSQL)
DoCmd.SetWarnings True

End sub

This is any aircode but you can get some idea...

[adrian84]

Hey there,

Thanks for the reply! Yeah I have seen very similar code and tried imitating it but still epic fail! In my part of course. Still, I'll try with this one. I won't be able to try it until tomorrow.

I'll keep you posted on what the out come is...

Crazy how something so simple can defeat a person sometimes

I know it is error on my part, just have not found where I am messing up!

Thanks again, I'll be in touch...

Adrian

[ssanfu]

I would urge you to try an alternate solution that will save you time and headaches.

Add a field named "Decommissioned", field type Boolean. Add the field to your queries with a criteria of FALSE. When a server is decommissioned, set the value to TRUE.
This way you can use all of your forms and reports without having to maintain a duplicate set of forms/reports/queries.
Also, you could add an option group ("Active", "Decommissioned","All") to select what you want in your forms/reports - "Active", "Decommissioned" or "All".

[June7]

I agree that maintaining 2 tables and moving data is not best relational database design and will more than likely lead to a lot of frustration. Why should users care how data is structured as long as they can get the desired output? They should not be dealing directly tables anyway. Or does every user has free reign to make design edits? That's a nightmare!

[adrian84]

Good morning,

Just got in today. I hear you all and your statements! I knew I would get replies like this lol...

Well as far as the way it is set up, the Decommission table already has a "Decommission" form to go with it. Each form is designed in such a way that when you hit the "Print this Server" button it will print a "report" of that server. Essentially it is just printing a record in terms of our MS Access world, but for "them" they view it as a report on a server. Each Form has their own design to the table it is associated with.

Also I know that many may view it as not a preferred designed, but it serves its purpose well. Then the decommission table came into play with its accompanying form. Part of the reason to keep them seperated is that there is a "counter" on the form that counts records. In the eyes of "them", they see it as the number of "servers" in our domain. This number is used every day in briefing reports so the counter needs to be accurate and not include the "servers", or records, that have been "decommissioned". So hence the seperate table.I am sure there are logical and coded ways to address this, but like I said, I am the only one working on this project and I am a Access Rookie! lol... Great program by the way!

Also, I know the concern about better ways of moving data, but really we are not moving tons. Essentially we would be moving 1 or 2 records or "servers" every now and then from the primary table. Even more, this database will really never reach records beyond 600 or 700 entries. If it ever does, I know I won't be around to see it lol. So With only that many records with an occasional move of a records every now and then, and me being the only Access guru at work at the momnet even though I am a rookie, they really want to keep it simple lol. Bad enough I have to train people to import a form!

Hope you can understand where I am coming from and why it is designed the way it is lol.

So at this point I am going to try and play with Khalid's code there and see what happens...

I'll keeep y'all updated

Adrian

[adrian84]

Ok, so I tried the code from Khalid and the attached pictures is my results...

*The screenshot is from my "test" database.

So what your seeing is:

-"Owning" Table is "DC Info Spreadsheet"
-"Recieving" Table is "NANW DC Spreadsheet"
-For testing purposes, I only included 2 fields which are "d_DC" and "d_SITE".
*d_DC is the Primary Key for the "DC Info Spreadsheet" table!
*NANW DC Spreadsheet has no Primary Key. BOTH tables have the same fields in them.

I am sure I am screwing this up somewhere but have not found a way to yell at myself for the issue lol...

So, I am still stuck, with the given information what do you think needs to be done to complete the objective?

Again, Objective: Via a comand button on the "DC Info Spreadeet" form, this button needs to be able to move a record to a sister table, "NANW DC Spreadsheet", in the same DB. Bonus points if the button would also delete the record during the move from "DC info Spreadsheet".

[adrian84]

*Update

Same code I just eliminated the spaces in my tbale names and that seemed to fix the error I was getting. However I have a new error when I run the command button... (attached screen shot)

I can't beleive I am getting owned over a little button lol...

Part of the problem is I don't really understand the code, i.e. eliminating spaces in the table names.

[June7]

Advise avoid creating names with spaces, special characters (@ # $ % & *), punctuation (underscore is exception), also reserved words as entire name. If any of these present, must enclose in [] in code. Just removing the spaces from the code won't help if the actual object names still have spaces.

Your SQL string has an unpaired parenthesis. The inner nested query must be within parenthesis.

[khalid]

*Update

Same code I just eliminated the spaces in my tbale names and that seemed to fix the error I was getting. However I have a new error when I run the command button... (attached screen shot)

I can't beleive I am getting owned over a little button lol...

Part of the problem is I don't really understand the code, i.e. eliminating spaces in the table names.

Adrain!

Two things are here which create the error....
1) Your table's name have spaces, which creates error it should be sorounded with the [] in the Select Setement.
2) If your recordID is not a numeric then, soround it with the ''

strSQL = strSQL + " WHERE table1.ID= ')" & Me.yourRecordID &"'"

And also if you can post your code wrapping in a code a by pressing # sign in the edit window while replying the post, would be helpful to read and edit for correction.

Hope this help.

[adrian84]

Hello Khalid,

Thanks for your posts! Too bad we are practiclaly on different ends of the earth! I manage user accounts for the militarty that are in Kuwait...usually don't hear from them until the next day! lol...

Ok, I copied the code and will paste it below... I was playing around with it some more and this is where I left off...

#
Private Sub Command104_Click()
Dim strSQL As String
strSQL = "INSERT INTO [NANW DCS preadsheet] ([d_DC], [d_SITE] )"
strSQL = strSQL + " SELECT [DC Info Spreadsheet].[d_DC], [DC Info Spreadsheet].[d_SITE] "
strSQL = strSQL + " WHERE [NANW DC Spreadsheet].[d_DC]= ')" & Me.[d_DC] & ""
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub


***You should reference my other post for what each name/field is I am sure I have somehting lined up wrong. Still thanks for your continued patience and assistance. Much appreciated.

[adrian84]

Yay! I was playing around and I got some of it to work!

I have some other issues now that are in the way. They are error messages.

1. If I run my button and click yes to copy/append the 1 row it copies accordingly as I want it too. If I click no, I get a runtime error and am brought to the debug option. How can this be coded so when I click no, it just stops the command and no pop ups about errors. When I clcik no cause I decide not to copy the record, I want to click no and be done. Not to be prompted with errors and brought to debug mode.

2. So my command button moves from the "owning table" (my primary table or table 1) to the "receiving table" (my secondary table or table 2). If I just so happen to have the "receiving table" open and I press my command button on the "owning table" at the same time, I get an error saying that it cannot complete because my "receiving table" is open. Anyway to get rid of that error or fix it?

[khalid]

Hello Khalid,

Thanks for your posts! ... I was playing around with it some more and this is where I left off...

#
Private Sub Command104_Click()
Dim strSQL As String
strSQL = "INSERT INTO [NANW DCS preadsheet] ([d_DC], [d_SITE] )"
strSQL = strSQL + " SELECT [DC Info Spreadsheet].[d_DC], [DC Info Spreadsheet].[d_SITE] "
strSQL = strSQL + " WHERE [NANW DC Spreadsheet].[d_DC]= ')" & Me.[d_DC] & ""
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

Hi,
Your code is okay, but there is a little missing I noted, you are missing ' after last &. It should be:

strSQL = strSQL + " WHERE [NANW DC Spreadsheet].[d_DC]= ')" & Me.[d_DC] & "'"

You got an error while not copying record to the "receiving table"; to get rid of it there is a solution:
Add the highlited lines to your code:

 
Private Sub Command104_Click()
On Error GoTo Err_Handler
 
Dim strSQL As String
strSQL = "INSERT INTO [NANW DCS preadsheet] ([d_DC], [d_SITE] )"
strSQL = strSQL + " SELECT [DC Info Spreadsheet].[d_DC], [DC Info Spreadsheet].[d_SITE] "
strSQL = strSQL + " WHERE [NANW DC Spreadsheet].[d_DC]= ')" & Me.[d_DC] & "'"
 
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
 
Err_Exit:
Exit Sub
Err_Handler:
   MsgBox Err.Description
   Resume Err_Exit
End Sub

Give a try

[adrian84]

Hey Khalid,

I added that additional code for the error and it did change it. So when I go to append a record, and then I decide "No" I get a different pop up that says "The RunSQL action was canceled". Is there away to get rid of this pop up all together? Or is there a way to reword it cause that would work too I guess.

The reason for all the "pickyness" is because I need to make this DB as user friendly as possible. I have people using it now who never used Access! So when they see stuff like that pop up I always get questions people asking for help even though it is not a big deal. Simply, they just do not know what they are looking at.

So if I can change the caption to somehting like "The Server Archive operation has been canceled" that would be cool, or if I could eliminate any pop ups upon clicking "No" that would work too.

Thanks for your help, I am getting a better understanding of the code!

Adrian

[khalid]

Hummm!!
So you want to change the Message to something like user friendly. You got very cool users they don't know what access is ... we need also some. lol.
well there is a better and faster way to execute the code with no popups through db.excute command, but time being we will modify the existing code a little bit to your suggested popup message.

Private Sub Command104_Click()
On Error GoTo Err_Handler

Dim strSQL As String
strSQL = "INSERT INTO [NANW DCS preadsheet] ([d_DC], [d_SITE] )"
strSQL = strSQL + " SELECT [DC Info Spreadsheet].[d_DC], [DC Info Spreadsheet].[d_SITE] "
strSQL = strSQL + " WHERE [NANW DC Spreadsheet].[d_DC]= ')" & Me.[d_DC] & "'"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

Err_Exit:
Exit Sub
Err_Handler:
MsgBox ("The Server Archive operation has been canceled.")
Resume Err_Exit
End Sub

See what happens