Code to copy a record from one table to another.

lryckman · March 25th, 2004, 09:20 AM

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!

Steven · March 25th, 2004, 09:06 PM

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

lryckman · March 26th, 2004, 10:25 AM

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!

Steven · March 29th, 2004, 12:55 AM

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

lryckman · March 29th, 2004, 09:17 AM

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!

sal · March 29th, 2004, 11:13 AM

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

jurgenw · March 29th, 2004, 03:08 PM

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
[email protected]

billy1r · July 29th, 2017, 12:49 PM

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".