Question about Command Buttons and List Boxes on a Form

[Nuke1096]

Hello!



I just recently started learning access, as I do alot of work with it on my new job. Currently I'm trying to create a specific database but having some issues. Mind you, I've only been using access for about a week so I'm pretty green still.

Basically what I want to do is have a series of Command Buttons near the left part of my form, when clicked on display unique information in a list box along the right side of the form. From there, I want to click on the options in the list to fill in a series of fields along the bottom part of the form. I made a diagram to better explain what I'm talking about.



So basically I have Command Buttons on the left for each Department. I have a separate table made for each Department. So Department One has a specific table made for it that has Person 1, 2, 3, 4, 5 for example. And Department 2 has its own table that has Person 6, 7, 8, 9, 10, etc. What I want is to have it so when I click on the Department 1 Button, the List Box to the right shows a list of everybody from Department 1. Or when I click on Department 2, it shows everyone in Department 2, etc etc.

Then, what I want is to click on a person within that list, to show additional information about that specific person in the fields at the bottom.

How I have it setup table wise is that I have a Master Table that has everybody in it, all with the additional information such as Last Name, Email, ID, Title, etc etc. Then I have smaller tables for each individual Department connected to the Master Table with a relationship. So kinda like this...


Department One Table

[Auto-number Primary Key Field] [First Name]

- Person 1
- Person 2
- Person 3
- Person 4
- Person 5


Department Two Table

[Auto-number Primary Key Field] [First Name]

- Person 6
- Person 7
- Person 8
- Person 9
- Person 10



Master Table

[Auto-number Primary Key Field] [First Name] [Last Name] [Title] [ID] [Email]

- Person 1
- Person 2
- Person 3
- Person 4
- Person 5
- Person 6
- Person 7
- Person 8
- Person 9
- Person 10


Make sense? Anyway, any help would be much appreciated. I'm basically looking for some advice on how to best do this.

[Bob Fitz]

Not sure that your table structure is quite right.
See the attached db.

[Nuke1096]

That's definitely how I want it to work. Thanks for uploading that. However, one question that I am confused on. Where are the actual tables? Where is it pulling that information from? All I see is a Form in the attached DB.

[Nuke1096]

Nm, the tables were hidden along the left hand pane. I see them now.

This definitely puts me on the right path, thank you very much. I will post back if I have any further questions

[Nuke1096]

Actually I gots a question already. How do I make so that Last Name field is invisible in the List box portion. I just want a single field to show up in the List Box, but multiple fields to show up in the bottom part. So I either need a way for a single field to load all the info into the fields below, or a way to make the extra fields in the list box be invisible.

[Bob Fitz]

The form is unbound.
Look at the Row Source property of each list control, to see how they are populated.
The text boxes that display the names are populated by referencing the columns of the list box called lstPeople.

[Bob Fitz]

Actually I gots a question already. How do I make so that Last Name field is invisible in the List box portion. I just want a single field to show up in the List Box, but multiple fields to show up in the bottom part. So I either need a way for a single field to load all the info into the fields below, or a way to make the extra fields in the list box be invisible.

A list box has a Column Widths property. If you set a column to 0cm, then it will not be visible. The lstPeople list box already has the first two columns hidden.

[Nuke1096]

Quick question.

Alternatively, how would I use a Command Button instead of a List box to reference the 1stPeople List Box instead of using the 1stDept List Box to do so. I realize I would need to create 1 Button for each Department, but thats ok. I would prefer that.

[Bob Fitz]

You could use a button for each department instead of a list box, but that the advantage of a list box is that if new departments are created the form would then need more buttons added to it. With a list box the new departments would just appear in the list. So, are you sure you still want buttons?

[Nuke1096]

Ya I think I still want to go with buttons. Even if just for the learning experience.

[Bob Fitz]

OK, as compromise, how about this:

[Nuke1096]

Yup that worked fine. I have everything working at the moment. I just need to build the database and then make it look pretty.

Thx for all the help. If I have any more questions I'll check back to this thread