Moving a record from one table to another

[Sezzy]

Is there a way of when you click a button in a form it moves that record from one table into another one?

I know Access quite well, but I'm new to VB and modules.

Cheers

 

[fpendino]

Assuming that both tables contain the same fields or similar fields, yes this can be done.

Depending how much you understand there are a couple of ways to accomplish this. Your best bet is to create a List Box or Combo Box on your Form so that you can view the data in your tables.

Create an SQL string that pulls from the table that contains the data and put a 'WHERE' clause that tells it to pull a certain record based off of what you select in the ListBox/ComboBox.

Execute the SQL string using - Docmd.RunSQL (string name)

Create a Delete Query or string that uses a 'WHERE' clause similar to the one above, which is based off of the ListBox/ComboBox and execute it as well after you append the data to the new table.

 

[Sezzy]

Thanks for your help.

I tried doing what you sid, but I got confused!

In the end I put a button on the form that ran an append query and then a delete query for the records that i needed to move

Thanks again

 

[smercer]

Sezzy: I had my tables set up like that and now I wished I did not do it like that. Now it is better now because it is all stored in one table and the only thing that differences the records that I moved was the date sold field which is left null until a book is sold. and when I wanted results on what was sold it was a matter of setting a query to look for fields that "is not null"

Hope this helped you out.