MS Access : Add Select All to combo box

Question

i tried this : To add an (All) item to the list, return to Design view and select the combo box control. Then, replace the Row Source property with the following SQL statement:

SELECT City FROM Employees 
UNION SELECT "(All)" FROM Employees;

from here : https://msdn.microsoft.com/en-us/library/aa140084%28v=office.10%29.aspx

My Sql statement looks like this :

SELECT LastName FROM Employee  
UNION SELECT "(All)" FROM Employee;

I can see the (All) in my drop down,the problem is when I select the Option (All) i receive a blank page.

Am I missing something? Do I need some VBA Code ? This is my current Code:

Private Sub cboFilter2_AfterUpdate()
Dim myFilter As String
myFilter = "Select * from Employee_Filter_Query where ([LastName] = '" & cboFilter2 & "')"
Me.Employee_Filter_subform.Form.RecordSource = myFilter
Me.Employee_Filter_subform.Form.Requery
End Sub

Br,

Brad Brad · Accepted Answer · 2015-10-12T15:00:53

You likely do not have a last name of "(All)" and SQL doesn't automatically know to select everything when you pick "(All)" form the combobox. You'll need to adapt your query:

"Select * from Employee_Filter_Query where ([LastName] = iif('" & cboFilter2 & "'='(All)',[LastName],'" & cboFilter2 & "'))"

WHat this will do is look for the combobox to say "(All)" and if it does then match [LastName] = [LastName] which will always be true and return all records, otherwise it will match [LastName] against your filter and act like normal.

An alternative is to set up like this:

If cboFinter2 = "(All)" Then
    myFilter = "Select * from Employee_Filter_Query"
Else
    myFilter = "Select * from Employee_Filter_Query where ([LastName] = '" & cboFilter2 & "')"
End If

MS Access : Add Select All to combo box

1 Answers