Solved Duplicate a record on a form (1 Viewer)

[zelarra821]

Hi.
I am trying to duplicate a record in a form. I have managed to copy them using the following code:

CurrentDb.Execute "Insert into TPresupuestos (CodigoPresupuesto, CodigoCliente, FechaSolicitud, Observaciones, Año, CodigoComercial, CodigoFormaDePago, Transporte, Montaje, Obra, CodigoEstado, EsDeposito, Deposito, PorcentajeDeBeneficio, CodigoFactura, CodigoIVATransporte, CodigoIVAMontaje, IVAPresupuestos, TransporteProrrateado, CodigoTipoDePresupuesto, CodigoProveedor, EsTransporteInternacional ) " _
                    & " values ('" & CodigoPresupuesto & "', '" & Me.CodigoCliente & "', #" & Me.FechaSolicitud & "#, '" & Me.Observaciones & "', " & Me.Año & ", '" & Me.CodigoComercial & "', '" & Me.CodigoFormaDePago & "', " & Me.Transporte & ", " & Me.Montaje & ", '" & Me.Obra & "', '" & Me.CodigoEstado & "', " & Me.EsDeposito & ", " & Me.Deposito & ", " & Me.PorcentajeDeBeneficio & ", '" & Me.CodigoFactura & "', '" & Me.CmbIVATransporte & "', '" & Me.CmbIVAMontaje & "', '" & 1 & "', " & Me.TransporteProrrateado & ", '" & Me.CodigoTipoDePresupuesto & "', '" & Me.CodigoProveedor & "', " & Me.ChkTransporteInternacional & ")"

CurrentDb.Execute "Insert into TPresupuestosSubtabla (CodigoPresupuesto, Cantidad, Caracteristicas, Concepto, Precio, CodigoIVA, Imagen, Posicion, EsPorcentajeDeBeneficio, PorcentajeDeBeneficioArticulo ) " _
                    & " values ('" & CodigoPresupuesto & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Cantidad & ", '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Caracteristicas & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Concepto & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!TxtPrecioCoste & ", '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!CmbIVA & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Imagen1 & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Posicion & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!ChkEsPorcentajeDeBenficioArticulo & ", " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!TxtPorcentajeDeBeneficioArticulo & ")"

But I have two problems:

1. Although I can see the copied record in the table, it does not appear in the form. I guess I will have to update the origin of the form, but I don't know how to do it.

2. As you can see in the code, it copies two things. The record of the main table, and then you should copy the ones you have associated with the main table, there are one, two, three or whatever. However, I only managed to get him to copy one. How can I get all associated with the main table copied?

Thank you very much.

 

[CJ_London]

but I don't know how to do it.

assuming your code is in the form you want to requery after your execute code put

me.requery

 

[zelarra821]

Thank you very much, but that was the first thing I tried and it doesn't work.

 

[Pat Hartman]

1. Is there a filter on the form that is preventing the new record from showing?
2. The child table insert should use an append query that copies from the table, NOT from the form. The child insert is inserting only a single record since that is all you can reference at one time using Forms!yourform!yoursubform.form!control

 

[The_Doc_Man]

This appears to be a table/subtable situation and I am guessing from the syntax that it is a parent form/child form case as well. Further, the use of the control names implies that the fields are bound. At least prima facie, the INSERT INTO queries look OK to me.

If you add records to a table, that has NO effect on the current form (even if bound to the table) because it has its own internal recordset and is positioned at the same place it was before you did the insertion. I.e. the DB.Execute of an INSERT INTO sequence doesn't touch an already-open recordset. So you need to NAVIGATE to the two new records. Which means you probably need to FIRST tell the forms to .Requery (so that the recordsets now contain the new data) THEN you need to tell them to do a .GoTo Record operation. It should be enough to requery the parent form to trigger a requery in the linked subform. Then you should be able to navigate the parent form and if the subform is properly linked, that should also be affected. HOWEVER, if the linked field in the parent doesn't UNIQUELY link to a record in the subform, it might revert to the first matching child record. Which means explicit .GoTo Record operations for both forms in that case.

Hope that wasn't too confusing.

 

[zelarra821]

Sure enough, @Pat Hartman, there was a filter. I've already removed it. To do this, I have tried two different codes. The one I showed you before, that does create a new record in the table, and this one:

'strSQL = "INSERT INTO TPresupuestos ( id, CodigoPresupuesto, CodigoCliente, FechaSolicitud, Observaciones, Año, CodigoComercial, CodigoFormaDePago, Transporte, Montaje, Obra, CodigoEstado, EsDeposito, Deposito, PorcentajeDeBeneficio, CodigoFactura, CodigoIVATransporte, CodigoIVAMontaje, IVAPresupuestos, TransporteProrrateado, CodigoTipoDePresupuesto, CodigoProveedor, EsTransporteInternacional )"
'strSQL = strSQL & " SELECT id, '" & CodigoPresupuesto & "' As ElCodigoPresupuesto, CodigoCliente, FechaSolicitud, Observaciones, Año, CodigoComercial, CodigoFormaDePago, Transporte, Montaje, Obra, CodigoEstado, EsDeposito, Deposito, PorcentajeDeBeneficio, CodigoFactura, CodigoIVATransporte, CodigoIVAMontaje, IVAPresupuestos, TransporteProrrateado, CodigoTipoDePresupuesto, CodigoProveedor, EsTransporteInternacional"
'strSQL = strSQL & " FROM TPresupuestos"
'strSQL = strSQL & " WHERE CodigoPresupuesto ='" & CodigoPresupuesto1 & "'"

'DoCmd.RunCommand acCmdSaveRecord
'CurrentDb.Execute strSQL, dbFailOnError

'strSQL = "INSERT INTO TPresupuestosSubtabla ( ID, CodigoPresupuesto, Cantidad, Caracteristicas, Concepto, Precio, CodigoIVA, Imagen, Posicion, EsPorcentajeDeBeneficio, PorcentajeDeBeneficioArticulo )"
'strSQL = strSQL & " SELECT id, '" & CodigoPresupuesto & "' As ElCodigoPresupuesto, Cantidad, Caracteristicas, Concepto, Precio, CodigoIVA, Imagen, Posicion, EsPorcentajeDeBeneficio, PorcentajeDeBeneficioArticulo"
'strSQL = strSQL & " FROM TPresupuestosSubtabla"
'strSQL = strSQL & " WHERE CodigoPresupuesto ='" & CodigoPresupuesto1 & "'"

'DoCmd.RunCommand acCmdSaveRecord
'CurrentDb.Execute strSQL, dbFailOnError

What it does is replace one record with another.

I'm going to record a video for you to see. I will make the effort to explain it in English, which is not my language.

File on MEGA

mega.nz

 

[Gasman]

PMFJI
You never insert the autonumber, Access does that for you.? You appear to be doing that?, though I would have thought Access would complain as ID already exists.?
Just use the other fields.?

 

[zelarra821]

Hi @Gasman, for that reason I have removed the id, because I understand that Access does it. Thank you!

 

[The_Doc_Man]

Even if you are replacing the current record, it doesn't exist in the form's recordset until you force a form .Requery. The rules for shared table access ALSO apply to this case where you are sharing the record between a query and the form (which has its own implied query.) In effect you are sharing the table with yourself via two channels of modification.

When you are modifying a recordset that is also open elsewhere, for the brief time that both recordsets are open, there CAN be two copies of the recordset. One that contains your changes, the other that does not. When you close one, any modifications become available to the other recordset if you wait long enough for the automatic requery that is one of the File>>Options>>Current Database settings, OR until you force a .Requery.

 

[arnelgp]

you can use the code of Mr.Allen Browne here:

Microsoft Access tips: Duplicate the record in form and subform

How to duplicate the current record on a form in a Microsoft Access database, along with the related records in the subform.

allenbrowne.com

 

[zelarra821]

Good Morning. Mixing codes from here and there, testing, failing, I have come to this code:

Private Sub CmdCopiar_Click()
Dim strSql As String
Dim lngID As String

Dim vUltimo As Variant
Dim vAño As Long
Dim ElCodigoPresupuesto As String
  
    Me.Filter = ""
    Me.FilterOn = False
    'Me.Requery
  
    'Le damos a la variable vAño el valor del año actual: Las dos cifras finales
    vAño = Val(Right(Year(Date), 2))
    'vUltimo es igual al Número de Registros del año +1, que se sumará abajo
    vUltimo = Nz(DCount("[CodigoPresupuesto]", "[TPresupuestos]", "[Año] = " & Year(Date)), 0)
    'Si vUltimo es nulo, es porque no hay ningún NumJustifica, entonces le damos valor 0
    If IsNull(vUltimo) Then
        vUltimo = 0
    End If
    'Sumamos 1 al valor anterior
    vUltimo = vUltimo + 1
    ElCodigoPresupuesto = "P-" & vAño & "-" & Format(vUltimo, "00000")

If Me.Dirty Then
    Me.Dirty = False
End If

If Me.NewRecord Then
    MsgBox "Select the record to duplicate."
Else
    With Me.RecordsetClone
        .AddNew
            !CodigoPresupuesto = ElCodigoPresupuesto
            !CodigoCliente = Me.CodigoCliente
            !FechaSolicitud = Me.FechaSolicitud
            !Observaciones = Me.Observaciones
            !Año = Year(Date)
            !CodigoComercial = Me.CodigoComercial
            !CodigoFormaDePago = Me.CodigoFormaDePago
            !Transporte = Me.Transporte
            !Montaje = Me.Montaje
            !Obra = Me.Obra
            !PorcentajeDeBeneficio = Me.PorcentajeDeBeneficio
            !Deposito = Me.Deposito
            !CodigoIVATransporte = Me.CmbIVATransporte
            !CodigoIVAMontaje = Me.CmbIVAMontaje
            !EsTransporteInternacional = Me.ChkTransporteInternacional
            !TransporteProrrateado = Me.TransporteProrrateado
            !CodigoTipoDePresupuesto = Me.CodigoTipoDePresupuesto
            !CodigoProveedor = Me.CodigoProveedor
        .Update

        .Bookmark = .LastModified
        lngID = !CodigoPresupuesto
              
        If Me.[FPresupuestosSubformulario].Form.RecordsetClone.RecordCount > 0 Then
            Me.FPresupuestosSubformulario.SetFocus
            For i = 1 To Me.FPresupuestosSubformulario.Form.RecordsetClone.RecordCount
                CurrentDb.Execute "Insert into TPresupuestosSubtabla (CodigoPresupuesto, Cantidad, Caracteristicas, Concepto, Precio, CodigoIVA, Imagen, Posicion, EsPorcentajeDeBeneficio, PorcentajeDeBeneficioArticulo ) " _
                                & " values ('" & ElCodigoPresupuesto & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Cantidad & ", '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Caracteristicas & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Concepto & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!TxtPrecioCoste & ", '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!CmbIVA & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Imagen1 & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Posicion & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!ChkEsPorcentajeDeBenficioArticulo & ", " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!TxtPorcentajeDeBeneficioArticulo & ")"
                DoCmd.GoToRecord , , acNext
            Next i
            Me.CmdCopiar.SetFocus
        Else
            MsgBox "Main record duplicated, but there were no related records."
        End If
      
        Me.Bookmark = .LastModified
    End With
End If

Exit_Handler:
    Exit Sub
  
Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler

End Sub

This code copies me the record of the main form and the first one of the subform without problems.

The problem is in the subform. Here:

CurrentDb.Execute "Insert into TPresupuestosSubtabla (CodigoPresupuesto, Cantidad, Caracteristicas, Concepto, Precio, CodigoIVA, Imagen, Posicion, EsPorcentajeDeBeneficio, PorcentajeDeBeneficioArticulo ) " _
& " values ('" & ElCodigoPresupuesto & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Cantidad & ", '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Caracteristicas & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Concepto & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!TxtPrecioCoste & ", '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!CmbIVA & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Imagen1 & "', '" & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!Posicion & "', " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!ChkEsPorcentajeDeBenficioArticulo & ", " & Forms![FPresupuestos]![FPresupuestosSubformulario].Form!TxtPorcentajeDeBeneficioArticulo & ")"

If I leave it like that it tells me that the number of query values and the number of target fields are different.

I've added breakpoints, and I've recorded a video so you can see what it does:

File on MEGA

mega.nz

If I change the number of times it has to go through the subform to one, it works perfectly for me. So I just have to polish up that bug it's giving me.

What am I doing wrong?

 

[arnelgp]

you did not Totally follow mr.Brownes code.
review your code of mr.browne.

you do not need to Loop on each record of the subform.
only use Criteria.

nobody is interested in videos, upload a sample db.
or better a New Movie.

maybe a sample will stir you up.

 

[zelarra821]

It has cost, but I have succeeded. This is the code:

Private Sub CmdCopiar_Click()
Dim vUltimo As Variant
Dim vAño As Long
Dim ElCodigoPresupuesto As String
Dim strSql As String
Dim lngID As String
    
    'Le damos a la variable vAño el valor del año actual: Las dos cifras finales
    vAño = Val(Right(Year(Date), 2))
    'vUltimo es igual al Número de Registros del año +1, que se sumará abajo
    vUltimo = Nz(DCount("[CodigoPresupuesto]", "[TPresupuestos]", "[Año] = " & Year(Date)), 0)
    'Si vUltimo es nulo, es porque no hay ningún NumJustifica, entonces le damos valor 0
    If IsNull(vUltimo) Then
        vUltimo = 0
    End If
    'Sumamos 1 al valor anterior
    vUltimo = vUltimo + 1
    ElCodigoPresupuesto = "P-" & vAño & "-" & Format(vUltimo, "00000")

    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    If Me.NewRecord Then
        MsgBox "Selecciona el presupuesto que quieres copiar.", vbInformation, NombreBD
    Else
        With Me.RecordsetClone
            .AddNew
                !CodigoPresupuesto = ElCodigoPresupuesto
                !CodigoCliente = Me.CodigoCliente
                !FechaSolicitud = Me.FechaSolicitud
                !Observaciones = Me.Observaciones
                !Año = Year(Date)
                !CodigoComercial = Me.CodigoComercial
                !CodigoFormaDePago = Me.CodigoFormaDePago
                !Transporte = Me.Transporte
                !Montaje = Me.Montaje
                !Obra = Me.Obra
                !PorcentajeDeBeneficio = Me.PorcentajeDeBeneficio
                !Deposito = Me.Deposito
                !CodigoIVATransporte = Me.CmbIVATransporte
                !CodigoIVAMontaje = Me.CmbIVAMontaje
                !EsTransporteInternacional = Me.ChkTransporteInternacional
                !TransporteProrrateado = Me.TransporteProrrateado
                !CodigoTipoDePresupuesto = Me.CodigoTipoDePresupuesto
                !CodigoProveedor = Me.CodigoProveedor
            .Update
    
            .Bookmark = .LastModified
            lngID = !CodigoPresupuesto
                            
            If Me.[FPresupuestosSubformulario].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO TPresupuestosSubtabla ( CodigoPresupuesto, Cantidad, Caracteristicas, Concepto, Precio, CodigoIVA, Imagen, Posicion, EsPorcentajeDeBeneficio, PorcentajeDeBeneficioArticulo )"
                strSql = strSql & " SELECT '" & lngID & "' As ElCodigoPresupuesto, Cantidad, Caracteristicas, Concepto, Precio, CodigoIVA, Imagen, Posicion, EsPorcentajeDeBeneficio, PorcentajeDeBeneficioArticulo"
                strSql = strSql & " FROM TPresupuestosSubtabla"
                strSql = strSql & " WHERE CodigoPresupuesto ='" & Me.CodigoPresupuesto & "'"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Formulario principal copiado, pero no hay registros relacionados.", vbInformation, NombreBD
            End If
            
            Me.Bookmark = .LastModified
        End With
    End If
End Sub

Thank you very much to all, and to you especially, @arnelgp, for the example. It has helped me the most.