View original (French)

Copy fields from a form/subform to a new one

Dicab Posted messages 186 Status Member -  
 Tessel75 -
Hello everyone,

Using a command button, I am trying to copy the values of the fields contained in a form + subform into a new form + subform.

For the fields in the form, no problem, I manage to do it with the code

Forms("FRM_FACTURES").Controls("DATE_TRAVAUX") = Me.DATE_TRAVAUX


However, for the subform, which is tabular, it's impossible. I thought about making an append query in SQL mode, but I’m getting lost in the parent/child fields that link the records.

Since Access problems can sometimes be a bit complex to explain, my database is available here https://www.cjoint.com/?0LlncNb2HVk

With the button "Create Invoice" on the FRM_DEVIS form, I’m trying to copy all the values to a new FRM_FACTURES form.

Thank you for your help! :)

2 answers

  1.
    Dicab Posted messages 186 Status Member 2
     
    I tried this: 

    DoCmd.SetWarnings False
    mySql = "INSERT INTO [TBL_FACTURES] (IDENTIFIANT_FACTURE, ARTICLE_FACTURE, QTE_FACTURE, PU_HT, REMISE) SELECT " & Forms![FRM_FACTURES].NUM_FACTURE & ",ARTICLE_DEVIS, QTE_DEVIS, PU_HT, REMISE FROM [TBL_CONTENU_DEVIS] WHERE IDENTIFIANT_DEVIS=" & Forms![FRM_DEVIS]!NUM_DEVIS
    DoCmd.RunSQL mySql


    But the field "IDENTIFIANT_FACTURE" is reported as not found....
    I'm stuck :-( :-(
    0
  2. Tessel75
     
    Hello,
    Looking at your database, it seems that it would be much better to have just one table, "CONTENT_OF ...", instead of two for quotes and invoices. You would only need to add a checkbox to cancel the items on quote lines that have been rejected by clients (with the default value of such a checkbox set to "Yes"); then the invoice lines would only include those lines that have not been rejected. With this method, you would avoid a lot of hassle and your database would be lighter.
    Two other comments: it would also be better for your quote and invoice lines to refer directly to the product catalog references through their references and identifiers rather than their full labels; and I would also advise you to add a "Rates" table, given that they are not fixed.
    Best regards.
    0