I want to create an SQL statement in the row source of a field that will 
make the row source alternate between different tables depending on the 
value in another field in the table.

This is a rough idea of the scenario I am trying to create:

If the value in field 1 in table A is x then the row source of field 2 
will be taken from table B but if the value in field 1 in table A is y 
then the row source of field 2 will be taken from table C etc.

Any help with this problem gratefully received!

Caroline,

<< If the value in field 1 in table A is x then the row source of field 2 
will be taken from table B but if the value in field 1 in table A is y 
then the row source of field 2 will be taken from table C etc. >>

Assuming Field 2 is a combobox, put something like this in the "After 
Update" event for Field 1:

   Select Case Me.[Field 1]
      Case x   
         Me.[Field 2].RowSource = "SELECT DISTINCT [Table B].[Field 2]_
         FROM [Table B] WHERE (([Table B].[Field 1] = " & Me.[Field 1]_
         & "));"
      Case y
         Me.[Field 2].RowSource = "SELECT DISTINCT [Table C].[Field 2]_
         FROM [Table C] WHERE (([Table C].[Field 1] = " & Me.[Field 1]_
         & "));"
      Case z
         .
         . {code for z and other specific values for Field 1 here}
         .
      Case Else
         {code for all other values not covered above}
   End Select   

Greg