Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Using a Button - Copy Table1 form field to Table2 form field

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ok,

    I've been playing with this for a while and I think I've found the problem.
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by swisherink View Post

    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.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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 and\or 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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •