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

ashah08

Member
Local time
Today, 17:39
Joined
May 1, 2020
Messages
35
HI All,

I have been successful in copying the data from my existing main form and create a new record but when I am trying to do the same for my sub form, it doesn't work?
I am using D-Lookup to copy and create a new record on click
here's the code that worked for my main form

Private Sub btnA_Click()
Dim ID As Long
ID = TransactionID

DoCmd.GoToRecord , , acNewRec
[Date Expected] = DLookup("[Date Expected]", "Transactions", "TransactionID=" & ID)
[PO Date] = DLookup("[PO Date]", "Transactions", "TransactionID=" & ID)
[Requisitioner] = DLookup("[Requisitioner]", "Transactions", "TransactionID=" & ID)
[SupplierID] = DLookup("SupplierID", "Transactions", "TransactionID=" & ID)
[Destination ID] = DLookup("[Destination ID]", "Transactions", "TransactionID=" & ID)
End Sub

Above code works for my main form and creates a new record with all my details but my sub-form stays blank?

What should I do please guide me

Thank You
 

ashah08

Member
Local time
Today, 17:39
Joined
May 1, 2020
Messages
35
Hi @theDBguy

I even tried this from one of your post but it doesn't work for me.


post
Feb 12 2010, 07:07 PM
Post#3


UA Moderator
Posts: 78,509
Joined: 19-June 07
From: SunnySandyEggo

Hi,
Welcome to Utter Access!
You can nest two DLookup() statements. For example:
DLookup("TextField", "SecondTable", "PrimaryKey = " & DLookup("PrimaryKey", "FirstTable", "SomeOtherCriteriaIfNeeded"))
Hope that helps...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:39
Joined
Oct 29, 2018
Messages
21,358
here is the attached copy of my database for the review
my main form name is Headform
my sub form name is TransactionsDetailsSUBform
button used to duplicate is named Duplicate record

To open it in normal edit mode you need to press shift to bypass login form
Hi. Your main form is based on a record source with three tables. Which table did you want to copy the current record into?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:39
Joined
Oct 29, 2018
Messages
21,358
Hi @theDBguy
I just want to create a new record by copying details from my existing record.
I dont want to store them anywhere.
Three tables used are supplier table
Destination table and transaction table and my transaction table has transactionsID which is my primary key for the record and based on my primary key I have linked my sub form.

Please correct me if I am wrong

Thank you so much for your reply
As you may know, records are stored in tables. So, when you say you want to "copy" a record, then it means you want to create a duplicate record in the table. If so, in which table or tables are we trying to create a duplicate record?
 

ashah08

Member
Local time
Today, 17:39
Joined
May 1, 2020
Messages
35
As you may know, records are stored in tables. So, when you say you want to "copy" a record, then it means you want to create a duplicate record in the table. If so, in which table or tables are we trying to create a duplicate record?

HI @theDBguy

First I would like to thank you for your prompt response and the explanation that you wrote in your last reply.
Answer to your question will be I would like to duplicate record in transactions table for headform and for subform it would be transactionsdetails table.

Correct me if I am wrong.

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:39
Joined
Oct 29, 2018
Messages
21,358
HI @theDBguy

First I would like to thank you for your prompt response and the explanation that you wrote in your last reply.
Answer to your question will be I would like to duplicate record in transactions table for headform and for subform it would be transactionsdetails table.

Correct me if I am wrong.

Thank you
Okay, it's late here now. I'll see if I can give it a try tomorrow. Good night.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:39
Joined
Feb 19, 2002
Messages
42,976
Although you can copy the parent record using code as you have done, you will need to use an append query to copy the child records. The append query will take TWO arguments. The WHERE clause needs to know the FK of the ORIGINAL parent record and the Insert clause needs to know the FK of the NEW parent record so the New parent record must be saved prior to running the append. Personally, I would use DAO to insert the new record rather than the method you have chosen.
 

ashah08

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

can you tell me what are the steps to use DAO method ?
any article or reference to which i refer to ?

Thank You so much for the reply
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:39
Joined
Feb 19, 2002
Messages
42,976
This code should go in the click event of your "copy" button. You will need to change the relevant variable names to suit your database.
Code:
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.ParentPK
Set db = CurrentDB
Set td = db.TableDefs.YourParentTableName
Set rs = td.OpenRecordset (dbOpenDynaset, dbSeeChanges)

    rs.AddNew
        rs!fld1 = Me.fld1
        rs!fld2 = Me.fld2
        rs!...
        SaveMewID = rs!PrimaryKeyName
    rs.Update
   
    strSQL = " Insert Into (fld1, fld2, fldFK)"
    strSQL = strSQL & " Select fld1, fld2, " & SaveNewID & " From YourChildTable"
    strSQL = strSQL & " Where fldFK = " & SaveOldID
   
    db.Execute strSQL, dbFailOnError
 
Last edited:

ashah08

Member
Local time
Today, 17:39
Joined
May 1, 2020
Messages
35
HI @Pat Hartman & @Isaac

Thank You so much for taking out the time and helping me.

is it possible to give me some explanation about which fields in the code needs to be changed?
i am fairly new to access and the database you see attached above is my first database in access.
i learned everything online and have build this.

i do understand the code that you have forwarded but not completely to incorporate in my work.

Any help would be much appreciated!!

Thank you once again
 

ashah08

Member
Local time
Today, 17:39
Joined
May 1, 2020
Messages
35
HI All,

I tried out the code mentioned above but i am getting error 3134 insert into statement error

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]

SaveMewID = rs!TransactionID
rs.Update

strSQL = " Insert Into ([Date Expected],[PO Date],[Requisitioner],[SupplierID],[Destination ID] )"
strSQL = strSQL & " Select [Description] " & SaveNewId & " From TransactionsDetails"
strSQL = strSQL & " Where TransactionID = " & SaveOldID

db.Execute strSQL, dbFailOnError
End Sub


This is my code!!!
can someone please help me @Pat Hartman @Isaac @theDBguy
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:39
Joined
Feb 19, 2002
Messages
42,976
This will not work.
Sorry, typo. Should have been:

Set td = db.TableDefs!YourParentTableName
This needs a bang in front of the table name

Of course if you use improperly named objects that always need to be enclosed in square brackets, you could use
Set td = db.TableDefs("YourParentTableName")

But since I don't use improperly named objects, I prefer the shortcut method.

@ashah08,
"fld1", "fld2", "ParentPK", "PrimaryKeyName" anything that starts with "your". I'll edit the entry and mark them with red.

Sorry, there is a bug in this site's editor and I couldn't highlight the fields in the code window.
 

Isaac

Lifelong Learner
Local time
Today, 14:39
Joined
Mar 14, 2017
Messages
8,738
Sorry, typo. Should have been:

Set td = db.TableDefs!YourParentTableName
This needs a bang in front of the table name

Of course if you use improperly named objects that always need to be enclosed in square brackets, you could use
Set td = db.TableDefs("YourParentTableName")

But since I don't use improperly named objects, I prefer the shortcut method.

@ashah08,
"fld1", "fld2", "ParentPK", "PrimaryKeyName" anything that starts with "your". I'll edit the entry and mark them with red.
I'm having trouble seeing how that's a shortcut, but your new method should work. I avoid using ! when possible. Normally methods that provide Intellisense are preferred. I didn't use any brackets. : )

One more note - would help to throw in a table name to the Insert Into statement.
strSQL = " Insert Into (fld1, fld2, fldFK)"
 
Last edited:

ashah08

Member
Local time
Today, 17:39
Joined
May 1, 2020
Messages
35
Hi @Pat Hartman & @Isaac

I was able to solve the insert into error from the last reply @Isaac now it shows me error

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

error is on this line and it states "missing operator in query expression access"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:39
Joined
Feb 19, 2002
Messages
42,976
You have a typo:
SaveMewID = rs!TransactionID
should be
SaveNewID = rs!TransactionID

This tells me that you don't have Option Explicit set. Ad that as the second line of each module and in the properties set it as required so Access will do it automatically for you. I don't know if this will fix the error. One thing that will help is to put a stop on the .execute method. When the code stops, print strSQL to the debug window and paste the results here.

There is another typo which Issac so unhelpfully pointed out but didn't bother to help with a correction. He was more interested in pointing out my error.

strSQL = " Insert Into ([Date Expected],[PO Date],[Requisitioner],[SupplierID],[Destination ID] )"
strSQL = strSQL & " Select [Description] " & SaveNewId & " From TransactionsDetails"
strSQL = strSQL & " Where TransactionID = " & SaveOldID

Should be:

strSQL = " Insert Into TransactionsDetails ([Date Expected],[PO Date],[Requisitioner],[SupplierID],[Destination ID] )"
strSQL = strSQL & " Select [Description] " & SaveNewId & " From TransactionsDetails"
strSQL = strSQL & " Where TransactionID = " & SaveOldID

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

Users who are viewing this thread

Top Bottom