Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bruce is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    72

    Pass data from one form to another


    I have two tables (Table1 and Table2) and I use forms to enter data. There is a numeric field in the first and second form named SID. I have set the data control source on the second form (Form2) SID field to =Forms!Form1!SID. After completing data entry on Form1, I use a command button to open Form2 and the SID field data from Form1 is automatically entered in the SID field in Form2. However, after filling out the remaining data on Form2 and exiting the forms, I check the data in Table2 and the SID value is not being saved. The other data that was entered is saved but the SID number pulled from Form1 shows up as "0" in the table. I also tried modifying the code on the OpenForm event with OpenArgs but that didn't work either.

    I suspect this is a common use for passing a primary key value from Table1 to Table2 (foreign key). Eventually I'll hide the fields but I need to know why the numeric value transferred is not being saved in the table.

    Im using Access 2003. Thanks!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You have a calculation as the ControlSource of your control in Form2. You should bind the control to the field you want to update (the FK field). The Open event is too early in the process to reference a control on a form; you need to use the OnLoad event. Then you could use the OpenArgs or the Forms collection to set the value of the Form2Control and it will save. Me.SID = Forms!Form1!SID

  3. #3
    Bruce is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    72
    Thanks! I will try this. Can you help me with the VBA code that would acomplish this? I'm a newbie.

  4. #4
    Bruce is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    72
    I added the following code to the OnLoad event of Form2:

    Me.SID = Forms![Form1]![SID]

    The number was imported correctly but STILL not saved to Table2. I've checked for any restrictions but can find nothing that would stop this number from being saved to the table. bummer!

    Appreciate any other troubleshooting tips or suggestions. Thanks!

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do you have a control on your form named SID? If so, rename it to txtSID. Is the same control bound to the SID field of the RecordSource of the form? Then change your code to: Me.txtSID = Forms![Form1]![SID]

  6. #6
    Bruce is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    72

    Smile

    My fault. I forgot to bind Form2.SID to the table. The combination of binding and OnLoad code did the trick. Thanks!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're welcome. Glad I could help.

  8. #8
    rayan2002 is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    5
    Quote Originally Posted by RuralGuy View Post
    You're welcome. Glad I could help.
    I have same prob. and am Newbe please can you send me code and more explanation
    many thanx

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by rayan2002 View Post
    I have same prob. and am Newbe please can you send me code and more explanation
    many thanx
    Maybe this link will help: http://www.baldyweb.com/OpenArgs.htm

  10. #10
    rayan2002 is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    5
    Quote Originally Posted by RuralGuy View Post
    Maybe this link will help: http://www.baldyweb.com/OpenArgs.htm
    Thank you very much sir for quick response
    but my prob not solve bu this code, I have application (two forms and 2 tables ) the NI is primary key in first form and foriegn key in second form after filling first form1 there is button called Next to fill another part or form 2 user need to write NI manualy . I coul pass the value from form 1 to form 2 but un fortunatly I coudnt save the value in tables . I want NI textbox field passed automatically to NI_card textbox and saved in table
    thanx again

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can do that with the code from the link I provided.

  12. #12
    rayan2002 is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    5
    Quote Originally Posted by RuralGuy View Post
    You can do that with the code from the link I provided.
    Many thanks
    do I put th ecode under form load or after update

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    As the link shows it goes in the OnLoad event.

  14. #14
    rayan2002 is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    5
    It passed the value but doesnt save it in tables
    thanks alot

  15. #15
    rayan2002 is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    5
    Quote Originally Posted by Bruce View Post
    My fault. I forgot to bind Form2.SID to the table. The combination of binding and OnLoad code did the trick. Thanks!
    how do you solve the prob can you send me code
    thanx

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass list as parameter to in operator
    By bliever in forum Queries
    Replies: 5
    Last Post: 11-11-2009, 03:15 AM
  2. Pass a Parameter From a form to a Query
    By DDillesha in forum Forms
    Replies: 1
    Last Post: 10-28-2009, 12:49 PM
  3. I want to automatically pass a value to a query
    By Slategrey252 in forum Queries
    Replies: 1
    Last Post: 10-01-2009, 05:38 AM
  4. 90 days in the pass - Date help wanted
    By techexpressinc in forum Queries
    Replies: 1
    Last Post: 01-26-2009, 07:13 AM
  5. Replies: 0
    Last Post: 03-19-2006, 11:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums