HeIp!! I want to copy data from my mainform and subform and on button click create a new record? (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 15:21
Joined
Mar 14, 2017
Messages
3,294
Hi @Pat Hartman @Isaac @theDBguy


Hi @Pat Hartman @Isaac @theDBguy

Good Morning!!

@Pat Hartman
You mentioned i need to put stop before the executing lines, how to do that ?
how to get the sql code for data ?

@Isaac
what do you mean by (hint - it's Category #1). ???

@theDBguy
please correct me in my approach as you have seen my database already.

Have a good day guys
Really ?? Category 1 as in, the #1 and #2 that I just stated. I think I'm out of this thread as you are not really reading our posts and carefully following instruction. Sorry, for that reason, I'm out.
 

ashah08

Member
Local time
Today, 18:21
Joined
May 1, 2020
Messages
35
Hi @Isaac

i did read your comment and based on that only i asked you that question.
i have a column named category ID in transactiondetails table and it gets the data from category table which has category 1, 2 , 3 in the form of drop-down.

now whats my fault in this >???
i am trying my best to understand the technicalities of Access but sometimes i just cant understand, few things which are in depth.

i am sorry if you feel that i am not reading the post but frankly that's not the case.

Have a good day
Thank you
 

ashah08

Member
Local time
Today, 18:21
Joined
May 1, 2020
Messages
35
Hi @Pat Hartman

Code:
Insert Into TransactionsDetails ([Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total]) Select [Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total],754 From TransactionsDetails Where TransactionID = 385
Insert Into TransactionsDetails ([Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total]) Select [Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total],755 From TransactionsDetails Where TransactionID = 385

sql code when i print it in immediate window
SaveNewID is causing this error that i am getting.
it is creating two new save ID's
 
Last edited:

ashah08

Member
Local time
Today, 18:21
Joined
May 1, 2020
Messages
35
Hi @theDBguy @Pat Hartman
Code:
Private Sub Command663_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 Long       'Primary key value of the new record.
    
    '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
                        [Date Expected] = Me.[Date Expected]
                        [PO Date] = Me.[PO Date]
                        [Requisitioner] = Me.[Requisitioner]
                        [SupplierID] = Me.[SupplierID]
                       [Destination ID] = Me.[Destination ID]
                       '[TransactionID] = Me.TransactionID
                      
                       'lngID = !TransactionID
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = Me.TransactionID
            
            Debug.Print lngID
            
            'Duplicate the related records: append query.
            If Me.[TransactionsDetailsSUBform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [TransactionsDetails] ([Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total]) " & _
                    "SELECT " & lngID & " As NewID,[Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total]" & _
                    "FROM [TransactionsDetails] WHERE TransactionID = " & Me.TransactionID & ";"
                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

i have tried using allenbrownes code for duplicating my record but i still get the same error "" number of query values and destination field are not same"""

SQL code for the output

Code:
421
 421
 421
INSERT INTO [TransactionsDetails] ([Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total]) SELECT 421 As NewID,[Description],[Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total]FROM [TransactionsDetails] WHERE TransactionID = 421;


Please help please
that
 

ashah08

Member
Local time
Today, 18:21
Joined
May 1, 2020
Messages
35
Hi All,

Please help!!!!
Please
I am willing to pay anyone who can help me.
This is important for me.
Please help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:21
Joined
Oct 29, 2018
Messages
13,750
Hi All,

Please help!!!!
Please
I am willing to pay anyone who can help me.
This is important for me.
Please help
Hi. Can you reach out to me via email to discuss this further? My email address is in my signature below. Cheers!
 

Users who are viewing this thread

Top Bottom