I have a problem with this. Currently I am trying Allen's code and i am not successful.
Current Design
Table1 (Main Form)
TravelID (PK)
ApprovedBY
EntreredBy
BudgetCode
ExpenseCode
Table2 (Subform)
TripID (PK)
TripTypeID
TravellerID
TripStartDate
TripEndDate
TripStatus
Can you help with this? -
'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 and 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
-
!EnteredBy = Me.EnteredBy
-
!ApprovedBy = Me.ApprovedBy
-
!BudgetCode = Me.BudgetCode
-
!ExpenseCode = Me.ExpenseCode
-
.Update
-
-
'Save the primary key value, to use as the foreign key for the related records.
-
.Bookmark = .LastModified
-
lngID = Me.TravelAuthorizationID
-
-
-
'Duplicate the related records: append query.
-
If Me.[TravelAuthorizations Subform].Form.RecordsetClone.RecordCount > 0 Then
-
strSql = "INSERT INTO [Trips] ( TravelID, TripTypeID, TravellerID, TripStartDate, TripEndDate, TripStatus ) " & _
-
"SELECT " & lngID & " As NewID, TripID, TripTypeID, TravellerID, TripStartDate, TripEndate, TripStatus " & _
-
"FROM [Trips] WHERE TravelID = " & Me.TravelID & ";"
-
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
1 7272 ADezii 8,834
Recognized Expert Expert
I too seem to have trouble implementing Allen Browne's code, so I drastically changed the logic involved - sorry Allen! (LOL)! - Instead of using the Recordset exposed by the Form.RecordsetC lone Property, I created an External Recordset based on the Orders Table (Ref#1).
- I used a smaller Data set for the code testing, but it can easily be expanded.
- I allowed the Primary Key in the Main Table (Orders) to Auto-Generate when adding a New Record (Ref #2), then retrieved its value by setting a Bookmark to the LastModified Property of the Recordset (Ref #3).
- Retrieved the Value of the Foreign Key for the Records to be added to the Sub-Form (Order Details), which is the same as the Primary Key in the Main Form (Ref #4).
- Populated the Sub-Form (Ref #5), Requeried the Main Form (Ref #6), then Navigated to the newly created Main/Sub-Form Record(s) (Ref #7).
- Any questions, feel free to ask.
- Dim MyDB As DAO.Database
-
Dim rstDup As DAO.Recordset
-
Dim lngNewPK As Long
-
Dim lngOldPK As Long
-
Dim strSQL As String
-
-
Set MyDB = CurrentDb
-
Set rstDup = MyDB.OpenRecordset("Orders", dbOpenDynaset) 'Ref #1
-
-
With rstDup
-
.AddNew
-
'The Primary Key [Order ID] will Auto Generate 'Ref #2
-
![Order Name] = Me![Order Name]
-
.Update
-
.Bookmark = .LastModified 'Ref #3
-
lngNewPK = ![Order ID]
-
End With
-
-
'Retrieve the Value of the Old, Original Primary Key in order
-
'to populate the New Main Record's Sub-Form
-
lngOldPK = Me![Order ID] 'Ref #4
-
-
If Me.[Order Details].Form.RecordsetClone.RecordCount > 0 Then
-
strSQL = "INSERT INTO [Order Details] ([Order ID], [Section]) " & _
-
"SELECT " & lngNewPK & " As NewID,[Section] " & _
-
"FROM [Order Details] WHERE [Order ID] = " & lngOldPK & ";"
-
CurrentDb.Execute strSQL, dbFailOnError 'Ref #5
-
Else
-
MsgBox "Main record duplicated, but there were no related records."
-
End If
-
-
rstDup.Close
-
Set rstDup = Nothing
-
-
Me.Requery 'Ref #6
-
DoCmd.GoToRecord , , acLast 'Ref #7
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Catherine Jo Morgan |
last post by:
Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?
Or is it possible to allow this but to throw up an alert message? Warning
that this person is probably already in the database? TIA
|
by: Lyn |
last post by:
Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.
The parent form (frmMainForm) displays the data in each record, which
includes the ID of the father and the mother (who also have records in the
table). One record per form.
I have a Tab Control in the form, and in one of the tabs I have a subform
(sfmSiblings) in which I wish to list...
|
by: Charles Ledbetter |
last post by:
I'm missing the mistake.
I have a MainForm called frmResources.
I have a Second Form and related table called frmFootnotes. I am trying to
use it as a subform to the main form appearing in a tabctl in the main form.
The two are related with a one-to-many relationship. The PrimaryID field in
Resources is related to a non-primary field in Footnotes. At least that is
where it currently is. I've tried every combination I can think of and...
|
by: mavmavv |
last post by:
I have a Form where I have created a duplicate record button, no
problem...
The subform is where my problem lies. The subform displays data
matching the mainform's ID, these two values are linked. The subform
has no primary key, since there are multiple matching entries.
Basically the mainform displays Customer info + order totals, and the
subform displays the products.
When I goto duplicate the record, the ID from the duplicated...
|
by: claus |
last post by:
Hi,
I am not a programmer but tries anyway to program a feature in a form
where I am able to copy entries for the form and a subform. I have
tried follow the guide here http://allenbrowne.com/ser-57.html, but my
problem is that I have more than one keyfield in the join between the
two tables: Hovedoplysninger and Itemoplysninger. The tree fields are:
"Medarbejder", "Sagsnummer" and "Mandag". I have succeded creating the
new entry...
| |
by: Otis492 |
last post by:
Hello,
I have been struggling with this for a while. I am working on a rather simple database for claims in Access 2003. I have a table called claims that has a primary key field called Claim #. It is linked to a table called parts. The idea is that a claim can be filed for multiple damaged parts. In other words a claim # could have 0 to several damaged parts. My problem lies with the form that I have created. It is a main form with a...
|
by: VinArt |
last post by:
MS Acc 2003, XP
Thank you in advance for any help.
I have tables called "Makeup" and "Lines". Each makeup can have multiple lines.
Goal is to create a new "makeup" with identical "lines" except with a new makeup id.
I created a form Makeup Copy with Line Subform. In the main form there is ID (key field), Comments, Customer, Description plus an unbound field "NewMakeup". Each line of the subform has the fields "Makeup (primary...
|
by: 6afraidbecause789 |
last post by:
Kudos to anyone who can explain this one--how to duplicate a group of
continuous records in a subform for use in a new subform PK ID. The 2
entry fields (combo boxes) in the subform are RoleID and StaffID, and
the PK ID is StaffandIntsID. The subform's data is put into
jtblStaffandInts, which joins a Staff and Intervention tables:
So, here's what the data could look like in the table:
StaffandIntsID InterventionID StaffID RoleID
|
by: troy_lee |
last post by:
I have a table (Table A). It has one field, a PK. It is in a 1:M with
another table (Table B). I am having trouble with a form/subform setup
to view the 1:M records.
On the parent form, there is a combo box with a Select query to select
one of the records in Table A. (The parent form is bound to Table A.)
I want to be able to first, view all of the corresponding records from
Table B and then second, give an option for editing these...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |