Solved Duplicate the record and let user edit it in popup form

[gringope24]

Hi Folks,
I have continuous form which shows the result of the measurements made by technician. You can add measuremnt by clicking Add Measurement, then Pop up form appears to input data. I want to add possibility to that user will duplicate selected record and further edit/adjust it in the same pop up form.
I figured out fallowing solution.

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.OpenForm "frmMeasuresInput", , , , acFormAdd
    DoCmd.RunCommand acCmdPasteAppend

Measurements are collected in Protocols the only restriction is that 1 Protocol have max 3 records, and each record must have unique values of PoleFK: LA or LB or LC, . It is not allowed to have LA, LA, LC. This validation if specific PoleFK already exist in Protocol is done in BeforeUpdate event of the Pop up Form.

Therefore when user click Duplicate and Pop up appears, he must change PoleFK value, if not pop up will prompt a message.

Do you think thath it is good way to do it?


Continous form:

Pop up:

 

[arnelgp]

first before running the duplicating record code, check how many records you already have for the specific protocolFK value.
if you already have 3 (which means LA, LB, LC is already there), prevent the duplication.

if there is less than 3 then go with the duplication.

on your form you add code (BeforeUpdate event) on poleFK textbox/combobox to make sure that the selection was not already in your table.
you can do this by using dcount() function:

private sub poleFK_BeforeUpdate(Cancel As Integer)
Cancel = (DCount("1", "yourTable", "poleFK = " & Me!poleFK) <> 0)
If Cancel Then
Msgbox "poleFK, " & Me!poleFK & " is already in the table for this protocol!"
End If
end sub

 

[Pat Hartman]

Also, if your original form is where the records are normally added and changed, do NOT create a second popup to use for copying. Do the copy using the recordSource of the form with the copy button and go to the new record. That way you will have only ONE form where you need validation logic to prevent bad records from being saved. You do validate your data, right?

 

[gringope24]

@arnelgp
I found some issues with this method.
Seems that it copies data and control names, not field names. Therefore, when controls in continous form and input form have different names, then this method does not work and error appears. Moreover having validation rule for controls also prevents to paste data efficentcy.

@Pat Hartman
I am planning to have form with the same validation rules to add/change/copy the record.
I wanted to check your idea. I create with wizard btnDuplicate, but when I have validation rule on combobox control, then I also receive message that pasting is not completed.

Shall I put validation rules on form event level or keep validation for each control seperately?

 

[Pat Hartman]

VBA works better than macros. This is part of a procedure that copies a set of data. The first part uses the recordset clone method and that is all I left.

Private Sub cmdCopyQuote_Click()
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim str2Sql As String
Dim strSQL As String 'SQL statement.
Dim NewQuoteID As Long 'Primary key value of the new record.
Dim OldQuoteID As Long
Dim NewQuoteDetailID As Long
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim FromQD As DAO.QueryDef
Dim FromRS As DAO.Recordset
Dim ToTD As DAO.TableDef
Dim ToRS As DAO.Recordset

'Save any 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."
Exit Sub
End If


'Duplicate the main record: add to form's clone.
OldQuoteID = Me.QuoteID
With Me.RecordsetClone
.AddNew
!CustID = Me.cboCustID
!JobName = Me.JobName
!Notes = Me.Notes
!TermsID = Me.TermsID
!ShippingID = Me.ShippingID
!CustConID = Me.cboCustConID
!CustLocID = Me.cboCustLocID
!QuoteNumber = Nz(DMax("QuoteNumber", "tblQuotes"), 0) + 1
!QuoteDate = Date
!Expires = Date + 30
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
NewQuoteID = !QuoteID
'Display the new duplicate.
Me.Bookmark = .LastModified
End With

 

[gringope24]

I am able to duplicate record using below code, but when I display the new record it is already inserted and BeforeUpdate does not work to validate PoleFK Field.

Is I write proviously, my idea is to duplicate record and present it user for further edit.

In this situation my record is not dirty so validations in BeforeUpdate events does not work

Private Sub btnDuplicate_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field

  If Me.NewRecord = True Then Exit Sub

  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              Else
                ' All other fields.
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

 

[Pat Hartman]

Of course the beforeUpdate isn't working, you are adding the record directly to the recordsource and are bypassing all the form events. If the existing record was valid, the copied record will also be valid as long as you include all the required columns.

If you want the data to pass through the form's events, you need to do this differently.
1. copy the ID of the record you want to copy
2 go to a new record.
3. open a recordset using the copied ID
4. move each field to the form's controls in the click event of a button.
5. When you save the record, the BeforeUpdate event will run and assuming that is where your validation code is, it will run.

 

[arnelgp]

here is a demo of your record-duplication.

 

[Uncle Gizmo]

Your question reminded me of a previous question on the forum which I answered here:-

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

Extract:-
The method sets the textbox default value. Now you can see the information and vet it without saving it to the table.

This method allows you to open a form, ready to enter a new record with some of the text boxes already filled with information. The user can then update the text. The action of making an update to the text saves the record, your changes, and the "default values".

 

[gringope24]

Thanks All for ideas, I need to evaluate given propositions.
I have been learning ms Access for 2 years and every time there are some new things to learn. From one hand it is great because it is constant learning, but sometimes i am thinking if it is worth to invest time in it or to switch to another technology....

 

[Uncle Gizmo]

sometimes i am thinking if it is worth to invest time in it or to switch to another technology....

I have had similar thoughts over the years.

A couple of years back I had a look at building mobile apps with flutter. The attraction being that Flutter would build both Android and Apple apps at the same time, and indeed now you have a Windows interface. I succeeded in building a basic calculator by following a YouTube video. However I had great difficulty in grasping how to put my own app together from basics.

I recently fired up Visual Studio again to have an other go with flutter and discovered that they've changed everything! My original calculator app will not work without some fiddling around with it so I've left it for now, I can't be assed to jump through a load of what I consider unnecessary hoops... And this is what I found with the leading edge stuff, it moves significantly and regularly, requiring rebuilding of your old stuff again. Google app scripts was terrible in this regard... So the incentive to use, and learn something new quickly wanes. You don't get this so much with a mature product like MS Access and VBA. I'll hold off on Flutter until it's matured a bit more!

 

[Uncle Gizmo]

i am thinking if it is worth to invest time in it or to switch to another technology....

This looks Good, (not watched it myself yet)

Learn JavaScript by Building 7 Games - Full Course​

 

[Pat Hartman]

but sometimes i am thinking if it is worth to invest time in it or to switch to another technology....

?? Why would switching to a different technology change your learning curve? In fact, Access does so much for you, that your learning curve with a different technology would be much steeper. We are not born knowing everything. Every day our knowledge grows if we keep our mind open. Unless you need some functionality that Access does not provide, like web access from anonymous clients, then learn Access but try to understand the "Access" way.

 

[gringope24]

here is a demo of your record-duplication.

I took you solution as an example and make some changes in code, so that it is better understand by me. I paste it below just for information.

Moreover I check if measurement already exists for specific PoleFK exist in the in the event BeforeUpdate in the form level. For duplicating or for adding a record it works very well.
Nevertheless I am planning that user will be able to edit existing record via Pop Up form, where are buttons 'Cancel', 'Save(and Exit'), 'Save and New'. Unfortunatelly when user make changes in any of the field and then click one of the Saves Buttons, than BeforeUpdate on the form level is also activated and prevent saving. Do have similar problem or maybe should i change the way how I imagine that user will be edit records?
I want to make everything in pop so because user is more aware if he saves or cancel changes (by pressing save or cancel buttons)

Code of 'Save' Button:

Private Sub btnSave_Click()
        Form_BeforeUpdate (0)
        If blnOkToClose Then
            DoCmd.Close acForm, Me.Name
        End If
End Sub

Public Function CloneFormRecord( _
                                ByRef srcForm As Form, _
                                ByVal srcPKField As String, _
                                ByVal srcPKValue As Variant, _
                                ByRef trgForm As Form)
                                
    On Error GoTo Err_CloneFormRecord

    Dim rs1 As DAO.Recordset
    Dim fld As DAO.Field
    Dim ctrl As Control
    Dim crit As String
    Set rs1 = srcForm.Form.RecordsetClone
    If IsNumeric(srcPKValue) Then
        crit = srcPKField & " = " & srcPKValue
    Else
        crit = srcPKField & " = '" & srcPKValue
    End If
    
    rs1.FindFirst crit
'    On Error Resume Next

    For Each fld In rs1.Fields
        If (fld.Attributes And dbAutoIncrField) Then
            ' do nothing
        Else
            For Each ctrl In trgForm.Controls
                If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
                    If ctrl.ControlSource = fld.Name Then
                            ctrl = fld
                    End If
                End If
            Next
        End If
    Next
    
    rs1.Close
    Set rs1 = Nothing
    
    
Exit_CloneFormRecord:

  DoCmd.SetWarnings True
  Exit Function

Err_CloneFormRecord:

  MsgBox Err.Description
  Resume Exit_CloneFormRecord
    
End Function

 

[arnelgp]

if you want to Edit the record, Create another form for that.
don't make the Form as All-Around solution.
and have another button to Open your "Edit Form".

 

[gringope24]

@arnelgp Yes, this is a solution. But I am dissapointed that I need to use somekind for the "trick" for very common functionality. I feel that once again I am going in opposite way than Access seemed to work.

 

[Pat Hartman]

I am going in opposite way than Access seemed to work.

Unlike in a spreadsheet, we rarely copy data from one record to another in an application based on a relational database. Common data belongs in a parent table so you won't find any built in functions to simplify this. I can't tell whether you have a schema design flaw or you are trying to make data entry more convenient. I'll give you the benefit of the doubt and assume you are trying to simplify data entry. The question is - did someone specifically ask for this because they find data entry tedious or did you come up with this yourself because it was tedious for you to create test data and rather than enter discrete values, you want to copy most values.

You can always use cntl-' to copy the line above. There is also a shortcut for today's date which escapes me at the moment but I'm sure one of us can find all the shortcuts available in Access.

Personally, in most cases, i think it is better and provides a better work flow if people just type in values or use cntl-' to copy a specific value. There are situations where I will copy a block of records. For example, if a teacher gives a test, I will give him an option to create a test record for each student so he presses a button and gets 30 rows tied to a test and student and he just has to enter a grade. The grade is created as null to make it obvious which are missing. If I copy the scores from the last test, for example, it is very difficult to keep track of what has been updated and what has not. So, think long and hard about "helpful" features like copying entire rows.

 

[gringope24]

OK, all in all I had to confronte my ideas with pragmatism and hints from you. I decided to omit Pole FK Control when duplicating the record (code below). At the same time I made validation in the event BeforeInstert on form level (check if user fill fthe PoleFK field) on on control level (check if user put correct pole name, which have not already been inserted).

I think this thread and discussion was very constructive and valuable for me and I evaluate it as solved

Public Function CloneFormRecord( _
                                ByRef srcForm As Form, _
                                ByVal srcPKField As String, _
                                ByVal srcPKValue As Variant, _
                                ByRef trgForm As Form)
                                
    On Error GoTo Err_CloneFormRecord

    Dim rs1 As DAO.Recordset
    Dim fld As DAO.Field
    Dim ctrl As Control
    Dim crit As String
    Set rs1 = srcForm.Form.RecordsetClone
    If IsNumeric(srcPKValue) Then
        crit = srcPKField & " = " & srcPKValue
    Else
        crit = srcPKField & " = '" & srcPKValue
    End If
    
    rs1.FindFirst crit

    For Each fld In rs1.Fields
        Debug.Print fld.Name
        If (fld.Attributes And dbAutoIncrField) Then
            ' do nothing
        ElseIf fld.Name = "PoleFK" Then
            ' do nothing
        Else
            For Each ctrl In trgForm.Controls
                If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
                    If ctrl.ControlSource = fld.Name Then
                            ctrl = fld
                    End If
                End If
            Next
        End If
    Next
    
    rs1.Close
    Set rs1 = Nothing
    
    
Exit_CloneFormRecord:

  DoCmd.SetWarnings True
  Exit Function

Err_CloneFormRecord:

  MsgBox Err.Description
  Resume Exit_CloneFormRecord
    
End Function