Results 1 to 13 of 13
  1. #1
    tfurnivall is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    7

    Loading listbox entries that match a field on a form

    I have a form where I want to show People engaged with an Issue. I have tables:


    E_Person
    E_Issue
    R_PersonIssues (which relates people to issues in an n:n manner)

    On the form that displays an issue I have a listbox, where I would like to show all people currently engaged with an issue. The SQL that Access gives me as a starting point is:

    Code:
    SELECT DISTINCTROW E_Persons.ID, E_Persons.LastName, E_Persons.FirstName
    FROM E_Persons INNER JOIN (E_Issues INNER JOIN R_PersonIssues ON E_Issues.ID = R_PersonIssues.IssueID)
             ON E_Persons.ID = R_PersonIssues.PersonID;
    However, this gives me all of the entries in R_PersonIssues. What I think I need to do is to use the value of the current Issue being displayed. Could someone give me an idea of how to specify "CurrentEntry" as a source of values for use in the SQL statement?

    Many thanks in advance,

    Tony

    PS I have a sample database available if that would help...

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I say load up the sample. It will probably be easier to show you than explain how.

  3. #3
    tfurnivall is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    7
    Here we come...

    ..How does anyone get a reasonably functional database into a 500KByte limit?

    Tony
    Attached Files Attached Files

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    How do you tell if an issue is "currently engaged" as compared to others? Do you have a Complete or Status field? Looks like you will need to add a Where clause to your Select statement to limit the data.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    @Bulzie
    Did you have time to look at the DB? If so, I wont bother to. Thanks

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Quote Originally Posted by ItsMe View Post
    @Bulzie
    Did you have time to look at the DB? If so, I wont bother to. Thanks
    No I did not.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I created a query object, named query one. Here is the SQL
    Code:
    SELECT R_PersonIssues.ID, R_PersonIssues.PersonID, E_Persons.LastName, E_Persons.FirstName, E_Issues.Tag
    FROM E_Persons RIGHT JOIN (E_Issues RIGHT JOIN R_PersonIssues ON E_Issues.ID = R_PersonIssues.IssueID) ON E_Persons.ID = R_PersonIssues.PersonID
    ORDER BY E_Persons.LastName;
    I also added some entries to your junction table. I think this is what you are after. However, you may want to do some testing and consider your business rules while you try to break it.

    I am uploading the DB here. I did not have an opportunity to scrutinize everything. I am working on some other stuff, here. So, be sure to ask questions.
    Attached Files Attached Files

  8. #8
    tfurnivall is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    7
    Hi all,

    The presence of a Person-Issue tuplet implies that the Person is engaged with the Issue. So, what I want the query to do is to look at the current issue (how do I get that value, or do I have to put a hidden field onto the form to pick up the Issue ID), and then to scan the R_Person_Issues table for that Issue value, picking up the corresponding Person ID, and then retrieve the values for the Person (at present it's just the name, but once I can get the entry, I'll expand the size, and pick out the fields I want to display).

    ItsMe:

    I don't want to display (ergo, don't want/need to SELECT) items from the relationship table. For the purpose of this proof-of-concept all I want are E_Person.LastName, E_Person.FirstName. IOW values from the E_Person table, selected by the query. HTH


    @Bulzie

    I suspect that the WHERE clause will be WHERE R_Person_Issue = <current entry from E_Issue>._IssueID
    An underlying question is: Do I have to bring the IssueID from the current entry onto the form to make that value available? Or is it in an underlying query somewhere? No problem with putting it on the form, and hiding it, just wondered.


    Thanks to all - I don't have any of my Access stuff with me while I'm on vacation, and 99% of my Office coding is Excel.

    Tony

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If you only need to display people with issues, INNER JOIN the E_Persons.ID = R_PersonIssues.Person.ID

    If you only want to retrieve a specific E_Person, apply WHERE criteria to E_Persons.ID

  10. #10
    tfurnivall is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    7
    Thanks for the comments - and for your patience with a bumbling Excel programmer who left his Access books at home!

    I've put the current state of the db up again. I'm getting very confused because I haven't used SQL with any regularity in over 20 years, and then it was ANSI, not ACCESS. But that's by the way.

    The goal is to show, in lstEngagedPersons all the People who have an entry in R_PersonIssues that matches this issue. I can, occasionally, possibly even randomly, get such a list for the first entry. It refuses, however, stubbornly, to change! And very careful data design allows me to assert that it should! I also plan on exploiting the symmetry between the two key fields to get, at a later date, all Issues that a Person has (thanks for that delightful serendipity, ItsMe).

    A niggling memory from my very first Access course, back in the eighties, says Sub-Form, but his seems like over-kill. I have a SQL statement that should select entries from R_PersonIssues, which match the issue:
    Code:
    SELECT E_Persons.ID, E_Persons.LastName, E_Persons.FirstName
    FROM E_Persons INNER JOIN R_PersonIssues ON E_Persons.ID = R_PersonIssues.PersonID
    WHERE (((R_PersonIssues.IssueID)=[E_Issues].[ID]));
    Why doesn't it change when I step through the issues?
    How do I obliterate Ddescription from the database? I'm still getting an annoying prompt..
    Why did I leave my Access books at home? (OK, that's a question for me)

    Tony
    Attached Files Attached Files

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe ...

    • Add E_Issues.ID to recordset of form F_Issue
    • Adjust the SQL for the list box to use the new field as criteria
    • Add a line of code in the Form's OnCurrent Event to requery the listbox


    Code:
    SELECT DISTINCTROW E_Persons.ID, E_Persons.LastName, E_Persons.FirstName FROM E_Persons INNER JOIN R_PersonIssues ON E_Persons.ID = R_PersonIssues.PersonID WHERE (((R_PersonIssues.IssueID)=[Forms]![F_Issue]![ID]));
    Code:
    Me.lstEngagedPersons.Requery

  12. #12
    tfurnivall is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    7
    That's it, ItsMe!

    The .Requery was the action I was looking for, and didn't remember enough to ask. The listbox now adjusts itself and shows the Persons engaged with an issue. It occurred to me - at 2:30am in the midst of a loud thunderstorm with flashes of lightning - that what I'm trying to do here is exactly what the form wizard does when building the recordset for a form:

    A Form with an underlying Table has a finite set of TableFields, each of which is either "in" the Form or not. Being "in" the form can lead to additional data that characterize the relationship (think, Top, Left, Height, Width, etc) which do not apply if a Field is not in the Form.

    Access provides that lovely little four-arrow mechanism to shift Fields from In to Out of the form. THAT's the mechanism I want to replicate <whinge> it is so easy in Excel, where I do it almost in my sleep, but then Excel<>Access!</whinge>

    Now to see if I can get the ComboBox list to reflect the people who are not engaged with the issue. I think somewhere in the history of this thread is a sample SQL statement for that. I'll move on, and post a summary (or another request) as I progress.

    Thanks again, for your help - all of you - these Fora are the best support there is!

    Tony

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Perhaps the IN operator, i.e. NOT IN
    http://www.w3schools.com/sql/sql_in.asp

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

Similar Threads

  1. Replies: 4
    Last Post: 08-20-2015, 05:38 AM
  2. Replies: 2
    Last Post: 06-01-2015, 01:21 PM
  3. Replies: 3
    Last Post: 05-30-2014, 11:18 AM
  4. Replies: 3
    Last Post: 02-06-2013, 10:00 PM
  5. Form input field color changes to match text
    By fordtough in forum Forms
    Replies: 0
    Last Post: 05-06-2011, 09:49 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