• Using a Button – Copy Table1 form field to Table2 form field

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Using a Button – Copy Table1 form field to Table2 form field

    Author
    Topic
    #482626

    I have a database that contains tickets, the main table is the general ticket information (Table1); then I have additional Tables that contain specific types of tickets (Table2) and (Table3) where additional information is stored only for specific ticket types. Table2 and Table3 are not related to each other and tickets in Table1 may not have a record in either Table2 or Table3.

    The relationship is the ticket#.

    The Table1.ticket# field is the primary key in Table2.ticket# and Table3.ticket# (using a drop down list of available ticket#’s).

    When I select the Table2 button from the Table1 form, I would like to open the Table2 form to a New Record and copy the field value in Table1.ticket# field to Table2.ticket# field, and the Table1.section field to Table2.section field.

    I have been able to copy other information on the same form using the below but this doesn’t seem to work with going from Table1.Field1 to Table2.Field1.

    ‘Copy Field1 to Field2
    Me.Field2 = Me.Field1

    I figures I was missing the piece for the Form since “Me” references the current form.

    Also, if both forms are open and I would like to copy multiple fields from one form to another, do I have to modify anything?

    i.e. Change all of these with a single button click?
    Table1.fieldA = Table2.fieldA
    Table1.fieldB = Table2.fieldB
    Table1.fieldC= Table2.fieldC

    Viewing 4 reply threads
    Author
    Replies
    • #1328270

      As long as the fields in the Forms are linked to the corresponding tables what you need is:

      Code:
      FormName2.FieldA = FormName1.FieldA
      FormName2.FieldB = FormName1.FieldB
      FormName2.FieldC = FormName1.FieldC                         
      

      The above assumes that FormName1 is linked to Table1 and FormName2 is linked to Table2. The code would be the the same for the Table3 form except you would use FormName3…. on the left of the equal sign.
      :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1328275

        RG, Thank you for the feedback. What you provided was what I had expected, but I continue to have receive an error “Object required”.

        From Form1, I am clicking on a button with the following VBA expression associated:

        Private Sub Button_OpenForm2_Click()
        On Error GoTo Button_OpenForm2_Click_Err
        DoCmd.OpenForm “Form2”, acNormal, “”, “[FieldA]=” & Form1.[FieldA], , acNormal
        Form2.FieldA = Form1.FieldB

        Button_OpenForm2_Click_Exit:
        Exit Sub
        Button_OpenForm2_Click_Err:
        MsgBox Error$
        Resume Button_OpenPkg_Click_Exit
        End Sub

        • #1328299

          Private Sub Button_OpenForm2_Click()
          On Error GoTo Button_OpenForm2_Click_Err
          DoCmd.OpenForm “Form2”, acNormal, “”, “[FieldA]=” & Form1.[FieldA], , acNormal
          Form2.FieldA = Form1.FieldB

          Button_OpenForm2_Click_Exit:
          Exit Sub
          Button_OpenForm2_Click_Err:
          MsgBox Error$
          Resume Button_OpenPkg_Click_Exit
          End Sub

          The syntax you need is:
          Forms(“form2”).FieldB=me.FieldB

          This code executes from form1, so Me is just a reference to the form the code executes from.

          But does the record in table2 exist? Or do you need to create it?

          I doube that I would be using three tables in the scenario you describe. You could have all the fields in the one table, and just leave them as null when they are not needed.

    • #1328281

      Another way to explain what I am doing:

      Step #1: Open Form1, copy FieldA, FieldB, and FieldC, click on Button to Open Form2, paste FieldA, FieldB, and FieldC

      Step #2: Open Form2, click button, open Form3 where FieldA in Form3 = FieldA in Form2 IF NOT GoTo New Record and past FieldA value in FieldA

    • #1328284

      Change: DoCmd.OpenForm “Form2”, acNormal, “”, “[FieldA]=” & Form1.[FieldA], , acNormal
      To:DoCmd.OpenForm “Form2”, acNormal, , “FieldA = ” & Me.FieldA, acFormEdit , acNormal
      :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1328295

      Ok,

      I’ve been playing with this for a while and I think I’ve found the problem. :confused:

      Code:
      Private Sub cmdOpenMyPatient_Click()
      
      On Error GoTo Err_cmdOpenMyPatient_Click
      
          Dim lRecCnt   As Long
          Dim lPNo      As Long
          Dim zCriteria As String
          Dim zMode     As String
          
          lPNo = Me!PatientNo
          
          lRecCnt = DCount("[PatientNo]", "MyPatients", "[PatientNo] = " & lPNo)
          If lRecCnt = 0 Then
            zCriteria = ""
            zMode = acFormAdd
          Else
            zCriteria = "PatientNo = " & lPNo
            zMode = acFormEdit
          End If
          
          DoCmd.OpenForm "frm_MyPatients", acNormal, , zCriteria, zMode, acDialog
          If zMode = acFormAdd Then Forms![frm_MyPatients]![PatientNo].Text = lPNo
          
      Exit_cmdOpenMyPatient_Click:
          Exit Sub
      
      Err_cmdOpenMyPatient_Click:
          MsgBox Err.Description
          Resume Exit_cmdOpenMyPatient_Click
          
      End Sub
      

      The above code works to a point, i.e. when the DoCmd.OpenForm executes focus passes to the opened form and does not return to the following line in the procedure until the form opened by DoCmd.OpenForm is closed!

      So as I see it you’ll have to employ some Global variables and then have an FormOpen event procedure for the called forms to retrieve the data from the Global variables. That’s the best my limited Access VBA experience can do. Perhaps the real Access gurus will pipe in with a better solution. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1328431

      John,
      I am creating tickets in Form1 associated to Table1. Some of the tickets have one of two different process associated to them that is stored separately in Table2 and Table3. Because the records in Table1 can have multiple entries in Table2 andor Table3 the information was not combined in Table1.

      The primary key in table1 [DCTicket#] is linked to table2 [Ticket#] and table3 [Ticket#]. I would like to be able to click on a button from Form_Table1 and have it create a new record in Form_Table2 automatically setting the table2 [Ticket#] field to the table1 [DCTicket#]. I also have another field [box1] on (table1_form) that I would like to copy to a field on table2_form [boxA].

      I have used the Me.[field] to do this on a single form; i.e. auto complete portions of a form when a button is clicked but I have never gone from one form to another form. I will try using the syntax Forms(“form2”).FieldB=me.FieldB you provided. Thank you.

    Viewing 4 reply threads
    Reply To: Using a Button – Copy Table1 form field to Table2 form field

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: