Your description of having the RowSource come from two different tables suggests you have not normalized properly.
I expect you should have the RowSource records all in the one table with an extra field to select which of them is associated with the value in the "preceding field". The RowSource query simply refers to that control in the Where clause. A Requery of the combo is done in the Form OnCurrent and the "previous field's" Control AfterUpdate.