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 March 25th, 2004, 09:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default Code to copy a record from one table to another.

Good morning everyone!
I have created a button that I want to use to copy a record from one table and paste it in another. The table that it will come from is a linked table from another database. I created a query off of that table containing only the fields that I want to be copied over plus one other field (RFQNumber)that will be used to determine which record is copied by the criteria setting. Now I need to write the code that will select all the fields except the RFQNumber field and copy and paste the value of the fields into another table. The field names in both tables are identical.
Any ideas
Please be explanatory as possible. Thank you in advance for any help you can give me.

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #2 (permalink)  
Old March 25th, 2004, 09:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How's this sound?
Code:
INSERT INTO Table2( Field1, Field2, FieldN )
SELECT Field1, Field2, FieldN
FROM Table2;
Which you could also do using the GUI with an Append query.


I am a loud man with a very large hat. This means I am in charge
Reply With Quote
  #3 (permalink)  
Old March 26th, 2004, 10:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

Thank you for your reply...
Here is what I typed in and the errors I received:
Code:
INSERT INTO HoldTable(QuoteType, QuoteQuantity, Initiator, DueDate, ReleasedForProcessing, GivenToQuoteGroup, GivenToCustomer, Customer, CustomerContact, ContactPhone, Vehicle, CustomerPartNumber, StantPartNumber, Description, Application, ApplicationType, ModelYear, ProductLife, TargetPrice, VolumeYear1, VolumeYeat2, VolumeYear3 , VolumeYear4, Hyperlink1, Hyperlink2, Hyperlink3, Hyperlink4, Hyperlink5, MarkedPrint, CustomerPrints, CompletePrints, Samples, DetailedDescription, MaterialSpecification, BOM, AssemblyWeight, AnnualValidation, Prototype, Prelaunch, Production, SpecialCharacteristics, ConceptDeliverable, PrototypesDeliverable, _
PilotRunDeliverable , PPAPDeliverable, ProductionDeliverable, DevelopmentSite, ProductConfig1, ProductConfig2, ProductConfig3, PatentIssues, PatentIssues1, TechAssump1, TechAssump2, TechAssump3, KnownLimit1, KnownLimit2, KnownLimit3, MfgAssumpt1, MfgAssumpt2, MfgAssumpt3, Equipment, TestAssumpt1, TestAssumpt2, TestAssumpt3 , PackagingType, Timing, Location, AdditionalStaffApproval, BAUD, GeneralManager, ManagingDirector, DivQualMan, DirectorEng, ProdEgr, QualityEng, MfgEng, ToolEng, TestingReqd, AudtiTestingReqd, LabFixturesReqd, GagesRqd, RejectRate, TestingDescription, CustomerSpecification, ApplicableStandards, _
PerformanceDescription , AsmDwgsComplete, CompDwgComplete, BOMComplete, DwgDescription, AssemblyEquipmentREqd, TestEquipmentReqd, TypeOfDateCodeReqd, MfgDescription, IncomingReq, OutgoingReq, InternalMaterialreq, PackagingDescription, GanttCompleted, Approval1, Approval2, Approval3, Approval4, Approval5, AdditonalInformation, AdditionalExistingVolume , RDTestingFixturesRequired, RDTestingRequired, RDDescription, PVTestingRequired, DVTestingRequired)
SELECT RFQInputSheet.QuoteType, RFQInputSheet.QuoteQuantity, RFQInputSheet.Initiator, RFQInputSheet.DueDate, _
RFQInputSheet.ReleasedForProcessing, RFQInputSheet.GivenToQuoteGroup, RFQInputSheet.GivenToCustomer, RFQInputSheet.Customer, RFQInputSheet.CustomerContact, _
RFQInputSheet.ContactPhone, RFQInputSheet.Vehicle, RFQInputSheet.CustomerPartNumber, RFQInputSheet.StantPartNumber, RFQInputSheet.Description, RFQInputSheet.Application, _
RFQInputSheet.ApplicationType, RFQInputSheet.ModelYear, RFQInputSheet.ProductLife, RFQInputSheet.TargetPrice, RFQInputSheet.VolumeYear1, RFQInputSheet.VolumeYeat2, _
RFQInputSheet.VolumeYear3, RFQInputSheet.VolumeYear4, RFQInputSheet.Hyperlink1, RFQInputSheet.Hyperlink2, RFQInputSheet.Hyperlink3, RFQInputSheet.Hyperlink4, _
RFQInputSheet.Hyperlink5, RFQInputSheet.MarkedPrint, RFQInputSheet.CustomerPrints, RFQInputSheet.CompletePrints, RFQInputSheet.Samples, RFQInputSheet.DetailedDescription, _
RFQInputSheet.MaterialSpecification, RFQInputSheet.BOM, RFQInputSheet.AssemblyWeight, RFQInputSheet.AnnualValidation , RFQInputSheet.Prototype, RFQInputSheet.Prelaunch, _
RFQInputSheet.Production, RFQInputSheet.SpecialCharacteristics, RFQInputSheet.ConceptDeliverable, RFQInputSheet.PrototypesDeliverable, _
RFQInputSheet.PilotRunDeliverable, RFQInputSheet.PPAPDeliverable, RFQInputSheet.ProductionDeliverable, RFQInputSheet.DevelopmentSite, _
RFQInputSheet.ProductConfig1, RFQInputSheet.ProductConfig2, RFQInputSheet.ProductConfig3, RFQInputSheet.PatentIssues, RFQInputSheet.PatentIssues1, _
RFQInputSheet.TechAssump1, RFQInputSheet.TechAssump2, RFQInputSheet.TechAssump3, RFQInputSheet.KnownLimit1, RFQInputSheet.KnownLimit2, RFQInputSheet.KnownLimit3, _
RFQInputSheet.MfgAssumpt1, RFQInputSheet.MfgAssumpt2, RFQInputSheet.MfgAssumpt3, RFQInputSheet.Equipment, RFQInputSheet.TestAssumpt1, RFQInputSheet.TestAssumpt2, _
RFQInputSheet.TestAssumpt3, RFQInputSheet.PackagingType, RFQInputSheet.Timing, RFQInputSheet.Location , RFQInputSheet.AdditionalStaffApproval, _
RFQInputSheet.BAUD , RFQInputSheet.GeneralManager , RFQInputSheet.ManagingDirector, RFQInputSheet.DivQualMan, RFQInputSheet.DirectorEng, RFQInputSheet.ProdEgr, _
RFQInputSheet.QualityEng , RFQInputSheet.MfgEng, RFQInputSheet.ToolEng, RFQInputSheet.TestingReqd, RFQInputSheet.AudtiTestingReqd, RFQInputSheet.LabFixturesReqd, _
RFQInputSheet.GagesRqd, RFQInputSheet.RejectRate, RFQInputSheet.TestingDescription, RFQInputSheet.CustomerSpecification, RFQInputSheet.ApplicableStandards, _
RFQInputSheet.PerformanceDescription, RFQInputSheet.AsmDwgsComplete, RFQInputSheet.CompDwgComplete, RFQInputSheet.BOMComplete, RFQInputSheet.DwgDescription, _
RFQInputSheet.AssemblyEquipmentREqd, RFQInputSheet.TestEquipmentReqd, RFQInputSheet.TypeOfDateCodeReqd, RFQInputSheet.MfgDescription, RFQInputSheet.IncomingReq, _
RFQInputSheet.OutgoingReq, RFQInputSheet.InternalMaterialreq, RFQInputSheet.PackagingDescription, RFQInputSheet.GanttCompleted, RFQInputSheet.Approval1, _
RFQInputSheet.Approval2, RFQInputSheet.Approval3, RFQInputSheet.Approval4, RFQInputSheet.Approval5, RFQInputSheet.AdditonalInformation , _
RFQInputSheet.AdditionalExistingVolume, RFQInputSheet.RDTestingFixturesRequired, RFQInputSheet.RDTestingRequired, RFQInputSheet.RDDescription, _
RFQInputSheet.PVTestingRequired, RFQInputSheet.DVTestingRequired FROM RFQInputSheet
FROM RFQCopyQuery
VB Highlights "HoldTable" and gives me:
Quote:
quote:Compile error:
Expected: End of statement
VB Highlights the whole "Insert Into" statement through the end of the () and gives me:
Quote:
quote: Compile error:
Syntax error
VB highlights the first "RFQInputSheet" of the select statement and gives me:
Quote:
quote: Compile error:
Expected: Case
Any thoughts / suggestions?

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #4 (permalink)  
Old March 29th, 2004, 12:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That certainly is one hell of a SQL statement
One suggestion -
If you're running this through VB, you'll need to enclose your statement properly.

So what you'd end up with is something along the lines of:
Code:
CurrentDb.Execute ("INSERT INTO HoldTable(QuoteType, QuoteQuantity, Initiator, DueDate, ReleasedForProcessing, GivenToQuoteGroup, GivenToCustomer, Customer, CustomerContact, ContactPhone, " _
  & "Vehicle, CustomerPartNumber, StantPartNumber, Description, Application, ApplicationType, ModelYear, ProductLife, TargetPrice, VolumeYear1, VolumeYeat2, VolumeYear3 , VolumeYear4, Hyperlink1, " _
  & "Hyperlink2, Hyperlink3, Hyperlink4, Hyperlink5, MarkedPrint, CustomerPrints, CompletePrints, Samples, DetailedDescription, MaterialSpecification, BOM, AssemblyWeight, AnnualValidation, Prototype, " _
  & "Prelaunch, Production, SpecialCharacteristics, ConceptDeliverable, PrototypesDeliverable, " _
  & "PilotRunDeliverable , PPAPDeliverable, ProductionDeliverable, DevelopmentSite, ProductConfig1, ProductConfig2, ProductConfig3, PatentIssues, PatentIssues1, TechAssump1, TechAssump2, TechAssump3, KnownLimit1, KnownLimit2, " _
  & "KnownLimit3, MfgAssumpt1, MfgAssumpt2, MfgAssumpt3, Equipment, TestAssumpt1, TestAssumpt2, TestAssumpt3 , PackagingType, Timing, Location, AdditionalStaffApproval, BAUD, GeneralManager, ManagingDirector, DivQualMan, DirectorEng, ProdEgr, " _
  & "QualityEng, MfgEng, ToolEng, TestingReqd, AudtiTestingReqd, LabFixturesReqd, GagesRqd, RejectRate, TestingDescription, CustomerSpecification, ApplicableStandards, " _
  & "PerformanceDescription , AsmDwgsComplete, CompDwgComplete, BOMComplete, DwgDescription, AssemblyEquipmentREqd, TestEquipmentReqd, TypeOfDateCodeReqd, MfgDescription, IncomingReq, OutgoingReq, InternalMaterialreq, PackagingDescription, GanttCompleted, Approval1, Approval2, " _
  & "Approval3, Approval4, Approval5, AdditonalInformation, AdditionalExistingVolume , RDTestingFixturesRequired, RDTestingRequired, RDDescription, PVTestingRequired, DVTestingRequired) SELECT RFQInputSheet.QuoteType, RFQInputSheet.QuoteQuantity, RFQInputSheet.Initiator, RFQInputSheet.DueDate, " _
  & "RFQInputSheet.ReleasedForProcessing, RFQInputSheet.GivenToQuoteGroup, RFQInputSheet.GivenToCustomer, RFQInputSheet.Customer, RFQInputSheet.CustomerContact, RFQInputSheet.ContactPhone, RFQInputSheet.Vehicle, RFQInputSheet.CustomerPartNumber, RFQInputSheet.StantPartNumber, RFQInputSheet.Description, RFQInputSheet.Application, " _
  & "RFQInputSheet.ApplicationType, RFQInputSheet.ModelYear, RFQInputSheet.ProductLife, RFQInputSheet.TargetPrice, RFQInputSheet.VolumeYear1, RFQInputSheet.VolumeYeat2, RFQInputSheet.VolumeYear3, RFQInputSheet.VolumeYear4, RFQInputSheet.Hyperlink1, RFQInputSheet.Hyperlink2, RFQInputSheet.Hyperlink3, RFQInputSheet.Hyperlink4, " _
  & "RFQInputSheet.Hyperlink5, RFQInputSheet.MarkedPrint, RFQInputSheet.CustomerPrints, RFQInputSheet.CompletePrints, RFQInputSheet.Samples, RFQInputSheet.DetailedDescription, RFQInputSheet.MaterialSpecification, RFQInputSheet.BOM, RFQInputSheet.AssemblyWeight, RFQInputSheet.AnnualValidation , RFQInputSheet.Prototype, RFQInputSheet.Prelaunch, " _
  & "RFQInputSheet.Production, RFQInputSheet.SpecialCharacteristics, RFQInputSheet.ConceptDeliverable, RFQInputSheet.PrototypesDeliverable, RFQInputSheet.PilotRunDeliverable, RFQInputSheet.PPAPDeliverable, RFQInputSheet.ProductionDeliverable, RFQInputSheet.DevelopmentSite, RFQInputSheet.ProductConfig1, RFQInputSheet.ProductConfig2, RFQInputSheet.ProductConfig3, RFQInputSheet.PatentIssues, RFQInputSheet.PatentIssues1, " _
  & "RFQInputSheet.TechAssump1, RFQInputSheet.TechAssump2, RFQInputSheet.TechAssump3, RFQInputSheet.KnownLimit1, RFQInputSheet.KnownLimit2, RFQInputSheet.KnownLimit3, RFQInputSheet.MfgAssumpt1, RFQInputSheet.MfgAssumpt2, RFQInputSheet.MfgAssumpt3, RFQInputSheet.Equipment, RFQInputSheet.TestAssumpt1, RFQInputSheet.TestAssumpt2, RFQInputSheet.TestAssumpt3, RFQInputSheet.PackagingType, RFQInputSheet.Timing, RFQInputSheet.Location , RFQInputSheet.AdditionalStaffApproval, " _
  & "RFQInputSheet.BAUD , RFQInputSheet.GeneralManager , RFQInputSheet.ManagingDirector, RFQInputSheet.DivQualMan, RFQInputSheet.DirectorEng, RFQInputSheet.ProdEgr, RFQInputSheet.QualityEng , RFQInputSheet.MfgEng, RFQInputSheet.ToolEng, RFQInputSheet.TestingReqd, RFQInputSheet.AudtiTestingReqd, RFQInputSheet.LabFixturesReqd, RFQInputSheet.GagesRqd, RFQInputSheet.RejectRate, RFQInputSheet.TestingDescription, RFQInputSheet.CustomerSpecification, RFQInputSheet.ApplicableStandards, " _
  & "RFQInputSheet.PerformanceDescription, RFQInputSheet.AsmDwgsComplete, RFQInputSheet.CompDwgComplete, RFQInputSheet.BOMComplete, RFQInputSheet.DwgDescription, RFQInputSheet.AssemblyEquipmentREqd, RFQInputSheet.TestEquipmentReqd, RFQInputSheet.TypeOfDateCodeReqd, RFQInputSheet.MfgDescription, RFQInputSheet.IncomingReq, " _
  & "RFQInputSheet.OutgoingReq, RFQInputSheet.InternalMaterialreq, RFQInputSheet.PackagingDescription, RFQInputSheet.GanttCompleted, RFQInputSheet.Approval1, RFQInputSheet.Approval2, RFQInputSheet.Approval3, RFQInputSheet.Approval4, RFQInputSheet.Approval5, RFQInputSheet.AdditonalInformation , " _
  & "RFQInputSheet.AdditionalExistingVolume, RFQInputSheet.RDTestingFixturesRequired, RFQInputSheet.RDTestingRequired, RFQInputSheet.RDDescription, RFQInputSheet.PVTestingRequired, RFQInputSheet.DVTestingRequired FROM RFQInputSheet " _
  & "FROM RFQCopyQuery")


Steven



I am a loud man with a very large hat. This means I am in charge
Reply With Quote
  #5 (permalink)  
Old March 29th, 2004, 09:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

Thank you very much Steven, that was a big help.
I have one last favor though please.
I added this to the end of the statement to give the user a chance to specify which record gets copied so now the last line of that looks like this:
  & "RFQInputSheet.AdditionalExistingVolume, RFQInputSheet.RDTestingFixturesRequired, RFQInputSheet.RDTestingRequired, RFQInputSheet.RDDescription, RFQInputSheet.PVTestingRequired, RFQInputSheet.DVTestingRequired FROM RFQInputSheet WHERE (((RFQInputSheet.RFQNumber)=[Please enter the RFQ number you want to copy])))
All lines of the code before this are exactly as you sent in your reply and the compiler didn't have any problems with it. Even since I changed it, the complier doesn't have a problem with the code, but when I run it, I get:
Quote:
quote:Run-time error '3061':
Too few parameters.Expected 2.
This is a new error to me...what does it mean and how should I fix it?

I really appreciate your help!

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #6 (permalink)  
Old March 29th, 2004, 11:13 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can not set a parameter in code. Just use an append query in Access, then use a reference to a textbox,combobox, etc. to pass the parametere, or build your string differently. Instead of creating your string like this:

WHERE (((RFQInputSheet.RFQNumber)=[Please enter the RFQ number you want to copy])))

use a reference to your textbox/combobox

".....WHERE (((RFQInputSheet.RFQNumber)=" & combo1.value & ")))"

You get the idea.



Sal
Reply With Quote
  #7 (permalink)  
Old March 29th, 2004, 03:08 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you use the QBE interface to design the append query and save it, the syntax you used will prompt the user to key in an RFQNumber.

What you need to do then is write code to respond to some event to execute the query. If you are going to use the execute method of the database object (DAO, you need the connection object for ADO), the user will not be notified of the fact that a record is about to be added. In that case, you should create a database variable, run the execute against the variable and check the database variable's recordsaffected property (you can't do this with an execute against currenttdb) to determine whether the append succeeded.

One other gotcha with what you've got there: Access 97 has an issue with decompiling when there are more than 10 consecutive line continuations. I do not know that this has ever been addressed in subsequent versions so I always keep it down to 8 continuations just in case. In your case, you would need to have a string variable and concatenate to it to keep the line continuations down to a reasonable number.

Dim strSql

strSql = "blah " & _
         "more blah " & _
         "even more " & _
         "still yet more "
strSql = strSql & "yet again more " & _
         "finally done"

You may also need to use delimiters if your RFQNumber is a string.

Given the ugly length of the syntax, it may be easier to open a recordset and addnew. My preference is to use an insert as has been suggested here but sometimes maintenance takes precedence:

Dim rstFrom As Recordset
Dim rstTo As Recordset
Dim fld As Field
Dim lngKey As Long
Dim db As DAO.Database

lngKey = InputBox ("Select an RFQ to copy")
Set rstFrom = ("Select * From RFQInputSheet Where RFQNumber = " & lngKey")
If rstFrom.EOF Then
    Msgbox "No match in From Table"
Else
    Set rstTo = ("Select * From HoldTable Where RFQNumber = " & lngKey)
    If rstTo.EOF Then
        rstTo.Addnew
        For Each fld in rstTo.Fields
            rstTo(fld.Name) = rstFrom(fld.Name)
        Next
        rstTo.Update
    Else
        MsgBox "Already exists"
    End If
End If
rstFrom.Close
Set rstFrom = Nothing
rstTo.Close
Set rstTo = Nothing
Set db = Nothing

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
  #8 (permalink)  
Old July 29th, 2017, 12:49 PM
Authorized User
Points: 116, Level: 2
Points: 116, Level: 2 Points: 116, Level: 2 Points: 116, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2009
Posts: 25
Thanks: 11
Thanked 0 Times in 0 Posts
Default Code to cut data from one table and paste in another

Does anyone have any idea how to write code to cut data from one table and paste it in another in the same database. I have a database to keep up with serial numbered items complete with a picture (which involves a multi-valued field). As I get rid of items, I would like to cut them from the table and paste them into an identical table I have built called Sold Items.

In fact there are two multi-valued fields, one containing the picture and one with a drop down to choose "Owned or Sold".
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
copy one record to another table using VBA kitaeshi Access VBA 1 June 11th, 2007 06:50 AM
Copy whole structure of table in #temp table maulik77 SQL Server 2000 2 December 21st, 2006 02:42 AM
ADO record copy and add to table Freddyfred Access 2 February 16th, 2005 10:36 PM
Trouble creating a Copy Record command on a Form Ron V Access 0 May 19th, 2004 01:36 PM
copy and append records from table-A to table B bhunter Access 6 March 9th, 2004 02:02 PM



All times are GMT -4. The time now is 07:09 AM.


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