dcsimg
Results 1 to 17 of 17

Thread: trying to insert record from one table to another keep getting errors

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    trying to insert record from one table to another keep getting errors

    here is the error message after i click the button
    Name:  k.jpg
Views: 112
Size:  9.8 KB
    and here is my code, both tables have the same structure
    Code:
    Private Sub Archive_Order_Click()
    On Error GoTo Err_Archive_Order_Click
    
        'Dim stDocName As String
        'stDocName = "Archive Order"
        'DoCmd.OpenQuery stDocName, acNormal, acEdit
        Dim Answer As Integer
        Dim numResponse As Integer
        Dim onn As String
        
        numResponse = MsgBox("Are you sure you want archive order(s)?", vbYesNo + vbQuestion, "Archive Orders?")
        onn = Me.Order_Number.Value
        
        If numResponse = vbYes Then
        DoCmd.RunSQL "Delete * From [Duptbl]"
        Me.Order_Complete.Value = True
        DoCmd.RunSQL "insert into Duptbl select * from [Order Master] where [Order Number] = '" & onn & "'"
        DoCmd.RunSQL "insert into Archive select * from [Duptbl]"
       MsgBox "deleteing " + onn + " from Order Master"
       ' Answer = MsgBox("Do you want to delete from ", vbYesNo + vbQuestion, "Archive Orders?")
       DoCmd.RunSQL "Delete * From [Order Master] Where [Order Number] = '" & onn & "'"
         
        End If
    
    Exit_Archive_Order_Click:
        Exit Sub
    
    Err_Archive_Order_Click:
        MsgBox Err.Description
        Resume Exit_Archive_Order_Click
        
    End Sub

  2. #2
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: trying to insert record from one table to another keep getting errors

    Seem to be a KEY violations.
    There are primary key on tables?

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: trying to insert record from one table to another keep getting errors

    Quote Originally Posted by gibra View Post
    Seem to be a KEY violations.
    There are primary key on tables?
    it had a primary key of the [order number] but i recently took the primary key off every table and still am getting the same error

  4. #4
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: trying to insert record from one table to another keep getting errors

    Quote Originally Posted by cubsm22p View Post
    it had a primary key of the [order number] but i recently took the primary key off every table and still am getting the same error
    Remove Primary Key isn't a good practice.
    You should instead specify the fields in INSERT INTO.

    If you get same error, then there are others problems, also.

  5. #5
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,057

    Re: trying to insert record from one table to another keep getting errors

    trying to insert record from one table to another
    to insert a record from one table to another, you need 2 tables
    so, why are you using 3 tables ?

    also: if you want to move a record from one table to another
    do so in a transaction, so you are sure that the insert as well as the delete succeeds

    question for you:
    please give the definition of
    1: Key
    2: Primary Key

    advice to you:
    put your database here (compacted and zipped)
    do not put off till tomorrow what you can put off forever

  6. #6

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: trying to insert record from one table to another keep getting errors

    ok i changed my code to this , and now im getting a data mismatch error in the VALUES part of the code not sure if i represented String, integer, and date right

    for date i used = # " * Me.Cartons_Due & " #
    for string i used = " & Me.Order_Number & " '
    for integer i used = me.[Roto Machine No]

    Code:
    Private Sub Archive_Order_Click()
    On Error GoTo Err_Archive_Order_Click
    
      Dim Answer As Integer
      Dim numResponse As Integer
        
      numResponse = MsgBox("Are you sure you want archive order(s)?", vbYesNo + vbQuestion, "Archive Orders?")
            If numResponse = vbYes Then
                Me.Order_Complete.Value = True
                DoCmd.RunSQL "Insert Into [Archive] ([Order Number],[Customer],[Received],[Need By],[Sales Rep],[Parts]," & _
                    "[Scheduled Ship],[Width],[Length],[Quantity],[Delivery Method],[Midax],[WCSS]," & _
                    "[Job Type],[Why],[Service Rep], [Why Reop],[Plate Date],[Press Date],[Press Sequence]," & _
                    "[Collator Date],[Offline Date],[Paper Due],[Carbon Due],[Die Due],[Ink Due]," & _
                    "[Repeat Order Numbers],[Misc Due],[Companion Order], [Companion Order Number], [Press],[No Parts]," & _
                    "[No Wide],[Calc Length],[Speed],[Calc Hrs],[Completed],[Remaining],[Additional Hrs]," & _
                    "[No of Inks Face],[No of Inks Back] ,[Die Number(s) Face],[Die Number(s) Liner],[Collator No:]," & _
                    "[Calc Collator Hours] , [CompletedC],[RemainingC],[Collator Delivery],[Offline Machine No]," & _
                    "[Calc roto Hrs],[Feet Per Core roto],[Order Complete],[Collator Speed],[Offline Speed]," & _
                    "[No Wide Thru Collator],[Calc Length Thru Collator],[No Wide Thru Roto],[Calc Length Thru roto]," & _
                    "[Completed roto Hrs],[Remaining roto Hrs],[Offline Delivery roto],[Exact Repeat],[Cores Due],[Cartons Due], [Ribbons Due], [Laminate Due],[Cleaning Cards Due],[Chip Due],[Tamarack]," & _
                    "[Backer Die Due] , [Quadrel], [Quadrel No], [Quadrel Date],[completed quad hrs],[Remaining Hrs- Quadrel], [Calc Length Thru quardel], [Tamarack Machine No], [Tamarack date]," & _
                    "[Remaining Hr-Tamarack],[Calc Length Thru tamarack],[completed tam hrs], [Roto Machine No],[Quad speed],[tam speed],[roto speed],[Feet Per Core tam],[Feet Per Core quad], [Offline Delivery tam],[Offline Delivery quad]," & _
                    "[No Wide Thru quad],[No Wide Thru Tam],[Special Pallets Due],[Security Tape Due],[Content Number],[Plate Length],[Number Wide Plates]) Values " & _
                    "(" & Me.Order_Number & " '," & Me.Customer & " ', # " * Me.Received & " #,# " * Me.Need_By & " #," & Me.Sales_Rep & " '," & Me.Parts & " ' ,# " * Me.Scheduled_Ship & " #,me.width,me.length,me.Quantity,me.Delivery_Method," & Me.Midax & " '," & Me.WCSS & " '," & Me.Job_Type & " '," & Me.Why & " '," & Me.Service_Rep & " '," & Me.[Why Reop] & " ',# " * Me.Plate_Date & " #,# " * Me.Press_Date & " #,me.[press sequence],# " * Me.Collator_Date & " #,);" & _
                    "(# " * Me.Offline_Date & " #,# " * Me.Paper_Due & " #,# " * Me.Carbon_Due & " #,# " * Me.Die_Due & " #,# " * Me.Ink_Due & " #," & Me.[Repeat Order Numbers] & " ',# " * Me.Misc_Due & " #," & Me.Companion_Order & " '," & Me.Companion_Order_Number & " ',me.press," & Me.No_Parts & " ',me.[no wide],me.[calc length],me.speed,me.[calc hours],me.completed,me.remaining,me.[additional hrs],me.[no of inks face],me.[no of inks back]," & Me.[Die Number(s) Face] & " '," & Me.[Die Number(s) Liner] & " ',)" & _
                    "(me.[collator no:],me.[Calc Collator Hours],me.completedc,me.remainingC," & Me.[Collator Delivery] & " ',me.[offline machine no],me.[calc roto Hrs]," & Me.Feet_Per_Core_roto & " '," & Me.Order_Complete & " ',me.[collator speed],,me.[Offline speed],me.[No Wide Thru Collator],me.[Calc Length Thru Collator]," & Me.No_Wide_Thru_Roto & " ',me.[Calc Length Thru roto],me.[Completed roto Hrs],me.[remaining roto Hrs]," & Me.[Offline Delivery roto] & " '," & Me.Exact_Repeat & " ',# " * Me.Cores_Due & " #,)" & _
                    "(# " * Me.Cartons_Due & " #,# " * Me.Ribbons_Due & " #,# " * Me.Laminate_Due & " #,# " * Me.Cleaning_Cards_Due & " #,# " * Me.Chip_Due & " #," & Me.Tamarack & " ',# " * Me.Backer_Die_Due & " #," & Me.Quadrel & " ',me.[Quadrel No],# " * Me.Quadrel_Date & " #,me[completed quad hrs],me.[Remaining Hrs- Quadrel],me.[Calc Length Thru quardel],me.[Tamarack Machine No],# " * Me.Tamarack_date & " #,me.[Remaining Hr-Tamarack],me.[Calc Length Thru tamarack],me.[completed tam hrs],me.[Roto Machine No],)" & _
                    "(me.[Quad speed],me.[tam speed],me.[roto speed]," & Me.Feet_Per_Core_Tam & " '," & Me.[Feet Per Core quad] & " '," & Me.[Offline Delivery tam] & " '," & Me.[Offline Delivery quad] & " '," & Me.[No Wide Thru quad] & " '," & Me.[No Wide Thru Tam] & " ',# " * Me.Special_Pallets_Due & " #,# " * Me.Security_Tape_Due & " #," & Me.Content_Number & " '," & Me.Plate_Length & " '," & Me.[Number Wide Plates] & " ')"
            End If
            
     Answer = MsgBox("Do you want to delete from ", vbYesNo + vbQuestion, "Archive Orders?")
            If Answer = vbYes Then
                MsgBox "deleteing " + onn + " from Order Master"
                DoCmd.RunSQL "Delete * From [Order Master] Where [Order Number] = '" & onn & "'"
            End If
     
    Exit_Archive_Order_Click:
        Exit Sub
    
    Err_Archive_Order_Click:
        MsgBox Err.Description
        Resume Exit_Archive_Order_Click
        
    End Sub

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,721

    Re: trying to insert record from one table to another keep getting errors

    you have not specified which insert is giving error

    you should not need the temporary table at all, that was suggested in a different thread to duplicate an existing order and allowing a new order number to be recorded, try copying the record from order maser directly into archive

    if you order number field is numeric then try removing the ' ' in the where clause

    NOTE it is quite difficult to read the errors listed in the image
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: trying to insert record from one table to another keep getting errors

    ok i have no exra table and made some changes till keep getting this error
    Name:  sg.PNG
Views: 35
Size:  2.6 KB
    Code:
    Private Sub Archive_Order_Click()
    On Error GoTo Err_Archive_Order_Click
    
      Dim Answer As Integer
      Dim numResponse As Integer
      Dim onn As String
      
      onn = Me.Order_Number.Value
      numResponse = MsgBox("Are you sure you want archive order(s)?", vbYesNo + vbQuestion, "Archive Orders?")
            If numResponse = vbYes Then
                Me.Order_Complete.Value = True
                DoCmd.RunSQL "Insert Into [Archive] * Values " & _
                    "(" & Me.Order_Number & " '," & Me.Customer & " ', # " * Me.Received & " #,# " * Me.Need_By & " #," & Me.Sales_Rep & " '," & Me.Parts & " ' ,# " * Me.Scheduled_Ship & " #,me.width,me.length,me.Quantity,me.Delivery_Method," & Me.Midax & " '," & Me.WCSS & " '," & Me.Job_Type & " '," & Me.Why & " '," & Me.Service_Rep & " '," & Me.[Why Reop] & " ',# " * Me.Plate_Date & " #,# " * Me.Press_Date & " #,me.[press sequence],# " * Me.Collator_Date & " #,);" & _
                    "(# " * Me.Offline_Date & " #,# " * Me.Paper_Due & " #,# " * Me.Carbon_Due & " #,# " * Me.Die_Due & " #,# " * Me.Ink_Due & " #," & Me.[Repeat Order Numbers] & " ',# " * Me.Misc_Due & " #," & Me.Companion_Order & " '," & Me.Companion_Order_Number & " ',me.press," & Me.No_Parts & " ',me.[no wide],me.[calc length],me.speed,me.[calc hours],me.completed,me.remaining,me.[additional hrs],me.[no of inks face],me.[no of inks back]," & Me.[Die Number(s) Face] & " '," & Me.[Die Number(s) Liner] & " ',)" & _
                    "(me.[collator no:],me.[Calc Collator Hours],me.completedc,me.remainingC," & Me.[Collator Delivery] & " ',me.[offline machine no],me.[calc roto Hrs]," & Me.Feet_Per_Core_roto & " '," & Me.Order_Complete & " ',me.[collator speed],,me.[Offline speed],me.[No Wide Thru Collator],me.[Calc Length Thru Collator]," & Me.No_Wide_Thru_Roto & " ',me.[Calc Length Thru roto],me.[Completed roto Hrs],me.[remaining roto Hrs]," & Me.[Offline Delivery roto] & " '," & Me.Exact_Repeat & " ',# " * Me.Cores_Due & " #,)" & _
                    "(# " * Me.Cartons_Due & " #,# " * Me.Ribbons_Due & " #,# " * Me.Laminate_Due & " #,# " * Me.Cleaning_Cards_Due & " #,# " * Me.Chip_Due & " #," & Me.Tamarack & " ',# " * Me.Backer_Die_Due & " #," & Me.Quadrel & " ',me.[Quadrel No],# " * Me.Quadrel_Date & " #,me[completed quad hrs],me.[Remaining Hrs- Quadrel],me.[Calc Length Thru quardel],me.[Tamarack Machine No],# " * Me.Tamarack_date & " #,me.[Remaining Hr-Tamarack],me.[Calc Length Thru tamarack],me.[completed tam hrs],me.[Roto Machine No],)" & _
                    "(me.[Quad speed],me.[tam speed],me.[roto speed]," & Me.Feet_Per_Core_Tam & " '," & Me.[Feet Per Core quad] & " '," & Me.[Offline Delivery tam] & " '," & Me.[Offline Delivery quad] & " '," & Me.[No Wide Thru quad] & " '," & Me.[No Wide Thru Tam] & " ',# " * Me.Special_Pallets_Due & " #,# " * Me.Security_Tape_Due & " #," & Me.Content_Number & " '," & Me.Plate_Length & " '," & Me.[Number Wide Plates] & " ')"
            End If
            
     Answer = MsgBox("Do you want to delete from ", vbYesNo + vbQuestion, "Archive Orders?")
            If Answer = vbYes Then
                MsgBox "deleteing " + onn + " from Order Master"
                DoCmd.RunSQL "Delete * From [Order Master] Where [Order Number] = '" & onn & "'"
            End If
     
    Exit_Archive_Order_Click:
        Exit Sub
    
    Err_Archive_Order_Click:
        MsgBox Err.Description
        Resume Exit_Archive_Order_Click
        
    End Sub

  9. #9
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,293

    Re: trying to insert record from one table to another keep getting errors

    Just to point out in the first post, it says 1 field for validation rule.

    What rules have you applied to the fields in the tables?
    Any constraints?
    Or perhaps you marked a field cannot be duplicated (like the primary key field).
    You also mentioned you took the primary key off, but the non duplicated value is probably still there.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  10. #10

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: trying to insert record from one table to another keep getting errors

    here is what the text data type is in every table
    Name:  kuyut.PNG
Views: 33
Size:  23.2 KB
    he is ever number data type
    Name:  kkk.PNG
Views: 33
Size:  17.1 KB
    Text data type
    Name:  gfj.PNG
Views: 32
Size:  22.2 KB
    Yes/No data type
    Name:  uyil.PNG
Views: 33
Size:  10.6 KB
    Memo data type
    Name:  gj.PNG
Views: 33
Size:  22.9 KB

    i think the error might be in the code where i have it red, how do i represent numbers on the sql code
    Code:
     Values " & _
                    "(" & Me.Order_Number & " '," & Me.Customer & " ', # " * Me.Received & " #,# " * Me.Need_By & " #," & Me.Sales_Rep & " '," & Me.Parts & " ' ,# " * Me.Scheduled_Ship & " #,me.width,me.length,me.Quantity,me.Delivery_Method," & Me.Midax & " '," & Me.WCSS & " '," & Me.Job_Type & " '," & Me.Why & " '," & Me.Service_Rep & " '," & Me.[Why Reop] & " ',# " * Me.Plate_Date & " #,# " * Me.Press_Date & " #,me.[press sequence],# " * Me.Collator_Date & " #,);" & _
                    "(# " * Me.Offline_Date & " #,# " * Me.Paper_Due & " #,# " * Me.Carbon_Due & " #,# " * Me.Die_Due & " #,# " * Me.Ink_Due & " #," & Me.[Repeat Order Numbers] & " ',# " * Me.Misc_Due & " #," & Me.Companion_Order & " '," & Me.Companion_Order_Number & " ',me.press," & Me.No_Parts & " ',me.[no wide],me.[calc length],me.speed,me.[calc hours],me.completed,me.remaining,me.[additional hrs],me.[no of inks face],me.[no of inks back]," & Me.[Die Number(s) Face] & " '," & Me.[Die Number(s) Liner] & " ',)" & _
                    "(me.[collator no:],me.[Calc Collator Hours],me.completedc,me.remainingC," & Me.[Collator Delivery] & " ',me.[offline machine no],me.[calc roto Hrs]," & Me.Feet_Per_Core_roto & " '," & Me.Order_Complete & " ',me.[collator speed],,me.[Offline speed],me.[No Wide Thru Collator],me.[Calc Length Thru Collator]," & Me.No_Wide_Thru_Roto & " ',me.[Calc Length Thru roto],me.[Completed roto Hrs],me.[remaining roto Hrs]," & Me.[Offline Delivery roto] & " '," & Me.Exact_Repeat & " ',# " * Me.Cores_Due & " #,)" & _
                    "(# " * Me.Cartons_Due & " #,# " * Me.Ribbons_Due & " #,# " * Me.Laminate_Due & " #,# " * Me.Cleaning_Cards_Due & " #,# " * Me.Chip_Due & " #," & Me.Tamarack & " ',# " * Me.Backer_Die_Due & " #," & Me.Quadrel & " ',me.[Quadrel No],# " * Me.Quadrel_Date & " #,me[completed quad hrs],me.[Remaining Hrs- Quadrel],me.[Calc Length Thru quardel],me.[Tamarack Machine No],# " * Me.Tamarack_date & " #,me.[Remaining Hr-Tamarack],me.[Calc Length Thru tamarack],me.[completed tam hrs],me.[Roto Machine No],)" & _
                    "(me.[Quad speed],me.[tam speed],me.[roto speed]," & Me.Feet_Per_Core_Tam & " '," & Me.[Feet Per Core quad] & " '," & Me.[Offline Delivery tam] & " '," & Me.[Offline Delivery quad] & " '," & Me.[No Wide Thru quad] & " '," & Me.[No Wide Thru Tam] & " ',# " * Me.Special_Pallets_Due & " #,# " * Me.Security_Tape_Due & " #," & Me.Content_Number & " '," & Me.Plate_Length & " '," & Me.[Number Wide Plates] & " ')"

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,721

    Re: trying to insert record from one table to another keep getting errors

    how do i represent numbers on the sql code
    if the database field is a text (even though it contains digits), then i would believe the number as a string should be enclosed in ' ' , if the database field is a numeric field then no need any quotes or anything needed, dates should be enclosed in # #
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: trying to insert record from one table to another keep getting errors

    Quote Originally Posted by westconn1 View Post
    if the database field is a text (even though it contains digits), then i would believe the number as a string should be enclosed in ' ' , if the database field is a numeric field then no need any quotes or anything needed, dates should be enclosed in # #
    what should i use wih the yes/no feilds

  13. #13

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: trying to insert record from one table to another keep getting errors

    Name:  ku.PNG
Views: 31
Size:  7.2 KB
    got this error after using this code
    Code:
     DoCmd.RunSQL "Insert Into [Archive] * Values " & _
    '                "('me.Order_Number',#me.[Date Entered Into Alvin]#,'Me.Customer',#Me.Received#,#Me.Need_By#,);" & _
    '                "('Me.Sales_Rep','Me.Parts',#Me.Scheduled_Ship#,me.width,me.length,me.Quantity,);" & _
    '                "(me.Delivery_Method,'Me.Midax','Me.WCSS','Me.Job_Type','Me.Why',);" & _
    '                "('Me.Service_Rep','Me.[Why Reop]',#Me.Plate_Date#,#Me.Press_Date#,);" & _
    '                "(me.[press sequence],#Me.Collator_Date#,#Me.Offline_Date#,#Me.Paper_Due#,);" & _
    '                "(#Me.Carbon_Due#,#Me.Die_Due#,#Me.Ink_Due#,'Me.[Repeat Order Numbers]',);" & _
    '                "(#Me.Misc_Due#,'Me.Companion_Order','Me.Companion_Order_Number',me.press,);" & _
    '                "('Me.No_Parts',me.[no wide],me.[calc length],me.speed,me.[calc hours],me.completed,me.remaining,);" & _
    '                "(me.[additional hrs],me.[no of inks face],me.[no of inks back],'Me.[Die Number(s) Face]',);" & _
    '                "('Me.[Die Number(s) Liner]',me.[collator no:],me.[Calc Collator Hours],me.completedc,me.remainingC,);" & _
    '                "('Me.[Collator Delivery]',me.[offline machine no],me.[calc roto Hrs],'Me.Feet_Per_Core_roto',);" & _
    '                "('Me.Order_Complete',me.[collator speed],me.[Offline speed],me.[No Wide Thru Collator],);" & _
    '                "(me.[Calc Length Thru Collator],'Me.No_Wide_Thru_Roto',me.[Calc Length Thru roto],me.[Completed roto Hrs],);" & _
    '                "(me.[remaining roto Hrs],'Me.[Offline Delivery roto]','Me.Exact_Repeat',#Me.Cores_Due#,)" & _
    '                "(#Me.Cartons_Due#,#Me.Ribbons_Due#,#Me.Laminate_Due#,#Me.Cleaning_Cards_Due#,);" & _
    '                "(#Me.Chip_Due#,'Me.Tamarack',#Me.Backer_Die_Due#," & Me.Quadrel & " ',me.[Quadrel No],);" & _
    '                "(#Me.Quadrel_Date#,me[completed quad hrs],me.[Remaining Hrs- Quadrel],me.[Calc Length Thru quardel],);" & _
    '                "(me.[Tamarack Machine No],#Me.Tamarack_date#,me.[Remaining Hr-Tamarack],me.[Calc Length Thru tamarack],);" & _
    '                "(me.[completed tam hrs],me.[Roto Machine No],'me.[Quad speed]',me.[tam speed],me.[roto speed],);" & _
    '                "('Me.Feet_Per_Core_Tam','Me.[Feet Per Core quad]','Me.[Offline Delivery tam]',);" & _
    '                "('Me.[Offline Delivery quad]','Me.[No Wide Thru quad]','Me.[No Wide Thru Tam]',);" & _
    '                "('Me.Special_Pallets_Due','Me.Security_Tape_Due','Me.Content_Number',);" & _
    '                "('Me.Plate_Length','Me.[Number Wide Plates]')"

  14. #14
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: trying to insert record from one table to another keep getting errors

    You're copying from one table, to another, right? Then why are we going through this? I think something has gotten lost here and it's being over complicated.
    All you should need to do is select from one table and insert into the the others... it looks like this:
    Code:
    Insert into TableA(Field1, Field2, Field3, field4) Select Field1, Field2, Field3, Field4 From AnotherTable
    Boom... plain and simple. as long as the data types of fields 1-4 in the two tables are the same, there's no problem. No quotes. No tick marks. No pounds. Nothing.

    You almost had it in post 1 but you were using select * (and people wonder why I'm adamant about not using Select *... here you go... this is one of them) ... the ONLY way that EVER works is if the fields in the two tables are EXACTLY the same AND IN THE EXACT SAME ORDER... and there's no guarantee that they will be. That's probably why you got the conversion error initially. A field probably got moved around and the two tables weren't in synch. If you list out all the fields between the two (and yeah, I can see that that is a lot of fields in this case, hey we've all had to bear that crutch at one time or another) then you can be guaranteed that the fields will be in the right order and won't get crossed up somewhere.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: trying to insert record from one table to another keep getting errors

    Quote Originally Posted by cubsm22p View Post
    Name:  ku.PNG
Views: 31
Size:  7.2 KB
    got this error after using this code
    Code:
     DoCmd.RunSQL "Insert Into [Archive] * Values " & _
    '                "('me.Order_Number',#me.[Date Entered Into Alvin]#,'Me.Customer',#Me.Received#,#Me.Need_By#,);" & _
    '                "('Me.Sales_Rep','Me.Parts',#Me.Scheduled_Ship#,me.width,me.length,me.Quantity,);" & _
    '                "(me.Delivery_Method,'Me.Midax','Me.WCSS','Me.Job_Type','Me.Why',);" & _
    '                "('Me.Service_Rep','Me.[Why Reop]',#Me.Plate_Date#,#Me.Press_Date#,);" & _
    '                "(me.[press sequence],#Me.Collator_Date#,#Me.Offline_Date#,#Me.Paper_Due#,);" & _
    '                "(#Me.Carbon_Due#,#Me.Die_Due#,#Me.Ink_Due#,'Me.[Repeat Order Numbers]',);" & _
    '                "(#Me.Misc_Due#,'Me.Companion_Order','Me.Companion_Order_Number',me.press,);" & _
    '                "('Me.No_Parts',me.[no wide],me.[calc length],me.speed,me.[calc hours],me.completed,me.remaining,);" & _
    '                "(me.[additional hrs],me.[no of inks face],me.[no of inks back],'Me.[Die Number(s) Face]',);" & _
    '                "('Me.[Die Number(s) Liner]',me.[collator no:],me.[Calc Collator Hours],me.completedc,me.remainingC,);" & _
    '                "('Me.[Collator Delivery]',me.[offline machine no],me.[calc roto Hrs],'Me.Feet_Per_Core_roto',);" & _
    '                "('Me.Order_Complete',me.[collator speed],me.[Offline speed],me.[No Wide Thru Collator],);" & _
    '                "(me.[Calc Length Thru Collator],'Me.No_Wide_Thru_Roto',me.[Calc Length Thru roto],me.[Completed roto Hrs],);" & _
    '                "(me.[remaining roto Hrs],'Me.[Offline Delivery roto]','Me.Exact_Repeat',#Me.Cores_Due#,)" & _
    '                "(#Me.Cartons_Due#,#Me.Ribbons_Due#,#Me.Laminate_Due#,#Me.Cleaning_Cards_Due#,);" & _
    '                "(#Me.Chip_Due#,'Me.Tamarack',#Me.Backer_Die_Due#," & Me.Quadrel & " ',me.[Quadrel No],);" & _
    '                "(#Me.Quadrel_Date#,me[completed quad hrs],me.[Remaining Hrs- Quadrel],me.[Calc Length Thru quardel],);" & _
    '                "(me.[Tamarack Machine No],#Me.Tamarack_date#,me.[Remaining Hr-Tamarack],me.[Calc Length Thru tamarack],);" & _
    '                "(me.[completed tam hrs],me.[Roto Machine No],'me.[Quad speed]',me.[tam speed],me.[roto speed],);" & _
    '                "('Me.Feet_Per_Core_Tam','Me.[Feet Per Core quad]','Me.[Offline Delivery tam]',);" & _
    '                "('Me.[Offline Delivery quad]','Me.[No Wide Thru quad]','Me.[No Wide Thru Tam]',);" & _
    '                "('Me.Special_Pallets_Due','Me.Security_Tape_Due','Me.Content_Number',);" & _
    '                "('Me.Plate_Length','Me.[Number Wide Plates]')"
    Database - Why should I use Parameters instead of putting values into my SQL string?-VBForums
    http://www.vbforums.com/showthread.p...-my-SQL-string

  16. #16
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,772

    Re: trying to insert record from one table to another keep getting errors

    OK... hold on... stop... we need to back up here folks and identify the problem. As stated back in post #1, the original issue was that the OP was trying to copy data from one table into another... is that still true? If that's the case, then this is purely a SQL issue and everything regarding dates, pounds, strings, ticks, and parameters is irrelevant.

    I don't know how we jumped from the code in #1 to the code in #6, but what's in #6 isn't even close to working if it's supposed to solve the original issue.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,721

    Re: trying to insert record from one table to another keep getting errors

    I don't know how we jumped from the code in #1 to the code in #6
    he was trying as in post #6 previously (in another thread) to duplicate existing records, someone posted code similar to post #1 for that, seems like he is switching to try anything that might work
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width