Duplicating A Record W/ Unique Indexes on Some Columns: Prompt User to Provide New Project Names On Execution of The Duplication

Jason Steward
Jason Steward used Ask the Experts™
on
I have a form called PowerlineF.  It's tied to a MS SQL backend table called PowerlineT.  On this form I have a button "btnDuplicateProject".  The goal is to duplicate the current record when a project needs to be split in half due to a decision to build it in different phases with different construction timelines.  The code I planned to use to duplicate is:

    

Private Sub Duplicate Project_Click()

On Error GoTo err_Error_handler
    
If Me.Dirty = True Then
    Me.Dirty = False
End If

With DoCmd
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdCopy
        .RunCommand acCmdRecordsGoToNew
        .RunCommand acCmdSelectRecord
        .RunCommand acCmdPaste
    End With

exit_Error_handler:
 On Error Resume Next
 Set objOutlook = Nothing
 Set objMailItem = Nothing
 Exit Sub
 
err_Error_handler:
 Select Case Err.Number
  Case 287
   MsgBox "Canceled by user.", vbInformation
  Case Else
   MsgBox "Error " & Err.Number & " " & Err.Description
 End Select

Resume exit_Error_handler

End Sub

Open in new window


I have some unique indexes preventing the duplicated record from saving.  My unique column is "ProjectName".  How can we prompt the user to provide a new name for the ProjectName field when pasting the duplicate record?  

Also, if original project was named "Silvertip Powerline" and we wanted to split that project in half, we would need to change the original ProjectName to "Silvertip Powerline (Phase-1)" and the new duplicated project as "Silvertip Powerline (Phase-2)" at the time of duplication?  I'm thinking we'd deploy a message box to facilitate the name change by asking the what the ProjectName should be changed to on the original, then followed by another message box prompting user to provide a ProjectName for the duplicate/new.  Problem is, I don't know how to implement these things within the code.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
I don't have my code handy right now but from memory..you need 2 recordsets...both would be based on the RecordsetClone of the form..then you would Add the new record like this..
Rst2.AddNew
For i = 0 to rst1.Fields.count -1
If rst1.Fields(i).Name = "Project name" then
' Set it as you like..you could ask the User.. you could use some default naming scheme..
Else
Rst2.Fields(i) = rst1.Fields(i)
End If
Next
Rst2.Update
Distinguished Expert 2017

Commented:
You are essentially doing copy and paste.  That doesn't give you the ability to substitute values for any of the fields.  You can open a recordset using DAO (or ADO if you prefer) and use .AddNew to add a new record.  You  populate each field:

rst.fld1 = Me.fld1
rst.fld2 = Me.fld2
rst.fld3 = Me.somenewvalue
...
John TsioumprisSoftware & Systems Engineer

Commented:
Populating each field invidually !!!!...is a recipe for errors...it would work for a table with few fields but what if it has a lot...(most times you work one some else's design)... iteration will also "protect" when someone just added a field or two and forgot to mention it.... suddenly all fails apart...these were the days..
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Use the RecordsetClone. Clean and fast:

Private Sub Duplicate Project_Click()

    Const Title As String = "Project Name"
    Const Prompt As String = "Please enter name of the new project:"

    Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field
    Dim Bookmark    As Variant
    Dim NewName     As String 

    ' Copy parent record.
    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone

    ' Move to current record.
    rst.Bookmark = Me.Bookmark
    With rstAdd
        .AddNew
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "ProjectName" Then
                    ' Adjust name.
                    .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        ' User entered empty name.
                        .Value =  rst.Fields(.Name).Value & " (Phase-1)"
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
            End With
        Next
        .Update
    End With
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark

    rst.Close
    rstAdd.Close

    ' Move to the new recordcopy.
    Me.Bookmark = Bookmark

    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing
    
End Sub

Open in new window

Author

Commented:
Thanks, Gustov!  Does anyone know of a way to let the results of a message box add the new value?  Message box:  "What is the new name of the Original Project"?   I'd like it to show the existing project name and give a text box where user can type in the new name.  The next function would be to ask for the NEW project for the second message box:  "What is the name for the new project?".

Any idea how I would modify this to add that functionality?

       For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "ProjectName" Then
                    ' Adjust name.
                    .Value = rst.Fields(.Name).Value & " (Phase-1)"
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
            End With

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "ProjectName" Then

                    .Value = InputBox("Enter name for New Project (" & Me.ProjectName & ") ")
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
            End With

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I've edited my answer to reflect this.

Author

Commented:
Excellent, John!  Thanks.  I didn't know about that InputBox option.  That code modification makes sense to me.  It looks like that would name the new duplication...   how do I also add an InputBox to change the name of the existing project?
John TsioumprisSoftware & Systems Engineer

Commented:
What do you mean by :how do I also add an InputBox to change the name of the existing project
Unless you mean
rst.Edit
rst.Fields("ProjectName") = InputBox("Enter New Name for Project :  (" & Me.ProjectName & ") ")
rst.Update

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
how do I also add an InputBox to change the name of the existing project?

You shouldn't mix this up. It is way to dangerous.
If you wish to rename the current project, do this the normal way - by having a form to edit details of the project where you can apply proper validating before saving the edits.

Author

Commented:
The goal isn't really to duplicate the existing project(s)…  it's to split them.  The existing projects are sometimes split up into different phases that weren't part of the original plan.  The existing procurement dates, charge numbers, etc. will be the same.  But the existing project name must change when it is split.  Usually we add "Phase-1" to the original and "Phase-2" to the new split-off.  But this is not always the case.  But at the time we duplicate and split, we usually need to rename that first one.

Author

Commented:
I can probably work with using an existing form like Gustav suggested.  I actually already have a name change form because they change every once in awhile due to various reasons.  I'm in an all day meeting today...   I'll test everything out when I get back to programming tomorrow and let you know if I run into issues implementing the suggested coding.  Thanks!!!
John TsioumprisSoftware & Systems Engineer

Commented:
Without full knowledge of your program/logic it would be hard to recommend but I would prefer to to have you generate 2 copies of your original Record ... manipulate them to phase1 & phase2 and then rename the old record that will show the origin of them.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then I would create a popup form where the user can edit the current name and the new name in relaxed mode - and either cancel to not change anything, or the finally press a button "Split Project".

Author

Commented:
The actual name of my project name field is PROJECT_NAME.   Should I replace your ".Name" with .PROJECT_NAME?   Otherwise, I'm not sure exactly where to put the actual name of my control that get's updated to change the project name.

Author

Commented:
I don't understand this part:

                ElseIf .PROJECT_NAME = "???" Then
                    ' Adjust name.

Open in new window


What should go in the quotes?  I got a little lost in the functionality?


Also, I'm also going to need to update the existing footage since we're splitting the line.  Here's the way I think we would incorporate that:
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .PROJECT_NAME = "Project Name" Then
                    ' Adjust name.
                    .Value = InputBox(Prompt, Title, rst.Fields(.PROJECT_NAME).Value)
                    If Value = "" Then
                        ' User entered empty name.
                        .Value = rst.Fields(.PROJECT_NAME).Value & " (Phase-1)"
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.PROJECT_NAME).Value
                End If
                
                            If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .TotalFootage = "TotalFootage???" Then
                    ' Adjust name.
                    .Value = InputBox(Prompt, Title, rst.Fields(.TotalFootage).Value)
                    If Value = "" Then
                        ' User entered empty name.
                        .Value = rst.Fields(.TotalFootage).Value & " ???"
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.TotalFootage).Value
                End If
                
                
            End With

Open in new window


I think I have most of the code right, but I don't know what goes in the places where I entered "???".  

Thanks!
John TsioumprisSoftware & Systems Engineer

Commented:
"???" --> just place your own values based on the field you want to fill.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Should I replace your ".Name" with .PROJECT_NAME?  

No. .Name is the name of the property, "PROJECT_NAME" is its value.
So, please revert to my original code. It is tested and works.

And you initially wrote:
My unique column is "ProjectName".

so how could anyone know it actually is "PROJECT_NAME". Anyway, just adjust that.

Author

Commented:
When I examined my code, I saw there was an underscore and I reported actual column name so there was no confusion about why I modified the code.

I was able to test and I got a "Data Type Conversion Error" message.  Debugging highlighted this:
.Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)

Note, this is on the part I tried to add so user can update the length of the project.  I'm sure I didn't do something correctly, because I am not a VBA expert.  I tried to highlight that bold below, but the HTML didn't seem to work.  Look for the .   Here's the code that generated that error:

        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "Project_Name" Then
                    ' Adjust name.
                    .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        ' User entered empty name.
                        .Value = rst.Fields(.Name).Value & " (Phase-1)"
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
                
                
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "TotalFootage" Then
                    ' Adjust name.
              [b]      .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)[/b]
                    If Value = "" Then
                        ' User entered empty name.
                        .Value = rst.Fields(.Name).Value
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
                
                
            End With

Open in new window

Author

Commented:
I'm assuming the Data Type error I described above is due to Project_Name being a text field then using the same coding language to populate the TotalFootage which is an integer field.  Any ideas on how I would change
.Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)

Open in new window

to make InputBox be for a number and not text?

Author

Commented:
I tried the code snippet below, but it hangs up on the first .update.   I get an error message:  "Runtime error 3146 odbc-- call failed."

    With rstAdd
        .AddNew
        For Each fldProjectName In .Fields
            With fldProjectName
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "Project_Name" Then
                    ' Adjust name.
                    .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        ' User entered empty name.
                        .Value = rst.Fields(.Name).Value & " (Phase-1)"
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
        
           End With
           Next
                .Update
                
          For Each fldTotalFootage In .Fields
            With fldTotalFootage
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "TotalFootage" Then
                    ' Adjust name.
                    .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        ' User entered empty name.
                        .Value = rst.Fields(.Name).Value
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
     
            End With
            Next
                .Update
    End With

Open in new window


Thanks!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Again, please revert to my original code. It is tested and works.

Then, if you need to adjust more fields, just include these. And if it is number, convert the text output from InputBox to a number:

    Const Title2 As String = "Total Quantity"
    Const Prompt2 As String = "Please enter the total quantity for the new project:"

    ' <snip>

    With rstAdd
        .AddNew
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "ProjectName" Then
                    ' Adjust name.
                    .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        ' User entered empty name.
                        .Value =  rst.Fields(.Name).Value & " (Phase-1)"
                    End If
                ElseIf .Name = "TotalFootage" Then
                    ' Adjust total footage.
                    .Value = Val(InputBox(Prompt2, Title2, rst.Fields(.Name).Value))
                    If Value = 0 Then
                        ' User entered no quantity.
                        .Value =  rst.Fields(.Name).Value
                    End If
                Else
                    ' Straight copy.
                    .Value = rst.Fields(.Name).Value
                End If
            End With
        Next
        .Update
    End With

Open in new window

But, again, indeed with yet a parameter to input:

Then I would create a popup form where the user can edit the current name and the new name in relaxed mode - and either cancel to not change anything, or the finally press a button "Split Project".

Or you will end up bugging the user with never ending input boxes.

Author

Commented:
Gustav, if the code worked and accomplished the goals, I would love to stop asking questions and just mark as solved.  However, I'm still getting the exact same error message as earlier "Run-time error 3146:  ODBC--call failed."  Debugging highlights .Update

Note, when running the code I was prompted to enter a TotalFootage.  I was never prompted to enter the ProjectName.  Should your code be If .Name = "TotalFootage" Then instead of starting with ElseIf?  

Here is the complete code as copied from what you gave me:

Private Sub btnSaveSplit_Click()

If Me.Dirty Then Me.Dirty = False

Const Title As String = "Project_Name"
Const Prompt As String = "Please enter name of the new project:"
Const Title2 As String = "Total Quantity"
Const Prompt2 As String = "Please enter the total quantity for the new project:"


    Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field
    Dim Bookmark    As Variant
    Dim NewName     As String

    ' Copy parent record.
    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone

    ' Move to current record.
    rst.Bookmark = Me.Bookmark
    
    With rstAdd
        .AddNew
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or UID field.
                ElseIf .Name = "Project_Name" Then
                        ' Adjust name.
                        .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        .Value = rst.Fields(.Name).Value & " (Phase-2)"
                    End If
                    
                ElseIf .Name = "TotalFootage" Then
                    ' Adjust total footage
                     .Value = Val(InputBox(Prompt2, Title2, rst.Fields(.Name).Value))
                    If Value = 0 Then
                        ' User entered no quantity.
                        .Value = rst.Fields(.Name).Value
                    End If
                Else
                    ' Straight copy from original.
                    .Value = rst.Fields(.Name).Value
                End If
        
            End With
            Next
                .Update
                
        End With
    
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark

    rst.Close
    rstAdd.Close

    ' Move to the new recordcopy.
    Me.Bookmark = Bookmark

    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing
    


DoCmd.OpenForm "PowerlineF", acNormal, , "PowerlineUID = " & Me!PowerlineUID
DoCmd.Close acForm, Me.Name

End Sub

 Sub

Open in new window


While debugging, I noticed that hovering over .name in any part of your code gives "Object variable or with block variable not set".  ...not sure if that's supposed to be the case.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I can see, that I forgot to declare Value, so insert that or you code won't compile:

    Dim Value       As Long

Open in new window

Apart from that and the trailing "sub" it seems OK.
.Name is the Name property of fld which is set in each loop of the fields collection so it cannot be empty.

You could attach a sample database.

Author

Commented:
Thank you, sir.  I declared the value, but I still got that error on .Update.  I put together a new database with some test data in it.  After running from the test application, I got a slilghtly different error.  It's asking me to populate the timestamp field.  My original table is with an ODBC link to SQL and that table did have a timestamp.  Not sure if that's causing the error in my original application.

 Please see attachment.  When you open this, please first select a project from the combo box on the top left.  Then press the button "Split Project".

2019-09-10_104629.jpgPowerlineProjects.accdb
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
If you exclude field TimeStamped:

                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or UID field.
                ElseIf .Name = "timestamped" Then
                    ' Ignore.
                ElseIf .Name = "Project_Name" Then

Open in new window

and change the field in the table to Not Required, the code will could run.

However, one or more fields don't accept dupes, so this you must correct before an Update can take place.

You have 117 fields, and it is not possible for me to find out which fields it is, not to say which non-unique values should be assigned to these.

Author

Commented:
Thanks.  I understand.  TimeStamped is like an autonumber in SQL but with a data type of "timestamp".   Other than Timestampped, Project_Name, and the primary key (PowerlineUID), there's nothing that will reject nulls.  Also, I reviewed all indexes.  Other than the primary key and Project_Name, there are no unique values.

I changed the test database to Timestamp being not required.  This time when I ran it from the test database I got an error  "Run-Time error 3022:   The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship...."

I don't have any indexes or restrictions on duplicate data except for those named above and those have been declared in the code as you instructed me.  Is it possible that maybe we should use IF instead of ElseIF?   It never prompts me to enter the Project_Name for the new project like it does for TotalFootage.  I feel like we are very close to getting this to work!   Thanks!


Private Sub btnSaveSplit_Click()

If Me.Dirty Then Me.Dirty = False

Const Title As String = "Project_Name"
Const Prompt As String = "Please enter name of the new project:"
Const Title2 As String = "Total Quantity"
Const Prompt2 As String = "Please enter the total quantity for the new project:"


    Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field
    Dim Bookmark    As Variant
    Dim NewName     As String
    Dim Value       As Long

    ' Copy parent record.
    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone

    ' Move to current record.
    rst.Bookmark = Me.Bookmark
    
    With rstAdd
        .AddNew
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or UID field.
                ElseIf .Name = "timestamped" Then
                    'ignore
                ElseIf .Name = "Project_Name" Then
                        ' Adjust name.
                        .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        .Value = rst.Fields(.Name).Value & " (Phase-2)"
                    End If
                    
                ElseIf .Name = "TotalFootage" Then
                    ' Adjust total footage
                     .Value = Val(InputBox(Prompt2, Title2, rst.Fields(.Name).Value))
                    If Value = 0 Then
                        ' User entered no quantity.
                        .Value = rst.Fields(.Name).Value
                    End If
                Else
                    ' Straight copy from original.
                    .Value = rst.Fields(.Name).Value
                End If
        
            End With
            Next
                .Update
'.Update is highlighted on debug (post-error)
                
        End With
    
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark

    rst.Close
    rstAdd.Close

    ' Move to the new recordcopy.
    Me.Bookmark = Bookmark

    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing
    


DoCmd.OpenForm "PowerlineF", acNormal, , "PowerlineUID = " & Me!PowerlineUID
DoCmd.Close acForm, Me.Name

End Sub

Open in new window

Author

Commented:
We dimensioned Value as Long.  In our code, we have
                ElseIf .Name = "Project_Name" Then
                        ' Adjust name.
                        .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then

Open in new window


That Value would be text, but the "TotalFootage" value would be Integer/number.  Is that possibly the issue?  It seems strange that Project_Name is the first code, but TotalFootage is the only prompt I get to enter values.

To help ensure it skips over the primary key, I added that as an ElseIF to make it skip over as you had instructed with similar columns.

            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or UID field.
                ElseIf .Name = "PowerlineUID" Then
                    ' Skip Autonumber or UID field.

                ElseIf .Name = "timestamped" Then


When I hover over dbAutoIncrField, the field gives the number 16.  I don' t know what that is....   it is not my primary key.  That's the only info I can get from any of the fields in debugging.

Author

Commented:
OK, I narrowed it down...    there's a data type mismatch in

                ElseIf .Name = "PROJECT_NAME" Then
                        ' Adjust name.
                        .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                    If Value = "" Then
                        .Value = rst.Fields(.Name).Value & " (Phase-2)"
                    End If



I changed that to isnull(Value) Then  

and I think it works!   I'm making some tweaks, but you gave me what I need!!!
John TsioumprisSoftware & Systems Engineer

Commented:
Value is a reserved word...better change it...
John TsioumprisSoftware & Systems Engineer

Commented:
With 117 fields i am afraid we will see you again shortly... :)
Better spend some time on Normalization.

Author

Commented:
Not all of the columns are getting copied.  I figured out why.  The recordset clone is only allowing a copy of records that are represented in the current form/query.  I'd like all columns to be copied.  The name of the source table is PowerlineT.   Is there an easy way to tweak that code to have it copy all .Fields as opposed to just what's in the current form.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes. Include all required fields in the query.

Author

Commented:
Thanks.  That's exactly what I did and everything works great.  I marked one of your posts as the verified solution.  Feel free to edit that post with my final complete working code if you'd like to provide that for future users of this website forum.


Private Sub btnSaveSplit_Click()

If Me.Dirty Then Me.Dirty = False

Const Title As String = "Project_Name"
Const Prompt As String = "What do you want to name your NEW project?"
Const Title2 As String = "Total Footage"
Const Prompt2 As String = "Please enter the total footage for the NEW project:"


    Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field
    Dim Bookmark    As Variant
    Dim NewName     As String
    Dim Value       As Long


    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone


    rst.Bookmark = Me.Bookmark
    
    With rstAdd
        .AddNew
        For Each fld In .Fields
        
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or UID field.
                ElseIf .Name = "PowerlineUID" Then
                    ' Skip Autonumber or UID field.
                ElseIf .Name = "timestamped" Then
                        'ignore
                ElseIf .Name = "PROJECT_NAME" Then
                        ' Adjust name.
                        .Value = InputBox(Prompt, Title, rst.Fields(.Name).Value)
                  '  If IsNull(Value) Then
                   '     .Value = rst.Fields(.Name).Value & " (Phase-2)"
                  '  End If
                    
                ElseIf .Name = "TotalFootage" Then
                    ' Adjust total footage
                     .Value = Val(InputBox(Prompt2, Title2, rst.Fields(.Name).Value))
                  '  If Value = 0 Then
                        ' User entered no quantity.
                   '     .Value = rst.Fields(.Name).Value
                   ' End If
                Else
                    ' Straight copy from original.
                    .Value = rst.Fields(.Name).Value
                End If
        
            End With
            
            Next
                .Update
                
        End With
    
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark

    rst.Close
    rstAdd.Close

    ' Move to the new recordcopy.
    Me.Bookmark = Bookmark

    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing
    

DoCmd.Close acForm, Me.Name

    DoCmd.OpenForm "PowerlineF"
    [Forms]![PowerlineF]![cboProjectSearch] = Me.PowerlineUID
    [Forms]![PowerlineF].Requery
    
End Sub

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Thanks for the feedback!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today