Results 1 to 11 of 11

Thread: [RESOLVED] creating duplicate record VBA

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Resolved [RESOLVED] creating duplicate record VBA

    i want to create a button to duplicate record shown on the form every field to be the same except the Key which is [Order Number]
    this will have to be changed according to the first 4 numbers if the first four numbers are 0000-XXXX-XXXX then the new [Order Number] for that record should be 0001-XXXX-XXXX, same as if the [Order Number] is 0001-XXXX-XXXX then the new [Order Number] should be 0002-XXXX-XXXX

    Name:  vbarecorfd.jpg
Views: 258
Size:  30.2 KB

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: creating duplicate record VBA

    If it's VBA, you need to ask in the Office Development forum

  3. #3
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: creating duplicate record VBA

    Thread moved from visual basic.net forum to office development forum.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: creating duplicate record VBA

    Here is some code i wrote for duplicating a record in the form and changes just the order number but it giving an error in the Value ('0001-####-####'
    Code:
    If [Order Number].Value = "0000-####-####" Then
    StrSQL = " Insert Into [Order Master] ([Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
        "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
        "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
        "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
        "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
        "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
        "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
        "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
        "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
        "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
        "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due],[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
        "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs],[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
        "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed],[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
        "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number],[Order Master].[Plate Length],[Order Master].[Number Wide Plates]) Values " & _
        "('0001-####-####',[Customer].value,[Received].value,[Need By].value,[Sales Rep].value,[Parts].value,[Scheduled Ship].value,[Width].value,[Length].value,[Quantity].value,[Delivery Method].value,[Midax].value,[WCSS].value," & _
        "[Job Type].value,[Why].value,[Service Rep].value,[Why Reop].value,[Plate Date].value,[Press Date].value,[Press Sequence].value,[Collator Date].value,[Offline Date].value,[Paper Due].value,[Carbon Due].value,[Die Due].value,[Ink Due].value," & _
        "[Repeat Order Numbers].value,[Misc Due].value,[Companion Order].value,[Companion Order Number].value,[Press].value,[No Parts].value,[No Wide].value,[Calc Length].value,[Speed].value,[Calc Hrs].value,[Completed].value,[Remaining].value,[Additional Hrs].value," & _
        "[No of Inks Face].value,[No of Inks Back].value,[Die Number(s) Face].value,[Die Number(s) Liner].value,[Collator No:].value,[Calc Collator Hours].value,[CompletedC].value,[RemainingC].value,[Collator Delivery].value,[Offline Machine No].value," & _
        "[Calc roto Hrs].value,[Feet Per Core roto].value,[Order Complete].value,[Collator Speed].value,[Offline Speed].value,[No Wide Thru Collator].value,[Calc Length Thru Collator].value,[No Wide Thru Roto].value,[Calc Length Thru roto].value," & _
        "[Completed roto Hrs].value,[Remaining roto Hrs].value,[Offline Delivery roto].value,[Exact Repeat].value,[Cores Due].value,[Cartons Due].value,[Ribbons Due].value,[Laminate Due].value,[Cleaning Cards Due].value,[Chip Due].value,[Tamarack].value," & _
        "[Backer Die Due].value,[Quadrel].value,[Quadrel No].value,[Quadrel Date].value,[completed quad hrs].value,[Remaining Hrs- Quadrel].value,[Calc Length Thru quardel].value,[Tamarack Machine No].value,[Tamarack date].value," & _
        "[Remaining Hr-Tamarack].value,[Calc Length Thru tamarack].value,[completed tam hrs].value,[Roto Machine No].value,[Quad speed].value,[tam speed].value,[roto speed].value,[Feet Per Core tam].value,[Feet Per Core quad].value,[Offline Delivery tam].value,[Offline Delivery quad].value," & _
        "[No Wide Thru quad].value,[No Wide Thru Tam].value,[Special Pallets Due].value,[Security Tape Due].value,[Content Number].value,[Plate Length].value,[Number Wide Plates].value"
    ElseIf [Order Number].Value = "0001-####-####" Then
        StrSQL = " Insert Into [Order Master] ([Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
        "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
        "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
        "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
        "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
        "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
        "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
        "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
        "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
        "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
        "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due],[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
        "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs],[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
        "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed],[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
        "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number],[Order Master].[Plate Length],[Order Master].[Number Wide Plates]) Values " & _
        "('0002-####-####',[Customer].value,[Received].value,[Need By].value,[Sales Rep].value,[Parts].value,[Scheduled Ship].value,[Width].value,[Length].value,[Quantity].value,[Delivery Method].value,[Midax].value,[WCSS].value," & _
        "[Job Type].value,[Why].value,[Service Rep].value,[Why Reop].value,[Plate Date].value,[Press Date].value,[Press Sequence].value,[Collator Date].value,[Offline Date].value,[Paper Due].value,[Carbon Due].value,[Die Due].value,[Ink Due].value," & _
        "[Repeat Order Numbers].value,[Misc Due].value,[Companion Order].value,[Companion Order Number].value,[Press].value,[No Parts].value,[No Wide].value,[Calc Length].value,[Speed].value,[Calc Hrs].value,[Completed].value,[Remaining].value,[Additional Hrs].value," & _
        "[No of Inks Face].value,[No of Inks Back].value,[Die Number(s) Face].value,[Die Number(s) Liner].value,[Collator No:].value,[Calc Collator Hours].value,[CompletedC].value,[RemainingC].value,[Collator Delivery].value,[Offline Machine No].value," & _
        "[Calc roto Hrs].value,[Feet Per Core roto].value,[Order Complete].value,[Collator Speed].value,[Offline Speed].value,[No Wide Thru Collator].value,[Calc Length Thru Collator].value,[No Wide Thru Roto].value,[Calc Length Thru roto].value," & _
        "[Completed roto Hrs].value,[Remaining roto Hrs].value,[Offline Delivery roto].value,[Exact Repeat].value,[Cores Due].value,[Cartons Due].value,[Ribbons Due].value,[Laminate Due].value,[Cleaning Cards Due].value,[Chip Due].value,[Tamarack].value," & _
        "[Backer Die Due].value,[Quadrel].value,[Quadrel No].value,[Quadrel Date].value,[completed quad hrs].value,[Remaining Hrs- Quadrel].value,[Calc Length Thru quardel].value,[Tamarack Machine No].value,[Tamarack date].value," & _
        "[Remaining Hr-Tamarack].value,[Calc Length Thru tamarack].value,[completed tam hrs].value,[Roto Machine No].value,[Quad speed].value,[tam speed].value,[roto speed].value,[Feet Per Core tam].value,[Feet Per Core quad].value,[Offline Delivery tam].value,[Offline Delivery quad].value," & _
        "[No Wide Thru quad].value,[No Wide Thru Tam].value,[Special Pallets Due].value,[Security Tape Due].value,[Content Number].value,[Plate Length].value,[Number Wide Plates].value"
    ElseIf [Order Number].Value = "0002-####-####" Then
        StrSQL = " Insert Into [Order Master] ([Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
        "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
        "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
        "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
        "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
        "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
        "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
        "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
        "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
        "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
        "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due],[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
        "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs],[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
        "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed],[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
        "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number],[Order Master].[Plate Length],[Order Master].[Number Wide Plates]) Values " & _
        "('0003-####-####',[Customer].value,[Received].value,[Need By].value,[Sales Rep].value,[Parts].value,[Scheduled Ship].value,[Width].value,[Length].value,[Quantity].value,[Delivery Method].value,[Midax].value,[WCSS].value," & _
        "[Job Type].value,[Why].value,[Service Rep].value,[Why Reop].value,[Plate Date].value,[Press Date].value,[Press Sequence].value,[Collator Date].value,[Offline Date].value,[Paper Due].value,[Carbon Due].value,[Die Due].value,[Ink Due].value," & _
        "[Repeat Order Numbers].value,[Misc Due].value,[Companion Order].value,[Companion Order Number].value,[Press].value,[No Parts].value,[No Wide].value,[Calc Length].value,[Speed].value,[Calc Hrs].value,[Completed].value,[Remaining].value,[Additional Hrs].value," & _
        "[No of Inks Face].value,[No of Inks Back].value,[Die Number(s) Face].value,[Die Number(s) Liner].value,[Collator No:].value,[Calc Collator Hours].value,[CompletedC].value,[RemainingC].value,[Collator Delivery].value,[Offline Machine No].value," & _
        "[Calc roto Hrs].value,[Feet Per Core roto].value,[Order Complete].value,[Collator Speed].value,[Offline Speed].value,[No Wide Thru Collator].value,[Calc Length Thru Collator].value,[No Wide Thru Roto].value,[Calc Length Thru roto].value," & _
        "[Completed roto Hrs].value,[Remaining roto Hrs].value,[Offline Delivery roto].value,[Exact Repeat].value,[Cores Due].value,[Cartons Due].value,[Ribbons Due].value,[Laminate Due].value,[Cleaning Cards Due].value,[Chip Due].value,[Tamarack].value," & _
        "[Backer Die Due].value,[Quadrel].value,[Quadrel No].value,[Quadrel Date].value,[completed quad hrs].value,[Remaining Hrs- Quadrel].value,[Calc Length Thru quardel].value,[Tamarack Machine No].value,[Tamarack date].value," & _
        "[Remaining Hr-Tamarack].value,[Calc Length Thru tamarack].value,[completed tam hrs].value,[Roto Machine No].value,[Quad speed].value,[tam speed].value,[roto speed].value,[Feet Per Core tam].value,[Feet Per Core quad].value,[Offline Delivery tam].value,[Offline Delivery quad].value," & _
        "[No Wide Thru quad].value,[No Wide Thru Tam].value,[Special Pallets Due].value,[Security Tape Due].value,[Content Number].value,[Plate Length].value,[Number Wide Plates].value"
    ElseIf [Order Number].Value = "0003-####-####" Then
        StrSQL = " Insert Into [Order Master] ([Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
        "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
        "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
        "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
        "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
        "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
        "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
        "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
        "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
        "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
        "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due],[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
        "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs],[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
        "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed],[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
        "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number],[Order Master].[Plate Length],[Order Master].[Number Wide Plates]) Values " & _
        "('0004-####-####',[Customer].value,[Received].value,[Need By].value,[Sales Rep].value,[Parts].value,[Scheduled Ship].value,[Width].value,[Length].value,[Quantity].value,[Delivery Method].value,[Midax].value,[WCSS].value," & _
        "[Job Type].value,[Why].value,[Service Rep].value,[Why Reop].value,[Plate Date].value,[Press Date].value,[Press Sequence].value,[Collator Date].value,[Offline Date].value,[Paper Due].value,[Carbon Due].value,[Die Due].value,[Ink Due].value," & _
        "[Repeat Order Numbers].value,[Misc Due].value,[Companion Order].value,[Companion Order Number].value,[Press].value,[No Parts].value,[No Wide].value,[Calc Length].value,[Speed].value,[Calc Hrs].value,[Completed].value,[Remaining].value,[Additional Hrs].value," & _
        "[No of Inks Face].value,[No of Inks Back].value,[Die Number(s) Face].value,[Die Number(s) Liner].value,[Collator No:].value,[Calc Collator Hours].value,[CompletedC].value,[RemainingC].value,[Collator Delivery].value,[Offline Machine No].value," & _
        "[Calc roto Hrs].value,[Feet Per Core roto].value,[Order Complete].value,[Collator Speed].value,[Offline Speed].value,[No Wide Thru Collator].value,[Calc Length Thru Collator].value,[No Wide Thru Roto].value,[Calc Length Thru roto].value," & _
        "[Completed roto Hrs].value,[Remaining roto Hrs].value,[Offline Delivery roto].value,[Exact Repeat].value,[Cores Due].value,[Cartons Due].value,[Ribbons Due].value,[Laminate Due].value,[Cleaning Cards Due].value,[Chip Due].value,[Tamarack].value," & _
        "[Backer Die Due].value,[Quadrel].value,[Quadrel No].value,[Quadrel Date].value,[completed quad hrs].value,[Remaining Hrs- Quadrel].value,[Calc Length Thru quardel].value,[Tamarack Machine No].value,[Tamarack date].value," & _
        "[Remaining Hr-Tamarack].value,[Calc Length Thru tamarack].value,[completed tam hrs].value,[Roto Machine No].value,[Quad speed].value,[tam speed].value,[roto speed].value,[Feet Per Core tam].value,[Feet Per Core quad].value,[Offline Delivery tam].value,[Offline Delivery quad].value," & _
        "[No Wide Thru quad].value,[No Wide Thru Tam].value,[Special Pallets Due].value,[Security Tape Due].value,[Content Number].value,[Plate Length].value,[Number Wide Plates].value"
    End Sub

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: creating duplicate record VBA

    if only the order number is different, i would not think you need all the different sql strings

    i wold get the update order number first into a variable, then use that variable value in the sql
    Code:
    onn = [order number].Value
    s = Split(onn, "-")
    s(0) = Format(s(0) + 1, "0000")
    onn = Join(s, "-")
    Code:
        "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number],[Order Master].[Plate Length],[Order Master].[Number Wide Plates]) Values " & _
        "('" & onn & "',[Customer].value,[Received].value,[Need By].value,[Sales Rep].value,[Parts].value,[Scheduled Ship].value,[Width].value,[Length].value,[Quantity].value,[Delivery Method].value,[Midax].value,[WCSS].value," & _
    it appeared to me that maybe you had an opening bracket ( for values, but no matching closing bracket, but i might just have missed it
    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

  6. #6

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: creating duplicate record VBA

    i got it to run but the numbers i am getting r not what i want here i a screen shot

    Code:
    onn = [Order Number].Value
    MsgBox "onn = " + onn
    s = Split(onn, "-")
    s(0) = Format(s(0) + 1, "0000")
    onn = Join(s, "-")
    MsgBox "onn change = " + onn
    Name:  msbbox1.PNG
Views: 110
Size:  7.1 KB
    Name:  msgbox2.PNG
Views: 129
Size:  5.9 KB
    Last edited by cubsm22p; Feb 14th, 2018 at 02:28 PM.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: creating duplicate record VBA

    it would appear that the number is not formatted the same as in the previous examples, no - s, as in "0000-####-####"

    if it is just a numeric value, as in the msgbox then the code would have to be different, try a msgbox [order number].value, which of course should be the same as the first image above, but maybe the value is being changed when assigned to the variable, or the formatting is in the access control, not the table

    you can try this alternative to see if it works
    Code:
    onn = [order number].Value
    ''onn = "000010101707"
    onn = Format(onn + 100000000, String(12, "0"))
    MsgBox onn
    Last edited by westconn1; Feb 14th, 2018 at 03:25 PM.
    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: creating duplicate record VBA

    Quote Originally Posted by westconn1 View Post
    it would appear that the number is not formatted the same as in the previous examples, no - s, as in "0000-####-####"

    if it is just a numeric value, as in the msgbox then the code would have to be different, try a msgbox [order number].value, which of course should be the same as the first image above, but maybe the value is being changed when assigned to the variable, or the formatting is in the access control, not the table
    this code seemed to do the trick now i have to some how use a statement to see if the first four number are 0000 or 0001 or 0002 or 0003
    Code:
    Dim fullpath As String
    Dim dirs(0 To 2) As String
    
    Dim s As Variant
    Dim onn As String
    Dim strSQL As String
    
    onn = [Order Number].Value
    s = Split(onn, "-")
    dirs(1) = Format(s(0) + 100000000)
    dirs(0) = "000"
    fullpath = Join(dirs, "")
    MsgBox "fullpath = " + fullpath

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: creating duplicate record VBA

    see the edit on my previous post
    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

  10. #10

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: creating duplicate record VBA

    This worked now im tring to get my SQL code to run correctly
    Code:
    onn = [order number].Value
    If onn Like "0000********" Then
    onn = Format(onn + 100000000, String(12, "0"))
    ElseIf onn Like "0001********" Then
    onn = Format(onn + 200000000, String(12, "0"))
    ElseIf onn Like "0002********" Then
    onn = Format(onn + 300000000, String(12, "0"))
    If onn Like "0003********" Then
    onn = Format(onn + 400000000, String(12, "0"))
    ElseIf onn Like "0004********" Then
    onn = Format(onn + 500000000, String(12, "0"))
    ElseIf onn Like "0005********" Then
    onn = Format(onn + 600000000, String(12, "0"))
    End If
    MsgBox onn
    Last edited by cubsm22p; Feb 14th, 2018 at 04:26 PM.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] creating duplicate record VBA

    why would you need all the elsifs, and surly if in a case of 0003********** + the 40000000 would give 0007***********
    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
  •  



Click Here to Expand Forum to Full Width