If Condition In Rowsource Of An Unbound Combo-box

Om Avataar · Apr 25th 2007

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

RowanS · Apr 28th 2007

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

If Condition In Rowsource Of An Unbound Combo-box

Participate now!

Share