Changing row source

[joacro]

Hi there,

I have a form called Referal with two option buttons
Then I have a combo box.
Should I select the first option box the combo box row source should change to Consultants Name found in the Consultants extended query
Should I select the second option box the combo row source should change to Brokers Name found in the Brokers extended query

Here is what I put into the after update event
rivate Sub cboRef_AfterUpdate()
'load appropriate control source for Combo Box Referral Broker/Consultant
Select Case Me![Objref]
Case 1
RowSource = "SELECT[Brokers Extended]"


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
End Sub

But I keep on getting a error saying "The record source on the specifiec form does not exist.

Hope someone will be able to help me.

Thanks in advance

[ssanfu]

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)

'-------------------------
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...

[joacro]

Thank you that sorted my problem

[RuralGuy]

I take it you are ready to use the Thread Tools and mark this thread as Solved?