The code should be in the after update event of the frame (option group), not the combo box. Think about it. You are trying to modify the row source of the combo box, but you can only do it AFTER you have made a change to the combo box.
And in both row source strings, you are missing spaces after "SELECT" and between the "*" and "FROM".
Although it is not an "ERROR", you shouldn't use spaces in object names.
(See http://mvps.org/access/tencommandments.htm - #3)
Code:
'-------------------------
Private Sub Objref_AfterUpdate()
'load appropriate control source for Combo Box Referral Broker/Consultant
Select Case Me.[Objref]
Case 1
Me.cboRef.RowSource = "SELECT [Brokers Extended].[Broker Name],[Brokers Extended].* FROM[Brokers Extended]"
Case 2
Me.cboRef.RowSource = "SELECT [Consultants Extended].[Consultant Name],[Consultants Extended].* FROM [Consultants Extended]"
End Select
Me.cboRef.Requery
End Sub
Question: Why two tables? Are the fields for Brokers that much different than Consultants? I don't think your structure is normalized...