Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Duplicate the main record and related detail records

Posted on 2012-12-26
6
Medium Priority
?
625 Views
Last Modified: 2012-12-26
Hello All!  I have run into an issue cloning records in Microsoft Access.  I have modified code from Allen Browne's website.  The code clone's the main record first after prompting the end user for a new date.  That part works flawlessly.  The problem arises when I get to the detail records.  All of the information for the detail records gets cloned; however I can't get the new date inserted into the records.  It leaves it as a null value which displays as 12/30/1899 in SQL Server 2005.  I believe it must have something to do with a date delimiter, but I am not quite sure.  Any help would be greatly appreciated.

Public Function DupRec()
On Error GoTo err_handle
'Purpose:   Duplicate the main form record and related records in the subform.
'Provided by Allen Browne, November 2005

Dim lngEventID As Long, lngSetupID As Long
Dim Msg, Style, Title, Response, MyString   'Message Box
Dim datStart As Date, datEnd As Date, strEName As String
Dim strSetup As String, strMeals As String, strBvgSvc As String
Dim strEquip As String, db As DAO.Database

    
Set db = DBEngine(0)(0)

'Get dates for new record from temporary table.
datStart = DLookup("[datStart]", "zttblDupDate")
datEnd = DLookup("[datEnd]", "zttblDupDate")
strEName = Forms!fdlgDupDate!txtEName
    
'Make sure there is a record to duplicate
If Me.NewRecord Then
    'Notify the user there is no record to duplicate
    Msg = "Select a record to duplicate."
    Style = vbOKOnly + vbInformation
    Title = "No Data"
    Response = RichBox(Msg, Style, Title)
Else
    'Duplicate the main record: add to form's clone.
    With Me.RecordsetClone
        .AddNew
        !EventName = strEName
        !Client = Me.cboClient
        !Category = Me.cboCategory
        !Status = Me.cboStatus
        !Location = Me.cboLocation
        !StartDate = datStart
        !EndDate = datEnd
        !ProgramStartTime = Me.txtProgramStart
        !ProgramEndTime = Me.txtProgramEnd
        !ArrivalTime = Me.txtArrivalTime
        !SetupDate = Me.txtArrivalDate
        !SetupTime = Me.txtSetupTime
        !RegistrationTime = Me.txtRegStart
        !iMISCode = Me.txtiMISCode
        !SvcChg = Me.txtSvcChg
        !PartnerDiscount = Me.cboDiscount
        !CleaningFee = Me.txtCleaningFee
        .Update
            
        'Save the primary key value, to use as the foreign key for the related records.
        .Bookmark = .LastModified
        lngEventID = !EventID
            
        'Duplicate the related event setup records: append query.
        If Me.[Event Setup Subform].Form.RecordsetClone.RecordCount > 0 Then
            strSetup = "INSERT INTO todbEventSet ( Event, MeetingDate, MeetingLocation, RentalRate, Rate, " & vbCrLf & _
                "PrimarySetup, SecondarySetup, RegTable, ExtraChairs, ProgramStartTime, " & vbCrLf & _
                "ProgramEndTime, ExpectedPrimary, ExpectedSecondary ) " & vbCrLf & _
                "SELECT " & lngEventID & " As NewEvent, " & datStart & " As NewDate, " & vbCrLf & _
                "todbEventSet.MeetingLocation, todbEventSet.RentalRate, " & vbCrLf & _
                "todbEventSet.Rate, todbEventSet.PrimarySetup, " & vbCrLf & _
                "todbEventSet.SecondarySetup, todbEventSet.RegTable, " & vbCrLf & _
                "todbEventSet.ExtraChairs, todbEventSet.ProgramStartTime, " & vbCrLf & _
                "todbEventSet.ProgramEndTime, todbEventSet.ExpectedPrimary, " & vbCrLf & _
                "todbEventSet.ExpectedSecondary" & vbCrLf & _
                "FROM [todbEventSet] WHERE Event = " & Me.txtID & ";"
            db.Execute strSetup, dbFailOnError
        Else
            'Notify the user there is no record to duplicate
            Msg = "Select a record to duplicate."
            Style = vbOKOnly + vbInformation
            Title = "No Data"
            Response = RichBox(Msg, Style, Title)
            MsgBox "Main record duplicated, but there were no related event setup records."
        End If
                       
        'Duplicate the related meal records: append query.
        If Me.[Event Meals Subform].Form.RecordsetClone.RecordCount > 0 Then
            strMeals = "INSERT INTO [todbMeals] ( Event, MealDate, MealType, FoodSetup," & vbCrLf & _
                "SetupTime, ServingTime, Expected) " & vbCrLf & _
                "SELECT " & lngEventID & " As NewEvent, " & datStart & " As NewDate, " & vbCrLf & _
                "todbMeals.MealType, todbMeals.FoodSetup, todbMeals.SetupTime, " & vbCrLf & _
                "todbMeals.ServingTime, todbMeals.Expected" & vbCrLf & _
                "FROM [todbMeals] WHERE Event = " & Me.txtID & ";"
            db.Execute strMeals, dbFailOnError
        Else
            MsgBox "Main record duplicated, but there were no related meal records."
        End If
            
        'Duplicate the related beverage records: append query.
        If Me.[Beverage Expenses].Form.RecordsetClone.RecordCount > 0 Then
            strBvgSvc = "INSERT INTO [todbBevSvc] ( Event, ExpenseDate, Beverage, Quantity, UnitPrice ) " & vbCrLf & _
                "SELECT " & lngEventID & " As NewEvent, " & datStart & " As NewDate, " & vbCrLf & _
                "todbBevSvc.Beverage, todbBevSvc.Quantity, todbBevSvc.UnitPrice " & vbCrLf & _
                "FROM [todbBevSvc] WHERE Event = " & Me.txtID & ";"
            db.Execute strBvgSvc, dbFailOnError
        Else
            MsgBox "Main record duplicated, but there were no related beverage records."
        End If
            
        'Display the new duplicate.
        Me.Bookmark = .LastModified
    End With
End If

'Update details records using the new dates.
DoCmd.RunSQL "UPDATE todbEventSet SET [MeetingDate] = " & datStart & " WHERE [Event] = " & Me.txtID
            
'Delete temporary table
DoCmd.DeleteObject acTable, "zttblDupDate"
        
Exit_Proc:
Exit Function

err_handle:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "DupRec()")
Resume Exit_Proc
End Function

Open in new window

0
Comment
Question by:schaIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38721435
<I have modified code from Allen Browne's website.>
Please post the link to this original code...
Have you tried contacting the author?
Perhaps something is awry in your modification?
Many experts here are wary of modifying another top developer's code...

< The code clone's the main record first after prompting the end user for a new date.  That part works flawlessly.  The problem arises when I get to the detail records.  All of the information for the detail records gets cloned; however I can't get the new date inserted into the records. >
Why would a date in the Parent record be needed to be stored in the Child table?
For example:
Orders (Main) and Order Details (Sub)
The Order Date need only exists in the main form/table, there is no need to duplicate this date in the subform because that would create duplicate/redundant data.  Besides, you can pull the OrderDate into the subform via a query.

I mean you can probably get an answer to your question as posted, ...

I am just wondering about the need to store the Date in many places instead of one.

Let's see what other Experts post, in case I misunderstood something...


JeffCoachman
0
 

Author Comment

by:schaIT
ID: 38721496
Thank you so much for your quick reply!  You have mentioned some good points.  Let me provide more background information on why the date is stored in the main record and the child records.  This database is used to book events for meeting spaces.  Meetings can booked as one day events or multiple day events.  

Example:  
The main record indicates ABC Company would like to have a meeting in Conference Room A from 12/26/2012 to 12/28/2012.  The first set of child records needs to store a date to indicate how Conference Room A should be setup on each of the days listed in the main record.  The second set of child records needs a date to store the lunch menu for each of the days listed in the main record.

The dates seems redundant, but without them we would not be able to tell what pieces of information belong to each day of the event.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38721523
Pesuming that there is a valid reason for duplicating this data...

You could:
1. Store the "Date" as a variable
2. Insert this date variable into the child records after the child records are duplicated.

Pseudo code:
'Code to store the date as a variable
'Code to copy main records
'Code to Copy the Child records
'Code to Insert the date variable into the child records
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 700 total points
ID: 38721628
*Roughly* like this, on a button on the main form:
Dim lngOrderID As Long
Dim dtmOrderdate As Date
Dim strSQL As String

lngOrderID = Me.OrderID
dtmOrderdate = Me.OrderDate

strSQL = "Update tblOrderDetails SET OrderDate=" & "#" & dtmOrderdate & "#" & " WHERE OrderID=" & lngOrderID

CurrentDb.Execute strSQL, dbFailOnError
[frmOrderDetails].Form.Requery

Sample attached
Database17.mdb
0
 

Author Closing Comment

by:schaIT
ID: 38721666
That does the trick!  The date delimiters were missing in the original update query.  After reviewing the syntax in your last post, I modified the code in the actual cloning of the details using the correct syntax and removed the update query.  

Originial:
 "SELECT " & lngEventID & " As NewEvent, " & datStart & " As NewDate, " & vbCrLf & _

Open in new window


New:
"SELECT " & lngEventID & " As NewEvent, # " & datStart & " # AS NewDate, " & _

Open in new window


It works perfectly now!  Thanks so much for your help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38721688
;-)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question