Adding "All" selection to a combo box that acts as a filter for an SQL statement

SGT68

Registered User.
Local time
Today, 02:39
Joined
May 6, 2014
Messages
77
Adding "All" selection to a combo box that acts as a filter for an SQL statement

My form has a combo box which is bound to a query that selects a list of member numbers. The value selected the goes onto filter a list driven by an SQL statement.

Code:
Private Function PopulateADO(qpMemberNo As Long)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    Dim strSQL As String
   
    
    strSQL = "SELECT [PEOPLE_MEMBERSHIP MASTER].MemberNo,....[LOADS MORE]
    "WHERE [PEOPLE_MEMBERSHIP MASTER].MemberNo =" & qpMemberNo

..[MORE STUFF]

End Function


Private Sub ComboMember_AfterUpdate()
Call PopulateADO(Me.ComboMember)
End Sub

This works fine when a single member is selected from the combo. But obviously i want a "select all" or * wildcard option.

Question is 2 parts.
1. How do i add the all or wildcard option to the bound combo?
2. How do i code the all or wildcard option into the SQL statement?
Is it something like is not null ?
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

First, a wildcard is different from all. So which do you mean?

A wild card looks like this:

WHERE [YourField] Like "*17*"

That will return all records where the YourField value has 17 anywhere in it. That's a wild card.

Selecting ALL would not use a WHERE clause. So in those instances you wouldn't add the WHERE clause onto the statement.


So which is it?
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

First, a wildcard is different from all. So which do you mean?

A wild card looks like this:

WHERE [YourField] Like "*17*"

That will return all records where the YourField value has 17 anywhere in it. That's a wild card.

Selecting ALL would not use a WHERE clause. So in those instances you wouldn't add the WHERE clause onto the statement.


So which is it?

Thanks for your reply.I want the combo box to carry an "All" option. Forget the wildcard
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

Is your combo box show a list of fields or a list of distinct values?
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

Selecting ALL would not use a WHERE clause. So in those instances you wouldn't add the WHERE clause onto the statement.

I realise that but what if, as in my code i was using an SQL stmt in a function that accepts the WHERE parameters as arguments. In a case where the argument is "ALL", i would need to code something like
strSQL = "SELECT [PEOPLE_MEMBERSHIP MASTER].MemberNo,....[LOADS MORE]
"WHERE [PEOPLE_MEMBERSHIP MASTER].MemberNo =" IS NOT NULL

I want to use just the one function, I dont want to have to build in another mechanism that branches out to different SQLs. There are going to be at least 4 combos passing 4 arguments.
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

Is your combo box show a list of fields or a list of distinct values?

dont know what you mean exactly


Properties:
Row Source
SELECT [PEOPLE SHORT MEMBER LIST].[Surname], [PEOPLE SHORT MEMBER LIST].[FName], [PEOPLE SHORT MEMBER LIST].[Title], [PEOPLE SHORT MEMBER LIST].[MemberNo] FROM [PEOPLE SHORT MEMBER LIST] ORDER BY [Surname], [FName];

Bound Column: 4
Limit to List: Yes
Allow Value List Edits: Yes
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

I feel there isn't enough information given about your project in order to fully understand, and help with a solution.

Is that the Control Source of your combo box...
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

If you need to pass it a WHERE clause then in the cases you want to show all, use this:

WHERE (1=1)

For your drop down, I would set the Default value to "All". That way the options show, but the initial value is "All". In your VBA you would see if the value is "All" and then send it the WHERE (1=1), if not "All" construct your WHERE clause as you have been.
 
Re: Adding "All" selection to a combo box that acts as a filter for an SQL statement

you could just populate it manually onload
 

Users who are viewing this thread

Back
Top Bottom