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

Hi @Pat Hartman

Code: 
Private Sub CopyRecord_Click()

Dim db             As DAO.Database
Dim rs             As DAO.Recordset
Dim td             As DAO.TableDef
Dim SaveNewId      As Long
Dim SaveOldID      As Long
Dim strSQL         As String

SaveOldID = Me.TransactionID
Set db = CurrentDb
Set td = db.TableDefs("Transactions")
Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)

    rs.AddNew
       rs![Date Expected] = Me.[Date Expected]
        rs![PO Date] = Me.[PO Date]
        rs![Requisitioner] = Me.[Requisitioner]
        rs![SupplierID] = Me.[SupplierID]
        rs![Destination ID] = Me.[Destination ID]
        
        SaveNewId = rs!TransactionID
    rs.Update
    
    strSQL = " Insert Into TransactionsDetails ([Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total] )"
    strSQL = strSQL & " Select [Order Quantity],[Received Quantity],[CategoryID],[SpeciesID],[ProcessID],[GradeID],[Price],[Unit of measure],[Cost Unit],[Currency],[Total], " & SaveNewId & " From TransactionsDetails"
    strSQL = strSQL & " Where TransactionID = " & SaveOldID
    
    db.Execute strSQL, dbFailOnError
End Sub


I made the appropriate changes in the code but it still give me error " Number of query values and destination field values are not the same ???????


Please help
Thank You
 
ashah08 said:
I made the appropriate changes in the code but it still give me error " Number of query values and destination field values are not the same ???????


Please help
Thank You
Click to expand...
Might be missing an Insert Into field.
Testing 20200717_2.jpg



*
 
Hi @Pat Hartman @Isaac

i tried all the possible ways mentioned but i am not able to figure out whats missing in my code

what should i do ???
 
See my previous post about missing the field you want to insert SaveNewID into.
 
ashah08 said:
I read that but & save new I'd is to store new I'd and it's not a field in my form
Click to expand...
This is an opportunity for exploration and learning.
When you construct an INSERT INTO statement, there are two basic components:
  1. The list of columns to be inserted (this comes first), as in: Insert Into TableName (col1,col2,col3)
  2. The list of values to be inserted (this comes second), as in your current: Select [Order Quantity],[Received Quantity],[CategoryID] (and more).
The number of columns in #1 must be the same as the number of values/selections in #2.
To solve this problem you're having currently, count each group up, and see where you are missing one (hint - it's Category #1).
I'm not sure which table column you WANT to insert that into, that is up to you.
 
Hi @Pat Hartman @Isaac
Thank you so much for all the replies my windows just got crashed.
I am sending this from my phone.
I'll revert back based on your inputs as soon as my laptop is back to normal

Thank you once again
 
ashah08 said:
Hi @Pat Hartman @Isaac
Thank you so much for all the replies my windows just got crashed.
I am sending this from my phone.
I'll revert back based on your inputs as soon as my laptop is back to normal

Thank you once again
Click to expand...
Hi. Just letting you know I didn't go away. I just decided to step back to avoid adding any confusion to the excellent help you're already getting. Cheers!
 
Hi @Pat Hartman @Isaac @theDBguy
Isaac said:
This is an opportunity for exploration and learning.
When you construct an INSERT INTO statement, there are two basic components:
  1. The list of columns to be inserted (this comes first), as in: Insert Into TableName (col1,col2,col3)
  2. The list of values to be inserted (this comes second), as in your current: Select [Order Quantity],[Received Quantity],[CategoryID] (and more).
The number of columns in #1 must be the same as the number of values/selections in #2.
To solve this problem you're having currently, count each group up, and see where you are missing one (hint - it's Category #1).
I'm not sure which table column you WANT to insert that into, that is up to you.
Click to expand...

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
 
ashah08 said:
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
Click to expand...
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.
 
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
 
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:
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
 
Hi All,

Please help!!!!
Please
I am willing to pay anyone who can help me.
This is important for me.
Please help
 
ashah08 said:
Hi All,

Please help!!!!
Please
I am willing to pay anyone who can help me.
This is important for me.
Please help
Click to expand...
Hi. Can you reach out to me via email to discuss this further? My email address is in my signature below. Cheers!
 
You must log in or register to reply here.

Similar threads

A
Open a form and insert data SQL
Replies
6
Views
524
andymartin3186
A
C
Solved Update a record with a recordset
Replies
9
Views
2,751
cheekybuddha
cheekybuddha
N
Solved How to scan a barcode from a parent form and update the child/subform in MS access
Replies
4
Views
599
Uncle Gizmo
Uncle Gizmo
D
Solved Creating a button to run a Delete Query
Replies
4
Views
905
Steve R.
Steve R.
J
code to close my current form on pressing the button to open my Main Menu
Replies
16
Views
2,235
Edgar_
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom