Adding "All" to a SELECT DISTINCT combobox

[hockey8837]

I have been trying to set up two cascading combo boxes.
cboDate feeds---> cboZip
My last step is to put an option in the cboZip (the child cbo) an '(All)' option so users can select all zip codes for that date if they want.

My VBA in the After Update event for cboDate is:

Private Sub cbodate_AfterUpdate()
 On Error Resume Next
   cboZip.RowSource = "Select distinct qryLocationofTreeWork.LocationZip " & _
            "FROM qryLocationofTreeWork " & _
            "WHERE qryLocationofTreeWork.TargetPlantingDate = '" & cboDate.Value & "' " & _
            "ORDER BY qryLocationofTreeWork.LocationZip;"
End Sub

The row source for cboDate is:

SELECT qryLocationOfTreeWork.TargetPlantingDate FROM qryLocationOfTreeWork GROUP BY qryLocationOfTreeWork.TargetPlantingDate ORDER BY qryLocationOfTreeWork.TargetPlantingDate;

The row source for cboZip is:

SELECT DISTINCT ztblZip.LocationZip FROM ztblZip;

I've looked at http://support.microsoft.com/kb/210290
http://www.mvps.org/access/forms/frm0043.htm

and I tried adding in UNION SELECT '(All)', null to both the cboZip row source and into the code, and couldn't get it to work.

I've attached a slimmed down version of the DB.

Thanks for any suggestions on how to get '(All)' to appear in cboZip!

 

[Insane_ai]

Use your rowsource query as a recordset instead. Loop through the records to create a string of values. Add "All" to the beginning by initializing the string with it or adding it to the end. The question then becomes, what do yo udo when the user selects the all option?


Example: ( I wrote this in notepad so please excuse any errors / typos)

Private Sub cbodate_AfterUpdate()
Dim DB as DAO.Database
Dim RS as DAO.Recordset
Dim strSQL, strRowSource as String
Set DB = CurrentDB
strSQL = Select distinct qryLocationofTreeWork.LocationZip " & _
"FROM qryLocationofTreeWork " & _
"WHERE qryLocationofTreeWork.TargetPlantingDate = '" & cboDate.Value & "' " & _
"ORDER BY qryLocationofTreeWork.LocationZip;"
Set RS = DB.OpenRecordset (strSQL)
strRowSource = "All;"
If RS.BOF = True And RS.EOF = True Then
'There are no Records
Exit Sub
Else
With RS
.MoveFirst
Do Until .EOF
strRowSource = strRowSource & ![LocationZip] & "; "
.MOveNext
Loop
End With
End If
cboZip.RowSource = strRowSource

 

[ChipperT]

I have been trying to set up two cascading combo boxes.
cboDate feeds---> cboZip
My last step is to put an option in the cboZip (the child cbo) an '(All)' option so users can select all zip codes for that date if they want.

My VBA in the After Update event for cboDate is:

Private Sub cbodate_AfterUpdate()
 On Error Resume Next
   cboZip.RowSource = "Select distinct qryLocationofTreeWork.LocationZip " & _
            "FROM qryLocationofTreeWork " & _
            "WHERE qryLocationofTreeWork.TargetPlantingDate = '" & cboDate.Value & "' " & _
            "ORDER BY qryLocationofTreeWork.LocationZip;"
End Sub

The row source for cboDate is:

SELECT qryLocationOfTreeWork.TargetPlantingDate FROM qryLocationOfTreeWork GROUP BY qryLocationOfTreeWork.TargetPlantingDate ORDER BY qryLocationOfTreeWork.TargetPlantingDate;

The row source for cboZip is:

SELECT DISTINCT ztblZip.LocationZip FROM ztblZip;

I've looked at http://support.microsoft.com/kb/210290
http://www.mvps.org/access/forms/frm0043.htm

and I tried adding in UNION SELECT '(All)', null to both the cboZip row source and into the code, and couldn't get it to work.

I've attached a slimmed down version of the DB.

Thanks for any suggestions on how to get '(All)' to appear in cboZip!

Where do you want the "All" to appear? First? Last? Somewhere else?

 

[nathansav]

In the past i have used something like this

"Select DISTINCT 0,'(All)' as E1 FROM tbl_HR; " & _
"UNION ALL " & _
"Select Dept_ID,[Emp_Name] from tbl_HR WHERE Dept_ID=" & me.cboDept

 

[hockey8837]

Use your rowsource query as a recordset instead. Loop through the records to create a string of values. Add "All" to the beginning by initializing the string with it or adding it to the end. The question then becomes, what do yo udo when the user selects the all option?


Example: ( I wrote this in notepad so please excuse any errors / typos)

Private Sub cbodate_AfterUpdate()
Dim DB as DAO.Database
Dim RS as DAO.Recordset
Dim strSQL, strRowSource as String
Set DB = CurrentDB
strSQL = Select distinct qryLocationofTreeWork.LocationZip " & _
"FROM qryLocationofTreeWork " & _
"WHERE qryLocationofTreeWork.TargetPlantingDate = '" & cboDate.Value & "' " & _
"ORDER BY qryLocationofTreeWork.LocationZip;"
Set RS = DB.OpenRecordset (strSQL)
strRowSource = "All;"
If RS.BOF = True And RS.EOF = True Then
'There are no Records
Exit Sub
Else
With RS
.MoveFirst
Do Until .EOF
strRowSource = strRowSource & ![LocationZip] & "; "
.MOveNext
Loop
End With
End If
cboZip.RowSource = strRowSource

I'd like for the 'All' selection to be a the top of the list of zip codes, and if the user selects it, then selects the report they want to run, that all zip codes under the cboDate will be included in the report.

Tried to put the above code in, but the 'All' selection didn't show up anywhere.

 

[nathansav]

Try
cbodate_Change()
strSQL = "Select distinct '(All)' From qryLocationofTreeWork UNION ALL " & _
"Select distinct qryLocationofTreeWork.LocationZip " & _
"FROM qryLocationofTreeWork " & _
"WHERE qryLocationofTreeWork.TargetPlantingDate = '" & cboDate.Value & "' " & _
"ORDER BY qryLocationofTreeWork.LocationZip;"
me.cbozip.rowsource = strSQL
me.cbozip.requery
exit sub

 

[hockey8837]

Nope, still no changes in the cboZip after making those changes... Do I need to change something with the row source of the cboZip? I noticed that I couldn't get it to SELECT DISTINCT until I changed it in the vba and the row source...

 

[Insane_ai]

Try using the Form_Open() event instead to update the list.

 

[vbaInet]

In the Row Source of the combo box:

SELECT Dept_ID, [Emp_Name], 0 As SortID
From tbl_HR 
WHERE Dept_ID=" & me.cboDept
UNION ALL 
SELECT DISTINCT 0, '(All)' as E1, Null  
FROM tbl_HR
ORDER BY SortID, [Emp_Name];

It will show in the second column so you need to adjust the Column Widths property.

 

[nathansav]

Have you got this working now?

 

[Brianwarnock]

I've never understood why people go to the trouble of including ALL in the combo, perhaps I've missed something, but just tell the users that selecting nothing selects all
then instead of field=combo or combo="ALL" use
field =combo or combo Is Null

I wait for the flak

Brian

 

[vbaInet]

I use All as well

The thing is there are some records that contain Null so if we use Null to represent All records, how do we represent those Null values? For me that's the reason why I use Null (when needed).

 

[Brianwarnock]

Fair comment vba , guess I never had a situation when a value of Null was valid.

Brian

 

[vbaInet]

I get a few of these cases.

A good example would be something like Categories. You may not want to categorise a record so obviously that would leave the Category Null, and you would still want to be able to search for uncategorised records.

 

[Brianwarnock]

The problem with Null is that you do not know if the field has been deliberately or accidentally left blank (null), I would have a positive entry for in your example, no category.

A search for Nulls then becomes a DB admin issue not a user issue.

Brian

 

[vbaInet]

If the form was properly built with full validation in place, there will be no accidental blank fields. Naturally, you would only allow Nulls for a field that is optional, like in my example, a category can be optional. So when you're searching for Null in such a field within existing records, you know quite alright that the user didn't categorise that record.

So if a user creates a new record and wants to leave the category blank, it's either they don't select anything, or if they had selected an item before and now want to make it blank, they will select the blank field in the combo box.

The (All) bit will just be in the search pane of the form, not in the actual value selections.

 

[Insane_ai]

I've never understood why people go to the trouble of including ALL in the combo, perhaps I've missed something, but just tell the users that selecting nothing selects all
then instead of field=combo or combo="ALL" use
field =combo or combo Is Null

I wait for the flak

Brian

It has been my experience that users like to see the "all" in the filters so they don't have to guess.