A "Copy/Paste" feature into a new record row.....

[kcdoell]

Good morning everyone:

I created a form and set the default view as a continuous form. Basically the form is displaying records in which the user can add or edit new ones. The record source for this form is a query that I built that is based on a table.

I have been working on this for several weeks and now I have been told that many times when a user wants to create a new record, much of the information that is displayed in a preexisting record is the same. Therefore, instead of rekeying in the same information (only making changes on a couple of the fields) they would like to copy the preexisting row and paste it into the “new” record row that is being displayed (the last row that is displayed) much like one would do in Excel.

Currently I have a before update event for writing new records:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'When a user is creating a new record the following code inserts the MonthID, YearID and
'The LocationsID.  It does a Dlookup for the Locations ID when the control cboLocation is
'blank.
 
Dim frm As Form
Set frm = Forms!Forecast
 
If IsNull(frm![Binding_Percentage]) Then
MsgBox ("You need to select a Binding Percentage before you create a new record")
Cancel = True
Else: Cancel = False
End If
 
If Me.NewRecord Then
  'If cboLocation is Not Null, grab the value from there
  If Not IsNull(frm![cboLocation]) Then
    JtnLocationsID = frm!cboLocation
    YearID = frm!CboYear
    MonthID = frm!CboMonth
  Else      'Forms!Forecast![cboLocation] is Null
    'Check and see if all 3 Controls have values in them
    If Not IsNull(frm![cboDivision]) And Not IsNull(frm![cboWrkReg]) And _
       Not IsNull(frm![cboCreditReg]) Then       'values in all 3 Controls
         JtnLocationsID = DLookup("[JtnLocationsID]", "tblLocationsMM", "[DivisionIDFK] =" & frm![cboDivision] & _
                          " And [WrkRegIDFK] =" & frm![cboWrkReg] & " And [CreditRegIDFK] =" & _
                          frm![cboCreditReg])
         YearID = frm!CboYear
         MonthID = frm!CboMonth
    Else
      'no value in [cboLocation], and 1 or more values are missing in [cboDivision],
      '[cboWrkReg], or [cboCreditReg]
 
      MsgBox "1 or more values are missing in"
 
    End If
  End If
End If
End Sub
 

I played around and discovered that I could, using the mouse, right click in one of the rows and select “copy” and then right click in the last row (the new record line) and select “paste”. Actually this was pointed out to me by one of the people I am working with. What happens though, is that the paste action does not take and it creates a “blank” record of which I can then right click in that row (the newly created “blank” one) and paste again the info. Then it works. I say “blank” because it actually did create a record on my table with the YearID, MonthID, & JtnLocationsID via the above mentioned code. These are fields that are not in the displayed rows, hence why I created the code to write new records to the table.

Given the copy and paste mindset of my end user what would be the best approach to easily satisfy their desire to not have to rekey much of the information that is already in previously displayed rows? I am relatively new at this so don’t know what the best solution. One thing to mentioned is that this particular table does not have any fields where duplicates are not allowed. I guess that is one less issue to deal with.

Does anybody have ideas on how they would approach this?

Thanks,

Keith :-)

[missinglinq]

Here's some sample code to carry selected values from an existing record into a new record, based on a command button placed in the header, but I suppose it could be in the footer.

Private Sub CopyPartialRecord2NewRecordButton_Click()
 
‘Assign field values to be carried forward to variables
MyFirstField = Me.FirstField
MySecondField = Me.SecondField
MyThirdField = Me.ThirdField
 
'Go to a new record
DoCmd.GoToRecord , , acNewRec
 
'Plug in old values from variables to new record
Me.FirstField = MyFirstField
Me.SecondField  = MySecondField 
Me.ThirdField = MyThirdField 
 
End Sub

You can modify it to check for Null values before copying, if you desire.

Linq ;0)>

[kcdoell]

Linq:

In my continuous form, if I select a row a small arrow button displays in the far left. Actually, you can see this in the attached image. It does not for say highlight visually all the fields in the row. Does that arrow icon mean that it is highlighting all fields in that particular row so I can reference any of the fields via the code?

Thanks for the reply,

Keith.

[kcdoell]

Here's some sample code ....................

Private Sub CopyPartialRecord2NewRecordButton_Click()
 
‘Assign field values to be carried forward to variables
MyFirstField = Me.FirstField
MySecondField = Me.SecondField
MyThirdField = Me.ThirdField
 
'Go to a new record
DoCmd.GoToRecord , , acNewRec
 
'Plug in old values from variables to new record
Me.FirstField = MyFirstField
Me.SecondField  = MySecondField 
Me.ThirdField = MyThirdField 
 
End Sub

You can modify it to check for Null values before copying, if you desire.

Linq ;0)>

Linq:

So would I have to define the variables like the following:

MyFirstField = Me.LOB
MySecondField = Me.UW
MyThirdField = Me.Policy_Type
 

etc......

[missinglinq]

This is not actually doing a Copy & Paste, but is assigning the fields to variables, going to a new record, then assigning the value of the variables back to the appropriate fields in the new record, so the fields don't have to be hilighted.

The arrow on the left indicates that particular record is the current record, and the sample code will then copy whichever fields of that record you've assigned it in the code. You simply need to replace Me.FirstField, Me.SecondField, etc. with the actual names for the textboxes you wish to copy.

Linq ;0)>

[missinglinq]

Sorry, we cross posted! Yes, you need to assign the variables just like it did in Post # 4. If you need more than the example showed, just make up more variable names, like MyFourthField, MyFifthField.

Linq ;0)>

[kcdoell]

Thanks, I have a meeting now but I will try that solution and get back to you on my result.

Thanks a lot...

Keith

[kcdoell]

Linq:

Finally got a chance to apply your idea. It worked perfectly! Below is the final solution:

 
Private Sub cmdCopyPasteRec_Click()
 
'When the user click on the command button it will copy and paste the highlighted record
 
'Confirm that the user wants to copy the record.
    If MsgBox("You are about to copy the highlighted record," & _
            " Click the ok button to proceed, if not hit cancel", vbOKCancel, vbDefaultButton2) = vbOK Then
 
'If yes, assign field values to be carried forward to variables
 
    MyField_1 = Me.Policy_Type
    MyField_2 = Me.Effective_Date
    MyField_3 = Me.Expiration_Date
    MyField_4 = Me.Policy_Number
    MyField_5 = Me.Insured_Name
    MyField_6 = Me.UW
    MyField_7 = Me.Broker
    MyField_8 = Me.LOB
    MyField_9 = Me.JtnLocationsID
    MyField_10 = Me.YearID
    MyField_11 = Me.MonthID
 
'Go to a new record
    DoCmd.GoToRecord , , acNewRec
 
'Plug in old values from variables to new record
 
    Me.Policy_Type = MyField_1
    Me.Effective_Date = MyField_2
    Me.Expiration_Date = MyField_3
    Me.Policy_Number = MyField_4
    Me.Insured_Name = MyField_5
    Me.UW = MyField_6
    Me.Broker = MyField_7
    Me.LOB = MyField_8
    Me.JtnLocationsID = MyField_9
    Me.YearID = MyField_10
    Me.MonthID = MyField_11
 
    End If
End Sub
 
 

Once again thanks a lot.

I also thought it would be nice if I could get the entire row that is selected to highlight. I put another post out there but no response so far. I saw that I could go to the properties of a field and set the backcolor using the get focus event. That works it highlights the entire column of that field if there are multiple rows displayed. Is there a way to get just the entire row to highlight (the one that is displaying the arrow). I am thinking that this visual is better for the end user rather than the default arrow that displays and I could have an error check to make sure that a row has been selected

Thanks,

Keith.