Hi guys, for my A2 computing project i am doing a access database for
a video store, what i want to do is at the click of a command button,
a record from one table (tblstock) is moved to another table
(tblsales). so that the record in tblstock is deleted and the record
is now in tblsales. If you could help me i would grately appreciate
it.

Another problem i am stuck with is at the click of a command button i
would like a record to be duplicated and moved into another table.
What i mean is a record in tblstock is moved into tblloans, but is
still in tblstock at the same time.

Please please help me, i would greatly appreciate it

Well...
I don't think this is supposed to be a forum where you get your homework
done for you. But, I may as well point you in the right direction.

You need a form showing the records in tblStock. Then you need to
execute SQL code which will add (Insert, also called Append) and delete
records as you intend.

Uderstand that you're not really going to "move" a record. You must
hold its data in memory somewhere and then add a new record with that
data in it. Then for the tblSales problem you'll delete the original
from tblStock, and from the tblLoans problem, you will leave the
original there in tblStock.

I have two basic approaches, you can use either - but the instructor
might prefer one over the other.

For both methods - a word to the wise. Access names a text box the same
as the name of the field it is bound to. Add "txt" in front of the text
box name (Properties box, "Other" tab) to avoid confusion re. which you
are referring to. Like "txtCustomerID"

Use SetWarnings (set to True or False) to get rid of Access' automated
warning boxes. Be sure to turn them on again after running the
Append/Insert and/or Delete queries. You will want to requery the form
to display the revised results.

Method #1: Create queries which append and delete records in the
appropriate tables. Save with appropriate names. When the button is
clicked, use a statement: DoCmd.OpenQuery "qryQueryName"

For the Append Queries: In the top row of the design grid, build them to
refer to the form's controls for the field content. The easiest way is
to right-click on the field space, click "Build". Now, in the far left
panel, navigate to Forms - All Forms, then find the text box you need,
double-click, and that sets up the reference. Back in the query design
view, you set the appropriate destination field in the "Append To" row.

For the delete query, you can build it to refer to the form to get the
ID # of the record to delete. You use a reference to the appropriate
text box in the "Criteria" row to control which record is to be deleted.

Method #2: Have VBA code assemble a query string that runs the code you
need. Like
INSERT INTO tblSales ( [FieldName_1], [FieldName_2] )
SELECT "More Test field data" , 5.50;
(this is raw code that doesn't get data from the form)

To get data from the form, Dim a variable to hold the content of each
text box. Also Dim a string which will be the complete SQL statement.
Dim strSQLInsert as string
Dim strSQLDelete as string
Dim intTapeID as Integer
Dim curPrice as Currency
intTapeID = Me!txtTapeID
curPrice = Me!txtPrice
etc.
(You can do it without the Dim'd variables, but it's harder to debug)

Note: The SQL string does not need, and probably shouldn't have, any
"Returns". It can all be one long line.
You can use a continuation [ _] in the code to make it easier to read.

strSQLInsert = "INSERT INTO tblSales ( [FieldName_1], [FieldName_2] )
SELECT " & intTapeID & ", " & curPrice & ";"

For many, a tricky part can be getting the quote marks right for any
punctuations you need, and especially when including strings in the SQL
statement. Below is one where you are adding a one-field record, but
it's a string data type. See the single quotes here. In the final SQL
string, the double quotes dissappear, and the single quotes surround the
string that was passed in as strFirstName.

strSQLInsert = "INSERT INTO tblSales ( [FirstName] ) SELECT '" &
strFirstName & "';"

Run the SQL by using DoCmd.RunSQL strSQLInsert

You can instead write:
DoCmd.RunSQL "INSERT INTO tblSales ( [FirstName] ) SELECT (etc. etc.)
BUT this is much messier, and gives you no way to look at the string
easily to see what's wrong with it.

Bruce PIck

Damon wrote: