how to cut from one and send to another

  • Thread starter Thread starter Jim VanGordon
  • Start date Start date
J

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
 
This looks like you need to assign a reference to DAO (maybe ADODB), to get
the Database object type.
 
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
 
Jim,

Open to any code module. Click on Tools References. Uncheck Microsoft ADO.
Scroll down and check Microsoft DAO 3.5. Close.
 
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
 
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.
Click to expand...

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!
 
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.
Click to expand...

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!


.
Click to expand...
 
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
 
Back
Top