|
Subject:
|
copy entire row
|
|
Posted By:
|
stealthdevil
|
Post Date:
|
11/27/2006 12:06:05 PM
|
hi,
I'm working on a module that loops through a table and copies a row from that table to another table when it finds a certain criteria. I know how to copy the row field for field, but this table has over 20 fields so it would make my code pretty lengthy. Is there a command that will copy the entire row instead of just going field by field?
Thanks,
Dave
|
|
Reply By:
|
gigaboy
|
Reply Date:
|
11/28/2006 9:05:02 PM
|
create a query for all 20 fields that returns a recordset then loop through the recordset and spit it into the new table one at a time
here is some pseudocode
your way
select row
insert to table
select row
insert to table
select row
insert to table
select row
insert to table
select row
insert to table
select row
insert to table
select row
insert to table
select row
insert to table
select row
insert to table
etc
my way
select 20 rows
move first
loop until eof
insert to table
move next
next
this is a vague example I could provide you with some code or a demo when I have more time, if you want me to do this send me an email and I could send you a demo
scottbos@lycos.com
cheers
scott
|
|
Reply By:
|
jackd
|
Reply Date:
|
11/30/2006 11:06:53 PM
|
Since the 2 tables have the same structure, can you create
some SQL as a base. Then determine which records you want to copy to the second table. Then, use a runsql command based on the SQL and criteria.
Something along the following lines, would seem to reduce the amount of individual field coding.
Dim baseSql as string
Dim strCrit as string
baseSql = " INSERT INTO Table2 " _
& " SELECT Table1.* " _
& " FROM Table1 WHERE "
strCrit = " [fieldX] like '*myCriteria1*' " _
& " AND [fieldY] = '" & criteria2 & "'"
DoCmd.RunSql baseSQL & strCrit
|
|
Reply By:
|
stealthdevil
|
Reply Date:
|
12/1/2006 10:39:16 AM
|
hey guys!
Thanks for the replies. Unfortunately the users have once again changed there mind on what they want. This time it actually made my job easier . Now I just have to append table 1 to table 2 via a query and then sort table 2 by a field. Much simplier.
|
