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

[Pat Hartman]

There is a comma missing in your statement.

strSQL = strSQL & " Select [Description], " & SaveNewId & " From TransactionsDetails"

 

[ashah08]

Hi

TransactionsDetails

TransactionsDetails is the table that relates to my subform and for my main its Transactions table

so in the code that you just edited for me

strSQL = " Insert Into TransactionsDetails ([Date Expected],[PO Date],[Requisitioner],[SupplierID],[Destination ID] )" --- all these are part of my main form
strSQL = strSQL & " Select [Description] " & SaveNewId & " From TransactionsDetails" ---- description is part of my subform
strSQL = strSQL & " Where TransactionID = " & SaveOldID


it should be transactiondetails or transactions table ???????

@Pat Hartman

 

[Pat Hartman]

You are copying data from the transaction details and putting it into transaction details and the only thing in the record that is changing is the reference to the FK.

I didn't mention explicitly but probably should have, this insert query should NOT include the primary key of the record being copied since a new autonumber will be generated when the copied data is inserted.

 

[Isaac]

@Issac,
When an expert takes the time to type custom code in response to a question, there is always the potential for typos. Sadly, this forum software is not a compiler nor is it a test platform.

Of course.

 

[ashah08]

Hi @Pat Hartman

i want to clarify something

based on the code that you gave me

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 - this is my primary key in main form and this is my foreign key for the subform
Set db = CurrentDb
Set td = db.TableDefs("Transactions") - this is the table which relates to my main form

Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)

rs.AddNew
rs![Date Expected] = Me.[Date Expected] ----- all the fields mentioned here are from my main form

rs![PO Date] = Me.[PO Date]
rs![Requisitioner] = Me.[Requisitioner]
rs![SupplierID] = Me.[SupplierID] ----- supplier and destination ID's are my bound to dropdown fileds which updates details for supplier and destination

rs![Destination ID] = Me.[Destination ID]

SaveNewId = rs!TransactionID --- - this is my primary key in main form and this is my foreign key for the subform
rs.Update

strSQL = " Insert Into Transactions ([Date Expected],[PO Date],[Requisitioner],[SupplierID],[Destination ID])" ----- table name mentioned here is my main form table name along with the fields from that table

strSQL = strSQL & " Select [Description], " & SaveNewId & " From TransactionsDetails" --- description is a field in my subform and
transactiondetails is the table that relates to subform

strSQL = strSQL & " Where TransactionID = " & SaveOldID

db.Execute strSQL, dbFailOnError
End Sub





Please read my explanation above and correct me if i am wrong

Note: here for demo i have just added description field but i will be adding more fields to that statement


Please help and correct me

@Pat Hartman

 

[Pat Hartman]

The insert query is there to copy the DETAILS, NOT the parent. You need to change the table following the "Insert Into to be the same as the table referenced by the select part of the append query. You are copying a set of detail records (based on the where clause) and inserting them under a different parent record in the same table.

Also,
When you post code, please use the code tags since it makes the code easier to read.

 

[ashah08]

okay

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 ([Date Expected],[PO Date],[Requisitioner],[SupplierID],[Destination ID])"
    strSQL = strSQL & " Select [Description], " & SaveNewId & " From TransactionsDetails"
    strSQL = strSQL & " Where TransactionID = " & SaveOldID

so after
rs.AddNew
i am suppose to enter all the fields from my subform or main form ??
right now i have kept my main form fields should i change them to sub form ??

@Pat Hartman

 

[Pat Hartman]

The rs.AddNew/rs.Update is the code that is copying the records from the main form which is where this code is running.
The insert query is what is copying the records that are visible on the subform. It is the WHERE clause that is telling Access which set of records to copy.

 

[ashah08]

Hi @Pat Hartman

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

 

[Pat Hartman]

Did you add the stop on the execute line as I suggested earlier? did you post the sql string that you printed to the debug window?

It is easier for all of us if you try to follow directions or ask if you don't understand why.

 

[Isaac]

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

Might be missing an Insert Into field.

*

 

[ashah08]

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 ???

 

[Isaac]

See my previous post about missing the field you want to insert SaveNewID into.

 

[ashah08]

See my previous post about missing the field you want to insert SaveNewID into.

I read that but & save new I'd is to store new I'd and it's not a field in my form

 

[Pat Hartman]

I still don't see the ACTUAL sql code that I asked you for twice. Issac probably identified the problem since it looks like the ID field is what is missing.. If I don't see the SQL string soon, I'll be gone for the day.

 

[Isaac]

I read that but & save new I'd is to store new I'd and it's not a field in my form

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.

 

[Pat Hartman]

Two last things,
1. If the current record might be dirty, you should save it before doing the copy.

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

2. If you want the form to go to the "new" record after you create it, add the following to the end of the button code.

    Set rs = Me.RecordsetClone
    rs.FindFirst "TransactionID = " & Me.TransactionID
        If rs.NoMatch Then
            MsgBox "Record not found"
        Else
            Me.Bookmark = rs.Bookmark
        End If
    rs.Close

 

[ashah08]

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

 

[theDBguy]

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

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!

 

[ashah08]

Hi @Pat Hartman @Isaac @theDBguy

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 @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