If Condition In Rowsource Of An Unbound Combo-box

  • Hi Access VBA Gurus,


    I currently have a fully functional SQL Select statement (as shown below) in the RowSource property of an UNbound ComboBox in an Access 2002 form:


    SQL
    SELECT DISTINCTROW qryKeyAgentAddEditForm.[Key Agent] FROM qryKeyAgentAddEditForm WHERE qryKeyAgentAddEditForm.[Key Agent Type]=cboKeyAgentType.Value ORDER BY qryKeyAgentAddEditForm.[Key Agent];



    I need to put in a conditional IF statement to the following effect:


    Code
    IF condition1 Then
    SELECT DISTINCTROW qryKeyAgentAddEditForm.[Key Agent] FROM qryKeyAgentAddEditForm ORDER BY qryKeyAgentAddEditForm.[Key Agent];
    ELSE
    SELECT DISTINCTROW qryKeyAgentAddEditForm.[Key Agent] FROM qryKeyAgentAddEditForm WHERE qryKeyAgentAddEditForm.[Key Agent Type]=cboKeyAgentType.Value ORDER BY qryKeyAgentAddEditForm.[Key Agent];
    ENDIF


    Is such a conditional IF condition valid as the RowSource property of an UNBound combo-box in Access VBA?


    Many thanks in advance to anyone who can help/advise

  • Re: If Condition In Rowsource Of An Unbound Combo-box


    Hi


    If it is going to work you will have to use the Access form of the IF statement; it is basically the same as the Excel statement. Thus:

    Code
    IIf(Condition, True statement, False statement)


    Note that the Access if has two eyes and elses are achieved by nesting the ifs in either true or false statments.


    If this doesn't work in the control properties you could set the rowsource using vba and requery the control. Without knowing more about your form and how it is used, I can't advise on which event you could set the rowsource, but this is a method I have used successfully. You may be use the "After Update" event of another control to set the rowsource and requery the combo control.


    Hope this helps.


    Cheers


    Rowan

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!