Results 1 to 6 of 6

Thread: [RESOLVED] Running SQL in VBA

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Resolved [RESOLVED] Running SQL in VBA

    Hello guys i have a duplicate button with some SQL code embedded into it and i cannot get the sql code to execute here is a copy of the code
    Code:
    Private Sub Duplicate__Record_Click()
    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
    onn = Format(onn + 100000000, String(12, "0"))
    
    MsgBox onn
    
    
    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 " & _
        "(''" & 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," & _
        "[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)"
        CurrentDb.Execute strSQL
       
    End Sub

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

    Re: Running SQL in VBA

    "(''" & onn & "'',
    i do not believe you should have double ' before and after the variable

    as this is a straight sql question, it may be better to ask a moderator to move this to the database forum, where someone better at sql may see the problem

    all i would do is start a much more basic sql when that works i would keep adding to it until i found the part that causes the problem
    Last edited by westconn1; Feb 15th, 2018 at 02:37 AM.
    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

  3. #3
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Running SQL in VBA

    if you have zillions of fields in a table, as you seem to have
    and you want to duplicate a record in that table using the "insert into fieldlist values fieldlist" method
    there will be a high probability of a typing error

    but there is a very simple way to duplicate a record with 1 field different
    say you want to duplicate a record in table1, but with just 1 field different
    create a table say table2 that has the exact same structure as table1
    and then execute the following 4 queries

    Code:
    delete * from table2
    insert into table2 select * from table1 where somefield=somevalue
    update table2 set somefield=someothervalue where somefield=somevalue
    insert into table1 select * from table2 where somefield=someothervalue
    do not put off till tomorrow what you can put off forever

  4. #4
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Running SQL in VBA

    now that i reread it,i realize that
    Code:
     
    insert into table1 select * from table2 where somefield=someothervalue
    can be replaced with:
    Code:
    insert into table1 select * from table2
    because there never will be more than 1 record in table2
    do not put off till tomorrow what you can put off forever

  5. #5

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: Running SQL in VBA

    here is what i have so far
    Code:
    Private Sub Duplicate__Record_Click()
    
    DoCmd.RunSQL "Delete * From Duptbl"
    DoCmd.RunSQL "insert into Duptbl select * from [Order Master] where [Order Number] = [order number].Value"
    onn = [order number].Value
    onn = Format(onn + 100000000, String(12, "0"))
    DoCmd.RunSQL "update Duptbl set [Order Number] = onn "
    DoCmd.RunSQL "inser into [Order Master] select * from Duptbl"

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

    Re: [RESOLVED] Running SQL in VBA

    here is what i have so far
    so this works or not? if not what happens?
    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