Listbox 1 Reference to Listbox 2...

[donniedarko]

Hello. I am new to access and had a different question...

I want to design this form where instead of typing in search criteria one would simply do the following:

Example:

Form has ADD/Remove button(s), 2 List boxes, Labels and Text boxes, etc...

a.) Once user click add button he will be able to add let's say... a Supervisor. Upon entering and saving supervisor's first and last name, supervisor's information gets added to Listbox 1.

b.) Once supervisor's name is selected from Listbox 1, Add Employee button unlocks and when clicked, brings up a second form letting user enter Employees information and then saving it accordingly.

c.) Now, List box 1 contains Supervisor name that when selected will make Listbox 2 display whatever employee was saved under that supervisor.

I can not find any example database on the net that does use this kind of aproach. I also think that there may be some vb code included. Gosh! So much for me thinking that Access would be very similar to Excel which I'm very good at. Can anyone at least show me the light? hehe...

Thank you all in advance for any answers given to my post.

 

[daveUK]

You will need 2 tables. One (Supervisor Table) will hold the SupervisorID (Primary Key) and the SupervisorName (this can be split into 2 fields if you want to use firstname and surname).

The second table will hold the EmployeeID (Primary Key), SupervisorID (from the Supervisor Table), EmployeeName (again, this can be split into firstname and surname if required).

To display the details you will need a main form and subform

The main form has a combo-box, or listbox, which is based on the Supervisor table and holds the names of the supervisors. The subform is based on the employees table. You will need to link the master/child fields of the main and sub form in order that the correct employees are displayed for the correct supervisor. You could also hold the employee details in a listbox on the subform.

HTH

 

[donniedarko]

Thank you for replying Dave. Would it be possible to have both Listboxes in One form?

 

[daveUK]

Yep, you would just need to requery the 2nd listbox (employee records) after selecting the value in the 1st listbox (Supervisor record)

 

[donniedarko]

Thanx for the help once again. One Last question...

Now that I Acomplished the above is the following possible? If so, How?

1. Click name from Supervisor listbox 1 = Show Employee's in listbox 2 under that supervisor.
Done!

2. Click name from listbox 2 = Show selected Employee's details in pre-designed textboxs.

Not Done!

How would I create step 2?

All help would be greatly apreciated

 

[RV]

You've got what's called a recursive relationship.
You need just one table, not two:

www.access-programmers.co.uk/forums/showthread.php?t=59771

RV

 

[Mile-O]

Beat me to it, RV.

Always got to remember that even supervisors can have supervisors.

Your link does not work though: This does.

 

[donniedarko]

woah... what was that again? I'm not used to think recursive. Would you mind posting a quick *.mdb example for me please? I'd worship you forever!!!

I think you'd need 2 listboxes and 1 textbox. Something really simple just so I could get the point. Once again, I'd really really really apreciate if you posted a *.mdb example for me.

Thank you guys for answering so far. Meanwhile, I'll be trying to figure out the old fashioned "test & error" way, hehe.

 

[RV]

Haven't got any examples in Access....
Open the link I referred to and / or search on BOM.

RV

 

[donniedarko]

well, like i said i've already read stuff found in that link reason why i asked for an example. thanx anyway. i'll just try a different aproach.

 

[WayneRyan]

Recursive thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=51083&highlight=recursive+wayne

Wayne