VBA to amend SQL string

[cpberg1]

Hey All,

Pretty new to using VBA. I can find what I need to do, usually online and amend to use for my application. Can write very basic. Using Access 2003.

I have an unbound form with many unbound comboboxes that I'm using to set the filter property of a subform bound to a query. Clear all filters command button uses loop to set each of the comboboxes to Null.

Everything is working as it should. I want to add the ability to have an ALL choice in my combobox so the user can select to see all values or perhaps an "All except XX" type choice. When "ALL" is selected the filter should not filter based on the status criteria, almost as though the combobox were null.

I've added a UNION "(ALL)" in the rowsource for the combobox so I have an ALL choice in the list.

I'm looking for a bit of help to amend the filtering code used to create the filter clause when the ALL choice is selected. Excerpt of code posted below.

If Len(Me.cmbStatusSelect.Value & "") > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[StatusName]='" & Me.cmbStatusSelect.Value & "'"
End If

If FilterClause = "" Then
Forms("FIAllocation")("FIallocation subform").Form.FilterOn = False
Else
CurrentFilter = FilterClause: FilterClause = ""

Forms("FIAllocation")("FIAllocation subform").Form.Filter = CurrentFilter
Forms("FIAllocation")("FIAllocation subform").Form.FilterOn = True
End If

Exit_FIAllocationSearch:
Exit Function

Error_FIAllocationSearch:
MsgBox "FIAllocation Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"FI Allocation Error"
Resume Exit_FIAllocationSearch
End Function

I've tried to put the existing code into parenthesis and first check IIF me.cmbstatusselect.value = "(ALL)", Filterclause="", rest of the code here

I keep getting errors with it so either my understanding of the code, the syntax or my whole idea is messed up! Anyone have some direction to point me?

Thanks!

 

[MarkK]

For best results please specify what errors you are getting at what lines of code.
Cheers,