Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I need some guidance on some things I need to do with a new database. I've done them before, but it was a few years ago and on Access 2003. Basically I want to put a list box onto an existing form. Ideally, I'd like to display information from another table by clicking an item in the list box.

    tblPeople holds data about people; keyfield is HandleP.

    tblGroups holds data about groups that people can belong to; keyfield is HandleG.

    tblConnections holds data connecting people to their groups. It has two fields: HandleP and HandleG. Each record specifies the connection between a person and a group. People can belong to several groups, probably no more than 5 (this would take 5 records in tblConnections). Groups can have many people, no more than five right now, but it can grow as time goes on.

    frmPeople shows all the data about a single person. It works fine right now. I want it to also have a list box showing the groups that the person belongs to. I need some guidance on setting up relationships, and how to hook the list box to tblConnections and have it show the proper groups. (I'm illiterate in SQL. In the past, I've had local help.)

    Ideally, I'd like to click a group in the list box and display the info about that group somewhere on frmPeople. All such info would fit on 5-10 short lines; there is space for it on frmPeople, maybe as a subform. I've done it before, but long ago on a different version.

    Any guidance on how to approach these things will be appreciated.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Leave the Control Source of the list box blank.
    Set the Row Source Type to Table/Query.
    Set the Row Source to a SQL string like this:

    SELECT tblGroups.GroupName FROM tblGroups INNER JOIN tblConnections ON tblGroups.HandleG = tblConnections.HandleG WHERE tblConnections.HandleP = Forms!NameOfTheForm!HandleP ORDER BY tblGroups.GroupName

    where GroupName is the field that describes the group, and NameOfTheForm is the name of ... you get it.

    Create an On Current event procedure for the form:

    Code:
    Private Sub Form_Current()
      Me.NameOfTheListBox.Requery
    End Sub
    where NameOfTheListBox is ... OK, OK, I'll stop.

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Thanks! It's a start.

    In the past, I had the services of a student who was an Access Guru in the making. He has moved on now, so I'm on my own. I need to know SQL.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Bingo! It works perfectly. I had to go through some error messages that I still don't understand, but I got through them. I'm still trying to find stuff on that #$%& ribbon.

    I will now try to implement the same thing on frmGroups -- so it shows the names of people in the group that is shown on the current form. This one has a new wrinkle: the names are in tblPeople in two fields: LastName and FirstName. It will be easy, I think, to get just LastName to show in the list box. What I would REALLY like to do, though, is to show the names in the form Last, First.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can either use

    SELECT tblPeople.Last, tblPeople.First FROM ...

    and set the Column Count property of the list box to 2 (so that the last name and first name will be displayed in separate columns), or use

    SELECT tblPeople.Last & ", " & tblPeople.First FROM ...

    and leave Column Count at 1 (names will be displayed as "Sander, Louis").

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Lou Sander' post='781394' date='24-Jun-2009 05:44']...

    I will now try to implement the same thing on frmGroups -- so it shows the names of people in the group that is shown on the current form. This one has a new wrinkle: the names are in tblPeople in two fields: LastName and FirstName. It will be easy, I think, to get just LastName to show in the list box. What I would REALLY like to do, though, is to show the names in the form Last, First.[/quote]

    Would not a main-form/sub-form set up be better?

    You could design your sub-form to look like a list if you want and it would have the advantage of being able to add people to the group directly through the sub-form. Just my 2 penn'orth.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  7. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I am starting to remember some of this stuff. Good idea about the subform, but that's not among the stuff I remember yet. I think I'll get things working along the lines I've started, then optimize it.

    I'm having trouble with the VBA code for the list box on frmGroups. The box is definitely on the form, and its name is definitely List38. But when I try to write Me.List38.Requery, List38 doesn't show up on the (whatever it's called) coding assistant that prompts with possible next words. Other controls on the form DO show up. When I type List38 in anyway, when I view the form I get a Compile Error: Method or data member not found.

    I think this happened with the other form, too, but it cured itself, or I cured it without realizing what I was doing.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The "coding assistant" is called IntelliSense; it should definitely include the list box in the popup list it shows when you type Me.
    If you can't get it to work, I'd delete the list box (after noting its properties), then create a new one.

  9. #9
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I deleted it and re-added it. Intellisense and the application work fine now. Thanks!

    The SQL in the narrow Row Source property field of the text box is l-o-n-g. Is there a way to see it all in a bigger window somewhere?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Lou Sander' post='781503' date='24-Jun-2009 19:52']The SQL in the narrow Row Source property field of the text box is l-o-n-g. Is there a way to see it all in a bigger window somewhere?[/quote]
    You can click in the Row Source property and press Shift+F2 to view it in the Zoom window, or press Ctrl+F2 to view it in the query design window.

  11. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Thanks! What would we DO without Woody's Lounge??? It's a fountain of knowledge, and a lot of times merely framing the question for posting reveals the answer. I wish there were a way of supporting it financially without buying stuff I don't need. ;-)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #12
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'm ready for the next step, but I'm in the dark about how to approach it. I need somebody to point a flashlight in the right direction.

    Each group in tblGroups has a contact person whose information is contained in tblPeople. I'd like to put that person's HandleP into a field in tblGroups, then have their contact information show up on frmGroups.

    I think this is easy, and that the key is to use a subform in frmGroups. I've looked in some books, but the light hasn't come on yet.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create a query based on tblGroups and tblPeople, joined on the HandleP field. Double-click the join line and select the option to return all records from tblGroups and only related records from tblPeople. This ensures that a group will be listed even if no contact person has been assigned to it yet.
    Add all fields from tblGroups to the query grid, and those fields from tblPeople that you want to display on the form.

    Use this query as record source for frmGroups. I'd make the controls read-only that are bound to fields that are originally from tblPeople.

  14. #14
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I understand that this query would replace tblGroups as the data source for frmGroups. Am I correct?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, indeed.

Page 1 of 2 12 LastLast

Posting Permissions

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