Solved: Macro to copy record from one table to another

[jaureguima]

I am trying to create a Macro(in the form of a command button) that when it is ran it will copy the record currently open when viewing it in the following forms:

Budgets Rec Step 2
CPR Approval
Project look up

For instance if the tracking number is 100 it will copy the record from the "main table" and place it in the "side table". It will no longer be recognized as 100 in the main table, but as 100.1. It will also check the box off were it revision to state that it is a revision.

I also want to create a command button in form "Project Look up" labeled complete and once this is checked off it will save a copy of that record to the side table and delete it from the main table:banghead:

[jaureguima]

For some reason it won't let me attach my file.

the column is labeled "tracking number" in the "Main table"
I want to copy from the "main table" table to the "side table" table
and all of the form names are as stated above:doh:

[OBP]

Did you try and attach the database?
It has to be Zipped to put on the forum.
So you want a special IDNumber, I take it that you are NOT using "Autonumber" to generate it in the "main table".
Are you sure that you actually want to delete the records from the "main table" or just set a flag that says "deleted" so that they are ignored?

[jaureguima]

:help I was able to attach it. The flag idea will probably work better. No, I am not using an auto number. Here is what I am trying to do. Create 4 forms. When they create a record for "budget recieved" (form) it will post to the table. But once this is revised in any of the steps (budget rec step 2, cpr approval, or project look up) all forms, I want to be able to identify that there is a revision and keep a running history of it so I thought the best way to do this is by numbering them with .1, .2, .3 etc 1000 will be the original 1000.1 will be the first revision. On the form I would like them to be able to push a revision button and all this will automaticly happen.

I am still in the begining stages of VB so I was unable to create the porper script. Thanks for all of your help:bow:

[OBP]

Do you want the original tracking number to be entered and incremented automatically when you enter a new Record?
At the moment there doesn't appear to be anywhere to enter the tracking number on the Budgets received form.

[jaureguima]

Yes incremented automactically sounds good. Sorry I added the tracking number to the "main table" at the last minute. It is not on the form yet

[OBP]

I have noticed that you do not have a key field in your Main Table.
What field is going to be unique in the table, the Project field?

[jaureguima]

the "tracking number" field will be the unique number. We can possibly have multiple projects with the same name.

[OBP]

Sorry, you shouldn't do what you want to do with the tracking numbers and have it as the key field. You would be much better having a simple Autonumber ID as the key field and the tracking number for what you want to do, i.e. tracking changes.

[jaureguima]

sounds good lets go with an auto number Id. Thanks for all your help

[OBP]

How many revisions are there likely to be, more than 9?

[jaureguima]

I was told that in rare cases they do go over 9. So it would be fine to keep it under 9:ipray:

[OBP]

OK, the attached version has some new Tables/Queries/Forms that will update the Main table and also run an "Append" query to add the Main Table record to the Side Table.
I have only put the Button on the Budget Rec Step 2 form for you to try out.

[jaureguima]

One last question? is there anyway to avoid the questions "you are about to run a update query" and "you are about to append 1 row". Thanks you have been a big help I have been banging my head trying to figure that out.

[OBP]

Yes you you can turn the System Warning Messages off and on with

DoCmd.SetWarnings False ' turns off
DoCmd.SetWarnings True ' turns back on

in the VBA before and after running the Query.

[jaureguima]

thanks you guys are great!