Results 1 to 8 of 8
  1. #1
    enginerdUNH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Location
    United States
    Posts
    35

    Display records based on another form's AutoID value

    Hi,

    First of all, I want to apologize if this answer has already been answered before. I have not been able to find an answer on this so I'm asking it here. I have a database with a main form with a few pop-up forms that come off of it. On the main form, it displays a user's personal information (name, address, phone, emergency contact, etc.). The pop-up forms are for use of showing information that is stored in other tables. On each table that is tied to the pop-up forms, there is a number field which I want to tie to the AutoID field on the main form. In other words, a user pulls up their information on the main page and clicks a button which brings them to a pop-up form which has other information. When the pop-up form appears, the user's AutoID from the main form is generated in the field that ties the two forms together and only allows the user to see records that are tied to the current user. Any records entered on the pop-up form will be stored in a table with it's own special AutoID for each record and the user's ID. I am looking for a way to tie this all together and make this work. Any input helps and again I'm sorry if this question has already been answered in the past.



    Morgan

  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,518
    Is this what you're trying to do?

    BaldyWeb wherecondition
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    enginerdUNH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Location
    United States
    Posts
    35
    Quote Originally Posted by pbaldy View Post
    Is this what you're trying to do?

    BaldyWeb wherecondition
    Thank you Paul for your response. Unfortunately that is not what I am looking for. The pop-up forms have different information than what is displayed on the main form and are based off of different tables. The key that I am looking for is how to tie them together using the user's AutoID from the main form and only show records that correspond to that user.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not exactly sure of your meaning in your first post, but if you intend to tie the auto numbers for the popup form records to auto numbers in any other table, you cannot rely on this.
    The key that I am looking for is how to tie them together using the user's AutoID
    Your subsequent post seems to support my guess. You need to use some other field that is common to all tables that are related to the main one. If that needs to be an autonumber id from the main table because you have no unique distinguishing value (such as employee number), then you must write that auto number value as a foreign key value to any other related table. Only then could you "link" tbl2.ForeignIDValue to tbl1.YourAutonumberField.

    You might benefit from more insight about autonumbers. Maybe you'll have to rethink your tables design. FWIW, that page ought to have "Meant to be used as meaningful data" as the #1 in the NOT list. Seems like an oversight to me.

    Hopefully you designed the popup forms to be modal so that the main form and popups that weren't closed don't get out of sync.
    Last edited by Micron; 02-19-2018 at 08:19 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    enginerdUNH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Location
    United States
    Posts
    35
    Quote Originally Posted by Micron View Post
    Not exactly sure of your meaning in your first post, but if you intend to tie the auto numbers for the popup form records to auto numbers in any other table, you cannot rely on this.
    Your subsequent post seems to support my guess. You need to use some other field that is common to all tables that are related to the main one. If that needs to be an autonumber id from the main table because you have no unique distinguishing value (such as employee number), then you must write that auto number value as a foreign key value to any other related table. Only then could you "link" tbl2.ForeignIDValue to tbl1.YourAutonumberField.

    You might benefit from more insight about autonumbers. Maybe you'll have to rethink your tables design. FWIW, that page ought to have "Meant to be used as meaningful data" as the #1 in the NOT list. Seems like an oversight to me.

    Hopefully you designed the popup forms to be modal so that the main form and popups that weren't closed don't get out of sync.
    Hi Micron,

    Thanks for your reply. let me start by clarifying that I am not trying to tie who auto number fields together. I have an auto number field on the main page which corresponds to a plain number field on the pop-up tables. There is a separate auto number field on the pop-up tables to keep track of each new record that is entered. The regular number field is what I am trying to tie back to the auto number of the current user back from the main form. I believe this is the type of thing that I'm looking to do https://social.msdn.microsoft.com/Fo...orum=accessdev but i'm not entirely sure if it's the right way to do it or how it works.

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can refer to another forms controls using the syntax found here; http://access.mvps.org/access/forms/frm0031.htm
    So if your UserId is on a form called frmMainMenu in a control called txtUserID you would get its value by using something like;
    Code:
    Dim MyCurrentUser as Long
    MyCurrentUserID = Forms!frmMainMenu!txtUserID
    Alternatively if you already have that value on the calling form you can use the OpenArgs() property to pass a string to the form you are opening.
    On the new form you simply use Me.OpeArgs to refer to it, as per the link you provided.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think what you're being told isn't being grasped. The answer from pbaldy ought to work. So would 2 of the proposed solutions at the link you provided - referring back to the still open parent form, or using the OpenArgs method to pass a key value from one form to another. That is just another way of doing the first suggestion. Then there is a 3rd method, which is using a stored query as a form filter, but that's one I don't use seeing as how it requires another db object (query) which isn't really necessary IMHO.

    Maybe what's missing is your understanding of how to make use of the value being passed to the popup form (or the value being referred to in the open parent form). The underlying record source for the popup form should be a query. The easiest way of implementing this is probably to make an unbound control on the popup form equal to the number that you say is part of the popup form's record source, either by
    - passing the main form value to it (using the OpenArgs parameter of the code that opens the popup)
    - or by referring back to the main form that contains said number
    Then when your popup form opens, the unbound hidden textbox can supply the number value to the query behind the popup, which will run automatically run when the form opens. If there is no record(s) for the popup because you're only using it to add new records to other tables, then said number in hidden control can still be used as the source for the linking number that needs to go into the other tables. If you want to be able to edit existing OR create new records with this popup, then the query way would handle both as long as that query is updatable.

  8. #8
    enginerdUNH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Location
    United States
    Posts
    35
    Quote Originally Posted by Micron View Post
    I think what you're being told isn't being grasped. The answer from pbaldy ought to work. So would 2 of the proposed solutions at the link you provided - referring back to the still open parent form, or using the OpenArgs method to pass a key value from one form to another. That is just another way of doing the first suggestion. Then there is a 3rd method, which is using a stored query as a form filter, but that's one I don't use seeing as how it requires another db object (query) which isn't really necessary IMHO.

    Maybe what's missing is your understanding of how to make use of the value being passed to the popup form (or the value being referred to in the open parent form). The underlying record source for the popup form should be a query. The easiest way of implementing this is probably to make an unbound control on the popup form equal to the number that you say is part of the popup form's record source, either by
    - passing the main form value to it (using the OpenArgs parameter of the code that opens the popup)
    - or by referring back to the main form that contains said number
    Then when your popup form opens, the unbound hidden textbox can supply the number value to the query behind the popup, which will run automatically run when the form opens. If there is no record(s) for the popup because you're only using it to add new records to other tables, then said number in hidden control can still be used as the source for the linking number that needs to go into the other tables. If you want to be able to edit existing OR create new records with this popup, then the query way would handle both as long as that query is updatable.
    I never said that any of the options in the link I provided wouldn't work. If you go back to my post where I provided that link, I noted that I don't know if that was entirely the right way to go about it or how those solutions work. I believe the post that Minty made with example code is what I was trying to figure out but I will get back to you once I've had some time to play with it

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

Similar Threads

  1. Replies: 6
    Last Post: 11-09-2017, 04:11 PM
  2. Replies: 4
    Last Post: 11-02-2015, 02:23 PM
  3. Replies: 8
    Last Post: 05-10-2015, 06:24 PM
  4. Update table with autoID
    By kris9 in forum Access
    Replies: 9
    Last Post: 08-09-2013, 12:08 PM
  5. AutoID
    By hithere in forum Queries
    Replies: 8
    Last Post: 03-05-2012, 03:30 PM

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
  •  
Other Forums: Microsoft Office Forums