Results 1 to 3 of 3
  1. #1
    tlkng1 is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2014
    Posts
    12

    Table Relationships and Auto-filling UserID field in two tables using a single form

    Hi all...please bear with me as I try to explain this one

    I have an IT user database which handles user information, trouble ticket tracking and user training tracking where the primary key, UserID, is the same for all three tables.

    The userID is not, and cannot, be auto-generated as it is an assigned field based on certain criteria that is then converted into a seven character ID using a hash algorithm.

    Three tables specific to the users:

    tblUsers: contains specific userID; name, department, access level, supervisor, assigned systems
    tblTickets: trouble tickets
    tblNewUserTraining: tracks required system training

    Individually the tables work fine, no issues, however, my question refers to form creation.

    Form 1 intent is to allow for entry of new users. In this case, I would need all the fields from the Users table as well as the fields from the NewUserTraining table. I've attached a generic single line of each table for better visual (primary keys in yelow).

    tblUsers to tblNewUserTraining is a one-to-one relationship while tblUsers to tblTickets is a one-to-many.

    The form in question deals with Creating a New User and setting up the training tracking. I've attached a generic version of the form.

    I need to have the assigned UserID and training info from this form to populate the same fields in the tblNewUserTraining.

    I did some research and found a reference to a foreign key, but not sure if the UserID in the tblNewUserTraining can be set as foreign?

    A ticket form is independent and will be filled in accordingly, but, I would also like to have the Ticket Command Button on the User form auto-fill the UserID on a Ticket Form (yet to be created). I've tried the DoCmd.OpenForm in add mode without luck...still figuring out the Commmand Button operations

    Ideas?

    Thanks

    TK



    Click image for larger version. 

Name:	tables.PNG 
Views:	6 
Size:	18.1 KB 
ID:	35298Click image for larger version. 

Name:	User_Profile_Form.PNG 
Views:	20 
Size:	28.0 KB 
ID:	35299
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,652
    A couple of methods come to mind. If you're going to add a single record, just populate the textbox after opening in add mode:

    DoCmd.OpenForm...
    Forms!SecondFormName.TextboxName = Me.TextboxName

    If you might add several records, I'd pass the value in OpenArgs and use code in the second form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,870
    You say these tables all have PK UserID. How are the tables related? Can you show readers the relationship window?

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto-filling a field based on other fields?
    By TerraEarth in forum Access
    Replies: 9
    Last Post: 06-19-2018, 09:30 AM
  2. Replies: 2
    Last Post: 11-16-2015, 08:10 AM
  3. Replies: 3
    Last Post: 07-24-2014, 01:22 PM
  4. Auto filling a field in a table. Help please
    By tdanko128 in forum Access
    Replies: 2
    Last Post: 01-18-2011, 12:22 PM
  5. Auto filling a form from table
    By JoScSM in forum Forms
    Replies: 1
    Last Post: 11-02-2010, 08:09 AM

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