Trying to move record from one table to another!

[adrian84]

Works perfectly!

So what is this about the db.execute command? As I said, I have multple back up copies of my databases so I can experiemnt with anything. If the db.execute command is quicker or more efficent I'd be willing to listen. Also I want to update you on the code I am using to append the records. The button is on the form of the "owning table" which appends to teh "receiving table". Below is the exact code I am currently using. Again this is my test database, the real deal will have better described buttons and better naming conventions.

Code:

Private Sub Command104_Click()

On Error GoTo Err_Handler

Dim d_SITE, d_DC, mysql

d_SITE = Me.[d_SITE]
d_DC = Me.[d_DC]

mysql = "INSERT INTO [NANW DB Spreadsheet] (d_SITE, d_DC) VALUES ('" & d_SITE & "', '" & d_DC & "');"

DoCmd.RunSQL mysql
DoCmd.SetWarnings False
DoCmd.SetWarnings True

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



Also on line 6 where it talks about "mysql = "INSERT INTO...", that line once I am finished will be extremely long due to the amount of fields I need to add. Is there some "word wrap" function that will allow me to see ALL the code in the window instead of having to scroll sideways to see everything?



Seperate from the previous request, back to the current code above, is there a portion of code we can add that will Delete the current record as well? So in action what would happen is:
-Press the command button
-record appends to "receiving table" table
-record is deleted from "owning table"
mission accomplished

This would be a great add on if we can do that!?

*Update: I got it to delete with:
DoCmd.RunCommand acCmdDeleteRecord

Is there a way to make it "assume" yes so I am not prompted twice for both the move and the delete? If not is there a way to have different message boxs for each prompt? i.e. "Archive Server operation has been canceled" (for saying no to move) and "Server has not been deleted from this database" (if I say no to the delete prompt).

[adrian84]

Yay! I got it all working as I need it too! All of it!

Slowly grasping the code and what the commands mean and how they work is helping a lot. Before I resolve this thread, would you liek to see the code I got working?

I think there may be a couple lines that can be taken out, but the codeworks so I wont mess with it lol.

Thanks for all your help!

Adrian

[khalid]

Yay! I got it all working as I need it too! All of it!....

Thanks for all your help!

Adrian

Glad that it worked for you , so you are ready to make this thread is solved? and also click on the scale the top right corner of the thread

With the Execute method, there is no need to call the SetWarnings method to disable change confirmation
message boxes because none are displayed. The Execute method operates directly on its parent
object.
There are several major benefits to using the Execute method rather than the DoCmd.RunSQL method:
❑ Execute runs faster than DoCmd.RunSQL does.
❑ Execute can be included in an existing transaction, like any other DAO operation, without
needing to specify an option to do so.
❑ You can specify several options that change the way the method works.

[rajsa]

Hi,

(I'm using Windows 7, Access 2010 - have updated in my settings but isn't showing on this post as yet)

I've come across this thread as I am trying to do exactly the same - I'm hitting the same problem as the original poster and I'm at around post number #11.

I have two tables: tblLocations and my archive table tblRemovedSamples. I'm wanting to use a button on a form to move the selected record to the archive table. All field names are the same, my ID field is numerical.

My code is below:

Private Sub btnRemoveSample_Click()


Dim strSQL As String


strSQL = "INSERT INTO tblRemovedSamples ( ID, Cryostat, Column_No, Drawer, Slot, Sample_ID, Cap_Colour, Date_Cryopreserved, Cryopreserved_User, Storage_User, Notes ) "
strSQL = strSQL + " SELECT tblLocation.ID, tblLocation.Cryostat, tblLocation.Column_No, tblLocation.Drawer, tblLocation.Slot, tblLocation.Sample_ID, tblLocation.Cap_Colour, tblLocation.Date_Cryopreserved, tblLocation.Cryopreserved_User, tblLocation.Storage_User, tblLocation.Notes "
strSQL = strSQL + " WHERE tblRemovedSamples.ID= )" & Me.ID


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


End Sub

I'm getting error message:

"Run-time error '3075':
Syntax error (missing operator) in query expression 'tblLocation.Notes WHERE tblRemovedSamples.ID =)1'."

I'm also a novice with Access - any help really appreciated as I'm getting to the point where I can't even see the code properly for staring at it!

[rajsa]

*Sigh* It would help if I could spell properly.

For reference - I added a FROM statement (FROM tblLocation) which cleared up the error 3075, then I was getting a parameter problem, caused by my mistyping one of the field names in the original table. I had checked all these already but just didn't see it. I don't know if this misspelling partially caused the initial problem.

Anyway, this works now