Trying to create a lookup field to dislay a dropdown list from another column in same table

[Sgt_Utz]

I am very new to Access so I apologize. I have a personnel table for all personnel within my Squadron (I'm in the Air Force). I want to create a lookup field with a dropdown to select the persons direct supervisor. This list needs to be all the people in the same table which it doesn't like. Does The Supervisor field have to be in a different table?



I'd also like to filter out in the dropdown list anyone who is a lower rank than the person I'm trying to select the supervisor of. I created a table for ranks and assigned each rank a number. So basically if the person is a SSgt and is assigned number 5, then filter out all person except those with a rank of 1-5 (sometimes someone will be the supervisor of another person of the same rank).

[ranman256]

no, everything can be in 1 table
PersonID
FirstN
LastN
SuperID

the superID would be the PersonID in the very same table. No need for extra tables.

the Rank table in a query would feed the combo box (drop box)
then it would filter the list:

sub cboBox_Afterupdate()
If IsNull(cboBox) Then
  Me.FilterOn = False
Else
  Me.Filter = "[RankCode]=" & cboBox 
  Me.FilterOn = True
End If
end sub

or:

"[RankCode] <= " & cboBox

[Sgt_Utz]

I've been playing with it for a while and got it to get the list of everyone, but not sure how this filter code works. I attached a picture showing my tables and query and how it's setup. Where do I add this code?

[Sgt_Utz]

*bumpity bumpity*

[Sgt_Utz]

I'm sorry I'm a little confused about your code part. Where do I add this?

no, everything can be in 1 table
PersonID
FirstN
LastN
SuperID

the superID would be the PersonID in the very same table. No need for extra tables.

the Rank table in a query would feed the combo box (drop box)
then it would filter the list:

sub cboBox_Afterupdate()
If IsNull(cboBox) Then
  Me.FilterOn = False
Else
  Me.Filter = "[RankCode]=" & cboBox 
  Me.FilterOn = True
End If
end sub

or:

"[RankCode] <= " & cboBox

[Gicu]

You should avoid using lookup fields in tables (http://access.mvps.org/access/lookupfields.htm for more info) and use a combo box instead. Simply create a form based on your table, open it in design view and right click on the supervisor textbox and choose Change To and select combo box. For the new combo select column count as 2, Column Widths as 0";2", List Width as 2" RowSource Type as Table/Query and for the Row Source clcik the builder icon on the right (...) and build your Select statement:
"Select ID,FullName:[LastName] &.....(like you already have it) FROM Personnel WHERE [Rank] > Forms![frmYourNewFormName]![Rank];".
And finally in the Enter event of the supervisor combo box enter Me.cboSupervisor.Requery.

Cheers,
Vlad

[Sgt_Utz]

Thank you. I will play with this. Appreciate the wisdom!

You should avoid using lookup fields in tables (http://access.mvps.org/access/lookupfields.htm for more info) and use a combo box instead. Simply create a form based on your table, open it in design view and right click on the supervisor textbox and choose Change To and select combo box. For the new combo select column count as 2, Column Widths as 0";2", List Width as 2" RowSource Type as Table/Query and for the Row Source clcik the builder icon on the right (...) and build your Select statement:
"Select ID,FullName:[LastName] &.....(like you already have it) FROM Personnel WHERE [Rank] > Forms![frmYourNewFormName]![Rank];".
And finally in the Enter event of the supervisor combo box enter Me.cboSupervisor.Requery.

Cheers,
Vlad