Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Setting Foreign Key in MS-Access

    Hi,
    I am using MS-Access 2007. I am creating database for my application.
    And i am having 2 tables. CreateUser table and Login table with these fields.
    CreateUser Table
    UserID
    UserName
    Password
    Login Table
    UserID
    Active
    LastLoginDate
    So when i add some data in Create user table, automatically i want to update login table with active field set to be "Yes".
    How to do this?

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    When you create the user in your CreateUser Table use an append query to add the UserID to your Login Table. In your Login Table you can set the default value of Active to True if you are using the Yes/No data type or Yes if you are using a text data type.

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    Quote Originally Posted by Poppa Smurf View Post
    When you create the user in your CreateUser Table use an append query to add the UserID to your Login Table. In your Login Table you can set the default value of Active to True if you are using the Yes/No data type or Yes if you are using a text data type.
    Can u make me more clear? Are you saying to use insert query in both the tables?

  4. #4
    Join Date
    Mar 2005
    Posts
    20
    Here's how I would do it. Make your active field of data type yes/no. Set the default value to yes. Set up the relationship between the two tables. Then create one form for your createuser table and another form for login table. I would add the login table form as a subform to createuser form. When you add a new record to createuser a record will also be created in login with the active field set to yes.

  5. #5
    Join Date
    Nov 2008
    Posts
    117
    Quote Originally Posted by hayesbcajh View Post
    Here's how I would do it. Make your active field of data type yes/no. Set the default value to yes. Set up the relationship between the two tables. Then create one form for your createuser table and another form for login table. I would add the login table form as a subform to createuser form. When you add a new record to createuser a record will also be created in login with the active field set to yes.
    I did as per your post, but i am not getting it. I dont know the mistake. Please check the attached file.
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2005
    Posts
    20
    In the table tbl_login, make the UserId field number instead of autonumber. Then add the following to the After Insert event on the form tbl_createuser...
    Me.tbl_login!Active = "Yes"

Posting Permissions

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