Changing rowsource based on lookup field

[AdamAA]

I have a table with First Name, Last Name and Current Position columns.

I have a second table with a First Name lookup to the first table, but also other columns like 'Time on Shift', 'Time off Shift', and 'Overtime'

(I know lookups are bad, but these are tables from Sharepoint and it's an established system I can't really get away from).

The second table sits in a Continuous Form, but I only want the First Name field to list people that are in the Current Position I specify with a combobox at the top of the form.

I know how to change the row source manually:

SELECT [Sub Contractors Table].ID, [Sub Contractors Table].[First Name], [Sub Contractors Table].Surname FROM [Sub Contractors Table] WHERE (([Sub Contractors Table]![Current Position]="******")) ORDER BY [Sub Contractors Table].[First Name];

But I would like to change '******' based on what I select in the combobox. I'mj just not sure how to do this programatically.

Thanks in advance!

 

[John Big Booty]

Look Up tables are fine it's Look ups at Table level that are bad.

The sample here has Combo Box with a Dynamic Row Source. Perhaps that will point you in the right direction.