VBA SQL Insert changing 2 fields (1 Viewer)

Gazza1709

New member
Local time
Today, 01:55
Joined
Sep 9, 2018
Messages
7
Hi all,

I have been trying to get an SQL Insert statement to duplicate a form and subform but changing two fields in one of the tables.

I got the following code from allen browne`s site (Can`t post link as first post with this username) after searching around.


Code:
Private Sub BtnOrder_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Integer       'Primary key value of the new record.
    Dim NewOrderTypeID As Integer
    
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !AccountID = Me.AccountID
                !OrderDate = Me.OrderDate
                !DueDate = Me.DueDate
                !Reference = Me.Reference
                !OrderStatusID = "3"
                !OrderTypeID = "5"
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !OrderHeaderID
            NewOrderTypeID = 5
            'Duplicate the related records: append query.
            If Me.[FrmOrderDetails].Form.RecordsetClone.RecordCount > 0 Then
            
            Stop
            
                strSql = "INSERT INTO [TblOrderDetails] (OrderDetailsID, OrderHeaderID, OrderTypeID, ProductID, Discount, Quantity, ListPrice)" & _
                    "VALUES(OrderDetailsID," & lngID & "," & NewOrderTypeID & ",ProductID,Discount,Quantity,ListPrice)" & _
                    "SELECT " & lngID & " As NewID, OrderHeaderID, OrderTypeID, ProductID, Discount, Quantity, ListPrice " & _
                    "FROM [TblOrderDetails] WHERE OrderHeaderID = " & Me.OrderHeaderID & ";"
            Debug.Print strSql
            
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub


This copies the main form record as expected but no matter what I put in the values clause it doesn't seem to copy.

Basically from the following :
Code:
"VALUES(OrderDetailsID," & lngID & "," & NewOrderTypeID & ",ProductID,Discount,Quantity,ListPrice)"

I want to copy all the fields as they are on the form but change the OrderHeaderID field to whatever is stored in lngID and to change the OrderTypeID to whatever is in NewOrderTypeID.


I have tried using single and double quotes in place of fields theat I want to keep the same.

Any help will be much appreciated.
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:55
Joined
Jun 21, 2011
Messages
5,901
Why is there a *Stop* there? Do you get anything in the Immediate Window for the Debug line?
 

Gazza1709

New member
Local time
Today, 01:55
Joined
Sep 9, 2018
Messages
7
Hi Gina,

I put this in so I could make sure the correct values were where they should be while I`m trying to get it to work.


The immediate window shows as below

Code:
INSERT INTO [TblOrderDetails] (OrderDetailsID, OrderHeaderID, OrderTypeID, ProductID, Discount, Quantity, ListPrice)VALUES(OrderDetailsID,209,5,ProductID,Discount,Quantity,ListPrice)SELECT 209 As NewID, OrderHeaderID, OrderTypeID, ProductID, Discount, Quantity, ListPrice FROM [TblOrderDetails] WHERE OrderHeaderID = 102;


The numbers are correct in the above that I would expect but with this I get the following error


Run Time Error '3137'

Missing semicolon (;) at end of statement


I`m guessing I have some quotes in the wrong place but can`t for the life of me see it. I thought an expert eye might spot where I`m going wrong.
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:55
Joined
Jun 21, 2011
Messages
5,901
Okay, well I see the problem, why did you add the VALUES line? It should read...

Code:
strSQL = "INSERT INTO [TblOrderDetails] (OrderDetailsID, OrderHeaderID, OrderTypeID, ProductID, Discount, Quantity, ListPrice)" & _
            "SELECT OrderDetailsID, " & lngID & ", " & NewOrderTypeID & ", ProductID, Discount, Quantity, ListPrice " & _
                "FROM [TblOrderDetails] WHERE OrderHeaderID = " & Me.OrderHeaderID & ";"

Hmm, double check to make sure I got the fields match up right.
 

Gazza1709

New member
Local time
Today, 01:55
Joined
Sep 9, 2018
Messages
7
Hi Gina,

Thanks for the help so far.

This is selecting records from TblOrderDetails with OrderHeaderID =lngID and OrderTypeID = NewOrderTypeID.

I actually want to set the OrderHeaderID and OrderTypeID fields to the lngID and NewOrderTypeID values when inserting them into the table.

The SELECT part should just choose the records with a matching OrderHeaderID that is set in the where clause.
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:55
Joined
Jun 21, 2011
Messages
5,901
Not sure what you are saying unless you mean you want to leave the other fields blank? If so then just remove the additional fields from the INSERT and SELECT lines.

Note, you are running an INSERT query and the SELECT part tells what fields are to be inserted.
 

Gazza1709

New member
Local time
Today, 01:55
Joined
Sep 9, 2018
Messages
7
Hi Gina,

Basically what I am trying to do is to copy all the fields from a record in TblOrderDetails where the OrderHeaderID is what is in the WHERE clause.

Then I want to Insert the record back into TblOrderDetails as a new record but to give it a new OrderHeaderID that is set as lngID and also change OrderTypeID which is set as NewOrderTypeID.
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:55
Joined
Jun 21, 2011
Messages
5,901
Then what I gave you should work. Please post the code you are using now here.
 

Gazza1709

New member
Local time
Today, 01:55
Joined
Sep 9, 2018
Messages
7
Hi Gina,

Got it all working. What you gave me was correct, not sure why I was throwing an error to start with but when I removed the code then reinserted it after restarting access it worked.


Many thank for all your help


Gareth
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:55
Joined
Jun 21, 2011
Messages
5,901
Probably some hanging character somewhere. Happy to help!
 

Users who are viewing this thread

Top Bottom