Copy Data from Table to Table with Command Button

[BaneMajik]

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.

[Rabbit]

What did you try?

[BaneMajik]

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,"Jun k 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.

[Rabbit]

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.

[BaneMajik]

Ok, I'll try that. Thanks for your help so far.

[Rabbit]

Not a problem. Let us know how that goes.

[BaneMajik]

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.

[Rabbit]

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.

[BaneMajik]

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.

[Rabbit]

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.

[BaneMajik]

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:

[code=vb]Private Sub SaveChanges_Cli ck()
On Error GoTo Err_SaveChanges _Click

DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_SaveChange s_Click:
Exit Sub

Err_SaveChanges _Click:
MsgBox Err.Description
Resume Exit_SaveChange s_Click

End Sub
[/Code]



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

Code as Follows:

[code=vb]Private Sub SaveTransaction _Click()
On Error GoTo Err_SaveTransac tion_Click

Dim stDocName As String

stDocName = "SaveTransactio nQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_SaveTransa ction_Click:
Exit Sub

Err_SaveTransac tion_Click:
MsgBox Err.Description
Resume Exit_SaveTransa ction_Click

End Sub[/code]




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.

[Rabbit]

Please use code tags.

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

[BaneMajik]

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.

[Rabbit]

Not a problem.

[SJ1000]

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?