Duplicate a record 1

[Salsaboy60]

Hello,

I have a form Orders (with sub-form) and in case of a delivery less then qte ordere (back-order) I like to duplicate the order line and create a backorder.
I try to do so with a duplicate recordset and i tryed with an query Insert Into, but in both cases I get errors.

I am not very at ease with this kind of work and it would be nice if someone could help me, or indicate me where i am mistaken.

The code for the recordset is :

Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!rqtDuplication 'Query where update is going to be
    Set rstAdd = qd.OpenRecordset
    Set rst = Me.RecordsetClone

       
        rst.AddNew
        rst!N°Document = Document
        rst!N°IdProduit = Me.N°IdProduit
        rst!Désignation = Nz(Me.Désignation)
        rst![Qte Commandee] = Nz(Me.Qte_Commandee)
        rst!Mouvements = Nz(Me.Mouvements)
        rst!Emplaçement = Nz(Me.Emplaçement)
        rst![Date modification] = Nz(Me.DateModification)
        rst!IDMagasin = Me.N°IdMagasin
        rst.Update
        
       
   rst.Close
   CurrentDb.Close

I get a error on : Set rstAdd = qd.OpenRecordset


And the case with sql is like this :

Dim strSql As String
  Dim strN°Document As String
  Dim strN°IdProduit As String
  Dim strDésignation As String
  Dim strQteCmd As String
  Dim strMouvements As String
  Dim strEmplacement As String
  Dim strDateModification As String
  Dim strprixAchatUHT As String
  Dim strIDMagasin As String
  
  
   strN°Document = DMax("[BackOrderID]", "BackOrder")
   strN°Document = strN°Document + 1
   strN°IdProduit = Me.N°IdProduit
   strDésignation = Nz(Me.Désignation)
   strQteCmd = Nz(Me.Qte_Commandee)
   strMouvements = Nz(Me.Mouvements)
   strEmplaçement = Nz(Me.Emplaçement)
   strDateModification = Nz(Me.DateModification)
   strIDMagasin = Me.N°IdMagasin
    
  strSql = "INSERT INTO Detailstock (" & strN°Document & "," & strN°IdProduit & "," & _
           strDésignation & "," & strQteCmd & "," & strMouvements & "," & strEmplacement & "," & _
           strDateModification & "," & strIDMagasin & ")"
  strSql = strSql + " SELECT DetailStock.N°Document, DetailStock.N°IdProduit, DetailStock.Désignation, " & _
           '"DetailStock.[Qte Commandee], DetailStock.Mouvements, DetailStock.Emplaçement, " & _
          "DetailStock.[Date modification], DetailStock.N°IDMagasin FROM DetailStock;"
  
 DoCmd.RunSQL strSql

The error is on the line DoCmd.RunSql strSql.


I would appreciate if someone can help me, because i am stuck for several days now. I lake of knowledge on this matter, and i did not find any real help on the internet.

Thanks in advance.

Hans

 

[Remou]

Let us try this one first:

Dim dbs As DAO.Database
    'See notes below
    Dim rstAdd As DAO.Recordset

    'You should not need this
    'Dim qd As DAO.QueryDef
    
    Set dbs = CurrentDb
    
    'Or this
    'Set qd = dbs.QueryDefs!rqtDuplication 'Query where update is going to be

    'rqtDuplication must be updatable, that is
    'you must be able to add records to this
    'query when you open it in the database 
    'window.
    Set rstAdd = db.OpenRecordset("rqtDuplication")

    'There is a problem here, I am going to
    'guess that you want to add a record to rstAdd
    'and that is not needed.
    'Set rst = Me.RecordsetClone

       
        rstAdd.AddNew
        rstAdd!N°Document = Document
        rstAdd!N°IdProduit = Me.N°IdProduit
        rstAdd!Désignation = Nz(Me.Désignation)
        rstAdd![Qte Commandee] = Nz(Me.Qte_Commandee)
        rstAdd!Mouvements = Nz(Me.Mouvements)
        rstAdd!Emplaçement = Nz(Me.Emplaçement)
        rstAdd![Date modification] = Nz(Me.DateModification)
        rstAdd!IDMagasin = Me.N°IdMagasin
        rstAdd.Update
        
       
   rstAdd.Close
   Dbs.Close

http://lessthandot.com

 

[Salsaboy60]

Ok, thank you.

I manipulated the code again and I found with your help the way to do it. Now i can even duplicate Form and Sub-Form to the BackOrder and DetailStock tabels.

I used this code :

Dim strN°Commande As String
    Dim strN°Document As String
      
   strN°Commande = DMax("[BackOrderID]", "BackOrder")
   strN°Commande = strN°Commande + 1
     
   strN°Document = DMax("[BackOrderID]", "BackOrder")
   strN°Document = strN°Document + 1
   
    
    rstBackOrder.AddNew
    rstBackOrder!BackOrderID = strN°Commande
    rstBackOrder![Numéro Commande] = Me.Parent.Numéro_Commande
    rstBackOrder!N°Prestataire = Me.Parent.N°Prestataire
    rstBackOrder![Date emission] = Me.Parent.Date_emission
    rstBackOrder!Emetteur = Me.Parent.Emetteur
    rstBackOrder!Commanditaire = Me.Parent.Commanditaire
    rstBackOrder.Update
    
        rstBackOrderDetail.AddNew
        rstBackOrderDetail!N°Document = strN°Document
        rstBackOrderDetail!N°IdProduit = Me.N°IdProduit
        rstBackOrderDetail!Emplaçement = Nz(Me.Emplaçement)
        rstBackOrderDetail![prix d'achat UHT] = 0
        rstBackOrderDetail![prix de vente UHT] = 0
        rstBackOrderDetail![Qte Commandee] = Nz(Me.Qte_Commandee)
        rstBackOrderDetail!QteConsRetour = 0
        rstBackOrderDetail!Mouvements = Nz(Me.Mouvements)
        rstBackOrderDetail![Qte door klant besteld] = "0"
        rstBackOrderDetail![QteConsignation] = 0
        rstBackOrderDetail!Sortie = 0
        rstBackOrderDetail!Perte = 0
        rstBackOrderDetail!Discount = 0
        rstBackOrderDetail!Tva = 0.21
        rstBackOrderDetail![Date modification] = Nz(Me.DateModification)
        rstBackOrderDetail!N°IdMagasin = Me.N°IdMagasin
        rstBackOrderDetail!Arrivée = 0
        rstBackOrderDetail!PersonneVenue = ""
        rstBackOrderDetail!Remarque = ""
        rstBackOrderDetail!BackOrder = -1
        rstBackOrderDetail.Update
        
   rstBackOrder.Close
   rstBackOrderDetail.Close
   CurrentDb.Close

I think to understand that I had to name all the fields in the recordset, even the one's I don't use. I fill them with blanks or zero. Anyway, this is working fine now.

Thx to you.

Hans

 

[Remou]

I think to understand that I had to name all the fields in the recordset, even the one's I don't use. I fill them with blanks or zero. Anyway, this is working fine now.


This depends on how you have set up your table.

http://lessthandot.com