Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 27th, 2006, 01:06 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default copy entire row

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 With Quote
  #2 (permalink)  
Old November 28th, 2006, 10:05 PM
Authorized User
 
Join Date: Nov 2006
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #3 (permalink)  
Old December 1st, 2006, 12:06 AM
Registered User
 
Join Date: Nov 2006
Location: , , Canada.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Code:
 
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 With Quote
  #4 (permalink)  
Old December 1st, 2006, 11:39 AM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is it possible to copy n-row to another dataset? vinod_yadav1919 BOOK: Beginning C# 2005 Databases 1 November 12th, 2008 08:53 PM
How to copy n-row to another dataset? vinod_yadav1919 C# 0 November 12th, 2008 04:48 PM
Entire configuration capture bissourav SQL Server 2000 1 August 29th, 2006 05:56 AM
spreading an entire program darthziv Pro VB.NET 2002/2003 0 December 4th, 2005 10:41 AM
onClick change bgcolor of entire row Pallav Javascript How-To 2 October 21st, 2004 12:35 AM



All times are GMT -4. The time now is 06:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.