Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    5

    Unanswered: Copying information from a control of 1 form to another control of a different form

    Hi all, i have been searching around for quite some time and have found a lot of good information but i seem to be missing a certain part of this process.

    I am using Access 2000 and i have set up a simple test DB so i can make sure that none of my field requirements are causing me an issue. Basically here is what i have:
    2 Tables: Table1 and Table 2
    2 Forms: Table1 and Table2
    2 Controls: Test (residing on Forms.Table1) and Test2 (residing on Forms.Table2)

    What i want to have happen is when someone types something in Forms.Table1.Test, it will copy that information into Forms.Table2.Test2

    I have an "On Open" event set up on Forms.Table2 that says
    "=[Forms]("Table2").[test2]=[Forms]("Table1").[Test]"

    Both forms would be open during this copy, on Forms.Table1 I have a button with an on click event that "Saves" Forms.Table1 and then opens Forms.Table2. The form opens ok without an error, but the data does not insert into Forms.Table2.Test2. Forms.Table2.Test2 stays blank.

    The event doesn't work if i open Forms.Table2 manually either. I have been messing around with this code for hours trying to figure out what the heck i am doing wrong and have come up empty handed. Please help! Thanks

    -Rob

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    What is the Relationships between these two tables ??

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by gigatigga
    Hi all, i have been searching around for quite some time and have found a lot of good information but i seem to be missing a certain part of this process.

    I am using Access 2000 and i have set up a simple test DB so i can make sure that none of my field requirements are causing me an issue. Basically here is what i have:
    2 Tables: Table1 and Table 2
    2 Forms: Table1 and Table2
    2 Controls: Test (residing on Forms.Table1) and Test2 (residing on Forms.Table2)

    What i want to have happen is when someone types something in Forms.Table1.Test, it will copy that information into Forms.Table2.Test2

    I have an "On Open" event set up on Forms.Table2 that says
    "=[Forms]("Table2").[test2]=[Forms]("Table1").[Test]"

    Both forms would be open during this copy, on Forms.Table1 I have a button with an on click event that "Saves" Forms.Table1 and then opens Forms.Table2. The form opens ok without an error, but the data does not insert into Forms.Table2.Test2. Forms.Table2.Test2 stays blank.

    The event doesn't work if i open Forms.Table2 manually either. I have been messing around with this code for hours trying to figure out what the heck i am doing wrong and have come up empty handed. Please help! Thanks

    -Rob
    Hi

    With BOTH forms open this should do it

    me.Test2 = Form_Table1.Test

    Provided form Table1 is open it should work in form Table2 Form_Open or Form_Load events or in any code in form Table2"

    This also would work in Access97 or later (from memory!).

    me.Test2 = Forms("Table1").Test


    MTB

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're trying to copy a field from a form or subform to a form or subform, you can use the reference to the form name itself this way also:

    Forms!MyMainFormName!MyFieldName = Forms!MyMainFormName!MySubformName!MyFieldName

    You can also use (to refer to a subform control):

    Forms("frmMain").fsubContorl

    or (for example) to call a public event of a control in a subform

    Call Forms("frmMain").fsubContorl.Form.FuncCall

    I personally don't like to use the . (ie. me.Text2) versus the ! (ie. me!Text2) when refering to the VALUE in a control since using the . typically refers to the CONTROL itself versus the value in the control (even though later versions of MSAccess will translate it correctly 'most of the time'), I prefer not to take the chance and use the bang originally defined to refer to the value of a field from working with older versions of MSAccess.

    I can't say if there's any additional processing time needed by MSAccess to correctly translate whether a . refers to the control or the value in that control (although I'm guessing if there was, it doesn't impact speed in any way) but I'd still rather not take the chance that the translation is done incorrectly.
    Last edited by pkstormy; 10-16-09 at 02:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2009
    Posts
    5
    Thanks so much for all the replies. I have also tried with the bang as well without any luck. I do get errors when i screw something up with the syntax so i have a feeling the code i am using is at least checking out ok, but i still end up with controls on the 2nd form that have nothing filled in.

    Do the tables need to have relationships for this to work?

    Thanks again for the assistance.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What errors are you getting?

    Are the fields updateable? (Did you try updating them manually?)

    I use this expression:
    Forms!MyMainFormName!MyFieldName = Forms!MyMainFormName!MySubformName!MyFieldName

    all the time without ANY problems.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try adding in a msgbox command to a button's onclick event to see what value you are really grabbing.

    ie.

    msgbox "My subform value of field x = " & Forms!MyMainFormName!MySubformName!MyFieldName
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Oct 2009
    Posts
    5
    Thanks for the replies. I don't get a bunch of time to work on this, which is why my response was delayed. When I try putting in the code suggested above i end up with "Forms!242total!MDN = Forms!InboxDataEntry!MDN" and i get an error stating "Database can't find the macro 'Forms!242total!MDN = Forms!InboxDataEntry!MDN" This is if i type it directly in the "On Open" field in the form properties. If I type this same thing into the "Expression Builder" it auto appends a = in front of the code and i get an error that says "THe expression you entered contains invaild syntax"

    Anymore insight would be great.

    Thanks a bunch.
    Rob.

  9. #9
    Join Date
    Oct 2009
    Posts
    5
    I should also note that i'm not using a subform, this is an entirely different form.

    Also: when i try the me.test2 way it says "The Expression On Load you entered as the even property setting produced the following error: The object doesn't contain the Automation object 'me'.

    Thanks
    Last edited by gigatigga; 10-21-09 at 19:35.

  10. #10
    Join Date
    Oct 2009
    Posts
    5
    Got it! I was typing my code in the wrong place.

    Thanks for all the help!

Posting Permissions

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