Add <All> to combo box selection

[oxicottin]

Hello, I have a combobox on my search form that uses a query as its RowSource to display a text employee name. I want to be able to select "All" to display all records.... below is my RowSource.

SELECT [qry_ActiveInactiveEmployees].[EmployeeName] FROM [qry_ActiveInactiveEmployees] ORDER BY [EmployeeName];

Here is what I tried with no luck....

SELECT EmployeeName FROM qry_ActiveInactiveEmployees 
UNION SELECT "<All>" FROM qry_ActiveInactiveEmployees SELECT [qry_ActiveInactiveEmployees].[EmployeeName] FROM [qry_ActiveInactiveEmployees] ORDER BY [EmployeeName];

Thanks,
SoggyCashew.....

 

[genomon]

Any help here?
[link ]

http://stackoverflow.com/questions/16598991/ms-access-multi-select-combo-box-select-all-none

[/url]

Beir bua agus beannacht!

 

[Andrzejek]

Did you try:
[tt]
SELECT "<All>" AS EmployeeName FROM qry_ActiveInactiveEmployees
UNION
SELECT [qry_ActiveInactiveEmployees].[EmployeeName]
FROM [qry_ActiveInactiveEmployees] ORDER BY 1
[/tt]



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[oxicottin]

Andy, your example does give me the "All" at the top of my list but it doesnt do anything. I have also tried using the employeeID number which gives me the <All> but doesnt do anything untill I select an employees name from the list.

SELECT 0 as EmployeeID,  "< ALL >" as EmployeeName FROM qry_ActiveInactiveEmployees Union SELECT [qry_ActiveInactiveEmployees].[EmployeeID], [qry_ActiveInactiveEmployees].[EmployeeName] FROM [qry_ActiveInactiveEmployees] ORDER BY [EmployeeName];

Thanks,
SoggyCashew.....

 

[PHV]

untill I select an employees name from the list
Which code is executed when an employee is selected ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[oxicottin]

PHV, with Andys example I tried the commented out part of my search function and with the part not commented out I tried in my last responce.

If Not IsNull(Me.cboEmployeeName) Then
        'varWhere = varWhere & "([EmployeeName] = """ & Me.cboEmployeeName & """) AND "
        varWhere = varWhere & "([EmployeeID] = " & Me.cboEmployeeName & ") AND "
    End If

Thanks,
SoggyCashew.....

 

[oxicottin]

Here is the whole thing in short...

Private Sub cboEmployeeName_AfterUpdate()
' Update the record source
    Me.Form.RecordSource = "SELECT * FROM qry_SearchEntries " & BuildFilter
    Me.Requery
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim intIndex As Integer
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    varWhere = Null
    
    If Not IsNull(Me.cboEmployeeName) Then
         varWhere = varWhere & "([EmployeeName] = """ & Me.cboEmployeeName & """) AND "
        'varWhere = varWhere & "([EmployeeID] = " & Me.cboEmployeeName & ") AND "
    End If
    
    If Not IsNull(Me.txtContactDate) Then
        varWhere = varWhere & "([DateOfIncident] = " & Format(Me.txtContactDate, conJetDate) & ") AND "
    End If
        
    If Not IsNull(Me.optContactGroup) Then
        If Me.optContactGroup.Value = 1 Then
        varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        Else
        varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        End If
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere

End Function

Thanks,
SoggyCashew.....

 

[PHV]

If Not IsNull(Me.cboEmployeeName) [!]And Me.cboEmployeeName <> "< ALL >" [/!]Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

You would be a lot better off if you can actually SEE your Filter:
[tt]...[blue]
Debug.Print varWhere[/blue]
BuildFilter = varWhere
...
[/tt]
This way you would notice right away that you have something like:
[tt]
([EmployeeName] = "<All>") AND ...
[/tt]

Unless you do have an employee with the name <All>, this will not work :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[oxicottin]

Andy if I did it PHV way and debug it does give me....

WHERE ([EmployeeID] = 0)

So shouldnt that show all employees since there is no 0 ID? .... I use the same technique on a simular search and it works I just cant figure out why not with this one....

Thanks,
SoggyCashew.....

 

[PHV]

So, use this:
If Not IsNull(Me.cboEmployeeName) And Me.cboEmployeeName <> 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[oxicottin]

PHV, lol I dont know why I didnt try zero instead of the <All>..... I worked great! Thanks very much!!!!

Thanks,
SoggyCashew.....

 

[Andrzejek]

You can thank PHV by clicking on:
[blue]
Like this post?
Star it![/blue]

This way other TT users will know this post was helpful



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PHV]

Even simpler:
If Nz(Me.cboEmployeeName, 0) <> 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886