Adding Unique Roster Number to Candidates

[WatsonDyar]

Here's the situation:

I have a field on a form that looks up "roster numbers" from another table that holds pre-populated numbers (A-01, A02...through A50; B01, B02, etc). I need to assign each student a number.

How to I limit the query within the lookup field to ONLY display available (unclaimed) roster numbers?

I have the indexes set-up to prohibit assignment of duplicate roster numbers; however, I want a better display for users registering students.

Thanks!

 

[RuralGuy]

If you are really using "Lookup Fields" then you need to read this: http://access.mvps.org/access/lookupfields.htm

 

[jdraw]

I agree with RuralGuy if you're using lookup fields in tables. Lookup fields in tables is not a good design strategy (unique to ms Access). Better approach is to use Lookup tables (applicable to all relational databases).

Here is an older, free video from Datapig on the use of listboxes that may apply.

The other, more common approach, is a combobox of roster numbers filtered by "already assigned numbers".

 

[arnelgp]

the look should look like this:

select [pre-populated number] from lookuptable where [pre-populated number] not in (select [student id] from thisTable);

now on your form, the textbox that has this rowsource. on its after update event you requery this textbox:

private sub textboxStudentCode_AfterUpdate()
me.textboxStudentCode.Requery
end sub