By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Copy Data from Table to Table with Command Button

P: 10
I am working with an Access 2003 inventory database. When a piece of equipment goes bad we junk it and delete it from the database. We have been copying the record to an excel form for storage just so we have a record of the former piece of equipment. I am trying to make the database more user friendly and keep all of the data in Access so have created another table called JunkedEquipment. I would like to be able to use a command button on a form to copy the record from the Equipment table to the JunkedEquipment table and then delete the record from the Equipment table and in the process give a confirmation message box to confirm the copy and delete.

I am not very familiar with VBA and have been fumbling over this the past 2 days. I have tried accomplishing this with a macro as well as a query but so far have fallen short of my goal.

Any Ideas or ways I can make this happen?

I appreciate any help given.
Nov 7 '07 #1
Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,327
What did you try?
Nov 7 '07 #2

P: 10
As I am not very knowledgable about setting up a query so I didn't try too much with that. I did try a macro and had it bring up a message box with a confirmation. It properly cancelled but would not run. I thought I could use the CopyObject and DeleteObject action to make this work but haven't had any luck with those as well. I am not sure if these actions will work with a record in a form or not.


Here is how I setup the Macro to date.

Action: Hourglass

Action: StopMacro w/ Condition: MsgBox("Are you sure?",289,"Junk Equipment")<>1

Action: CopyObject (Not sure how to direct record to another table.)

Action: DeleteObject


I have a feeling that I am somehow way off base with this.
Nov 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,327
Objects in this context refer to tables/forms/reports. What you're looking to do is to run an Append query and a Delete query. Get those two queries set up and you can call them from a macro or VBA.
Nov 7 '07 #4

P: 10
Ok, I'll try that. Thanks for your help so far.
Nov 7 '07 #5

Rabbit
Expert Mod 10K+
P: 12,327
Not a problem. Let us know how that goes.
Nov 7 '07 #6

P: 10
I got the append query up and running but I can't figure out what criteria to set in the query so that it just selects the current selected record in the form. Right now if I use a command button on my form to run this query it wants to append the entire table. I just want to append the current record that is being viewed in the form.
Nov 19 '07 #7

Rabbit
Expert Mod 10K+
P: 12,327
I got the append query up and running but I can't figure out what criteria to set in the query so that it just selects the current selected record in the form. Right now if I use a command button on my form to run this query it wants to append the entire table. I just want to append the current record that is being viewed in the form.
Have the query call the ID field on the form using the syntax Forms!FormName!ControlName.
Nov 19 '07 #8

P: 10
I originally couldn't get this to work so I took a break from this problem and worked on a few other things. When I came back it was no problem and I got it up and working right away. I guess I just had to take a break from it. Thanks for all of your help Rabbit!


I do have one other question for you, kinda dealing with the same thing, I am appending data to a different table in my database from a form to track equipment history everytime a change is made to the inventory. I can get it to work but only with two command buttons. Once the new information is entered in the form the user needs to use a command button to save the information in the form and then use a separate command button to append the data.

How can I get one command button to do both?

I would like one command button to save the data and then append it. Like I've said before I am not very good with VB so haven't been able to figure anything out with that and I can't find a way to do this with a macro. I know there is a save function when building a macro but that is for objects and not for data.
Dec 10 '07 #9

Rabbit
Expert Mod 10K+
P: 12,327
Well, I don't use macros but can't I'm guessing you have two macros set up? One for each button? Can't you just combine the actions of both macros?

If not, you can convert macros to VBA and then you just need to copy the contents of one into the other.
Dec 10 '07 #10

P: 10
Well, I don't use macros but can't I'm guessing you have two macros set up? One for each button? Can't you just combine the actions of both macros?

If not, you can convert macros to VBA and then you just need to copy the contents of one into the other.

I just setup the Save Command button with the Command button wizard. So that is VBA.

Code as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveChanges_Click()
  2. On Error GoTo Err_SaveChanges_Click
  3.  
  4.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  5.  
  6. Exit_SaveChanges_Click:
  7.     Exit Sub
  8.  
  9. Err_SaveChanges_Click:
  10.     MsgBox Err.Description
  11.     Resume Exit_SaveChanges_Click
  12.  
  13.     End Sub
  14.  


I setup the transaction command button with the command button wizard as well telling it run my append query.

Code as Follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveTransaction_Click()
  2. On Error GoTo Err_SaveTransaction_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "SaveTransactionQuery"
  7.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  8.  
  9. Exit_SaveTransaction_Click:
  10.     Exit Sub
  11.  
  12. Err_SaveTransaction_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_SaveTransaction_Click
  15.  
  16. End Sub



I already tried combining the code into one another but I couldn't get it work. I am not sure how to manipulate this code to get one command button to do both.
Dec 10 '07 #11

Rabbit
Expert Mod 10K+
P: 12,327
Please use code tags.

Just copy lines 4-7 in the second sub and put it after line 4 in the first sub.
Dec 10 '07 #12

P: 10
Please use code tags.

Just copy lines 4-7 in the second sub and put it after line 4 in the first sub.

That did the trick!

Thanks for all of your help again Rabbit.
Dec 10 '07 #13

Rabbit
Expert Mod 10K+
P: 12,327
Not a problem.
Dec 10 '07 #14

P: 3
Have the query call the ID field on the form using the syntax Forms!FormName!ControlName.
Hi,
can you explain exactly how you do this? Where do you enter the Forms!FormName!ControlName?
Mar 12 '08 #15

Scott Price
Expert 100+
P: 1,384
SJ1000,

Please do not Hijack another member's thread. It results in several problems: Primarily you are very unlikely to get an answer as most experts do not look at already answered threads.

Please post your question in it's own thread with all relevant information. You can include a link (use the globe icon on the menu bar of the reply window to insert the hyperlink) if that is relevant.

MODERATOR
Mar 12 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.