how to cut from one and send to another

[Jim VanGordon]

I'm trying to get a way to cut a record from one table
and paste it into another table via a command button on
the form. I posted this problem on the "Forms" string but
I was given an answer that doesn't work because there is
no database variable type (at least not when I try to use
Access 2000)

Here's what I was given (keep in mind that it doesn't
work):

Private Sub Copy_Record_Click()
Dim dbs as datbase
set dbs = currentdb
dbs.execute ("INSERT INTO table2 SELECT table1.* FROM
table1 WHERE table1.criteria ='" & "this record" & "'")
End Sub

This only works if the criteria you are looking for is a
string value. If you criteria is a number value, use this:

dbs.execute ("INSERT INTO table2 SELECT table1.* FROM
table1 WHERE table1.criteria =" & criteria)

TIA
Jim VanGordon

 

[Larry Jones]

This looks like you need to assign a reference to DAO (maybe ADODB), to get
the Database object type.

 

[Jim VanGordon]

How would I go about doing that? Might want to break it
down for me even though I do have a little VB experience.

TIA
Jim VanGordon

 

[PC Datasheet]

Jim,

Open to any code module. Click on Tools References. Uncheck Microsoft ADO.
Scroll down and check Microsoft DAO 3.5. Close.

 

[Jim VanGordon]

Ok, now I feel really dumb. If I have tables called New
Staking Engineering Forms and Completed Staking
Engineering Forms, how would I replace them with the
examples in the given code. I keep getting a syntax
error no matter what I try.

TIA
Jim

 

[John Vinson]

Ok, now I feel really dumb. If I have tables called New
Staking Engineering Forms and Completed Staking
Engineering Forms, how would I replace them with the
examples in the given code. I keep getting a syntax
error no matter what I try.

Replace Table1 with

[New Staking Engineering Forms]

and Table2 with

[Completed Staking Engineering Forms]

including the brackets - required because you (unwisely, IMO) are
using blanks in your table names.

If these two tables are of identical structure, you really should
consider the possibility of having a single table (which I'd name
tblStakingEngineeringForms, if I were designing it) with a yes/no
field [Completed]. If you want to see new forms use a query searching
for records wit [Completed] = False; for completed forms use
[Completed] = True. MUCH simpler to manage than moving data from table
to table!

 

[Jim VanGordon]

Thanks for offering that advice, but I'm not sure that
that would actually be the best way to implement my db.
I actually have about 7 identical tables and I'm afraid
that trying to query for all these different options
would make it more confusing on the actual user. I'm all
for consolidating it though. Any thoughts because this
is actually the first db in Access I've ever made?

TIA
Jim VanGordon

-----Original Message-----

Ok, now I feel really dumb. If I have tables called New
Staking Engineering Forms and Completed Staking
Engineering Forms, how would I replace them with the
examples in the given code. I keep getting a syntax
error no matter what I try.

Replace Table1 with

[New Staking Engineering Forms]

and Table2 with

[Completed Staking Engineering Forms]

including the brackets - required because you (unwisely, IMO) are
using blanks in your table names.

If these two tables are of identical structure, you really should
consider the possibility of having a single table (which I'd name
tblStakingEngineeringForms, if I were designing it) with a yes/no
field [Completed]. If you want to see new forms use a query searching
for records wit [Completed] = False; for completed forms use
[Completed] = True. MUCH simpler to manage than moving data from table
to table!


.

 

[Jim VanGordon]

Ok, here's the deal. Now I keep getting an error message
that says "too few parameters. Expected 1." I have no
idea what to do now. Any help would be appreciated.
Here's what I've got so far:

Private Sub send_to_____Click()

Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute ("INSERT INTO [New Staking Engineering Forms]
SELECT [Completed Staking Engineering Forms].* FROM
[Completed Staking Engineering Forms] WHERE [Completed
Staking Engineering Forms].criteria ='" & "this record"
& "'")

End Sub

Thanks in advance,
Jim VanGordon

 

[Jim VanGordon]

Problem solved.