Can I move a row of data from one table to another?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a basic stock control database.

I have a table with all stock currently held and I have made an autoform on
this table for adding stock and removing stock. My problm is I do not want
the button I created to delete a record, I would like to move the record to
another table I have so that I can track orders and where they went.

Many thanks to anyone who can help or point me in the right direction!
 
This can be done. I will assume both tables have the same structure and that
no data will be changed in the record. If this is not true, then some
adjustments to this will be necessary.

First, create an append query that will append to the other (history) table.
I don't know what field in your table is the unique identifier, but whatever
that field is, you will need to use it in the criteria of your query. I will
assume you have a text box with that identifier on your form. For example
purposes, I will use Product_Number. Where there is a field in your table
named Product_Number and a text box on your form named txtProductNumber.

So in the criteria row for the PRODUCT_NUMBER field in your query:

Forms!YourFormNameHere!txtProductNumber

Then in the click event of the command button that is currently deleting the
record, you need to put this code just before the delete so it will first
make a copy in the history table, then delete it from the current table:

CurrentDb.Execute("MyQueryNameHere"), dbFailOnError
 
Thanks Klatuu.

Seems to make sense however I am unsure where to start here? When you say
create append query do mean to go to queries then new?
 
I have managed to create the query however I dont know how to pplace that
code before the delete command, I thought I did it right but then it said
cannot find macro CurrentDb
 
If your code is like I posted:
CurrentDb.Execute("MyQueryName"), dbFailOnError
and you are getting an errror, it is possible you have a missing reference.
Open your VB Editor and select Tools-->References. You will see a list of
library references. Look for one that says Microsoft DAO ?? Library
Reference. The ?? is the version number. It will vary depending on your
version of Access. It should be checked.
 
Once again thank you for helping me. This is what I have done so far (ps
still no joy)

I went to queries, then new This is what the query looks like in design
view: http://www.edrenil.pwp.blueyonder.co.uk/access1.jpg

This is the form with the button I would like to copy the data to another
table before deleting the record:
http://www.edrenil.pwp.blueyonder.co.uk/access2.jpg

This is the error I get when trying to use the button:
http://www.edrenil.pwp.blueyonder.co.uk/access3.jpg

I know I have done something wrong somewhere but I dont know where?

Thanks
 
Hi Chris

As an alternative to a second table:
Create another field on your table labelled "Received". Make this a
True/False checkbox. Check the orders received. Set all querries, reports and
form views to exclude all records where the Received value is True. The data
will be out of the way but still available for viewing on forms or reports.
 
Klatuu once again I am in ur debt. I have nealry completed what I need to do
but am unsure where to enter command before the delete? Find below the
details for the button that deletes.

I don't know where to place "CurrentDb.Execute("Query"), dbFailOnError"

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, acMenuVer70

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Thanks Again!
 
The code is correct. It is in the wrong place. See where I have put it below.

Chris said:
Klatuu once again I am in ur debt. I have nealry completed what I need to do
but am unsure where to enter command before the delete? Find below the
details for the button that deletes.

I don't know where to place "CurrentDb.Execute("Query1"), dbFailOnError"

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click
Click to expand...

CurrentDb.Execute("Query1"), dbFailOnError"
 
Back
Top