Full Version: Adding (all) To A Combo Box
UtterAccess Forums > Microsoft® Access > Access Forms
dfishingski
I've read and I'm not getting it so please help. I've got a main form named PlaceOrder
and a continuous subform named PlaceOrder_Subform on the subform I Have a checkbox and a combobox
in the header of the subform. The checkbox is for show discontinued products and the combo box
is for ProductCategoriesID. In The detail section of the subform I have a combo box for ProductID. What
I would like to happen is on load the ProductCategoriesID would be set up to show (All) products and the combo box
would provide the option to go back to viewing view (All) products after a specific category had been selected.I copied some
code for a function call AddAllToList from Microsoft and placed the code in the form module. The (All) shows up in the combobox
but I can't get it to do anything but give me an empty combobox when I select (All), the rest of the selections work just fine so here
is the criteria I've got in the combobox queries and the code I copied. Can someone help me out thanks in advance.

ProductCategoriesID criteria is SELECT ProductCategories.ID, ProductCategories.CategoryName FROM ProductCategories UNION SELECT Null, '(All)' FROM ProductCategories;

ProductID criteria is SELECT Products.ID, Products.ProductName FROM Products WHERE (((Products.ProductCategoryID)=[Forms]![PlaceOrder]![PlaceOrder_Subform].[Form]![ProductCategoriesID])
AND ((Products.Discontinued)=GetShowDiscont())) ORDER BY Products.ProductName;

Code I copied Is

Function AddAllToList(C As Control, ID As Long, Row As Long, _
Col As Long, Code As Integer) As Variant

'***************************************************************
' FUNCTION: AddAllToList()
'
' PURPOSE:
' Adds "(all)" as the first row of a combo box or list box.
'
' USAGE:
' 1. Create a combo box or list box that displays the data you
' want.
'
' 2. Change the RowSourceType property from "Table/Query" to
' "AddAllToList."
'
' 3. Set the value of the combo box or list box's Tag property to
' the column number in which you want "(all)" to appear.
'
' NOTE: Following the column number in the Tag property, you can
' enter a semicolon (wink.gif and then any text you want to appear
' other than the default "all."
'
' For example
'
' Tag: 2;<None>
'
' displays "<None>" in the second column of the list.
'
'***************************************************************
Static DB As Database, RS As Recordset
Static DISPLAYID As Long
Static DISPLAYCOL As Integer
Static DISPLAYTEXT As String
Dim Semicolon As Integer

On Error GoTo Err_AddAllToList

Select Case Code
Case LB_INITIALIZE
' See if the function is already in use.
If DISPLAYID <> 0 Then
MsgBox "AddAllToList is already in use by another Control!"
AddAllToList = False
Exit Function
End If

' Parse the display column and display text from the Tag
' property.
DISPLAYCOL = 1
DISPLAYTEXT = "(All)"
If Not IsNull(C.Tag) Then
Semicolon = InStr(C.Tag, ";")
If Semicolon = 0 Then
DISPLAYCOL = Val(C.Tag)
Else
DISPLAYCOL = Val(Left(C.Tag, Semicolon - 1))
DISPLAYTEXT = Mid(C.Tag, Semicolon + 1)
End If
End If

' Open the recordset defined in the RowSource property.
Set DB = DBEngine.Workspaces(0).Databases(0)
Set RS = DB.OpenRecordset(C.RowSource, DB_OPEN_SNAPSHOT)

' Record and return the ID for this function.
DISPLAYID = Timer
AddAllToList = DISPLAYID

Case LB_OPEN
AddAllToList = DISPLAYID

Case LB_GETROWCOUNT
' Return the number of rows in the recordset.
RS.MoveLast
AddAllToList = RS.RecordCount + 1

Case LB_GETCOLUMNCOUNT
' Return the number of fields (columns) in the recordset.
AddAllToList = RS.Fields.Count

Case LB_GETCOLUMNWIDTH
AddAllToList = -1

Case LB_GETVALUE
' Are you requesting the first row?
If Row = 0 Then
' Should the column display "(All)"?
If Col = DISPLAYCOL - 1 Then
' If so, return "(All)."
AddAllToList = DISPLAYTEXT
Else
' Otherwise, return NULL.
AddAllToList = Null
End If
Else
' Grab the record and field for the specified row/column.
RS.MoveFirst
RS.Move Row - 1
AddAllToList = RS(Col)
End If
Case LB_END
DISPLAYID = 0
RS.Close
End Select

Bye_AddAllToList:
Exit Function

Err_AddAllToList:
Beep: MsgBox Error$, 16, "AddAllToList"
AddAllToList = False
Resume Bye_AddAllToList
End Function
orange999
see http://access.MVPs.org/access/forms/frm0043.htm to add ALL to rowsource
theDBguy
Hi, when you said the following:

"ProductCategoriesID criteria is SELECT ProductCategories.ID, ProductCategories.CategoryName FROM ProductCategories UNION SELECT Null, '(All)' FROM ProductCategories;

ProductID criteria is SELECT Products.ID, Products.ProductName FROM Products WHERE (((Products.ProductCategoryID)=[Forms]![PlaceOrder]![PlaceOrder_Subform].[Form]![ProductCategoriesID])
AND ((Products.Discontinued)=GetShowDiscont())) ORDER BY Products.ProductName;"

Did you actually mean to say "Row Source" instead of criteria?
dfishingski
yes my mistake they would be row source the criteria for ProductCategoriesID in the query
for the productID is [Forms]![PlaceOrder]![PlaceOrder_Subform].[Form]![ProductCategoriesID]
sorry was my mistake hope this helps
theDBguy
Okay, that's what I thought. Thanks for clarifying it. Try changing your criteria to something like this:

Oops, actually... it's not going to work yet. You need to change your combo row source first so that All will have an actual ID value rather than Null. For example, try using:

SELECT... UNION SELECT 0, '(All)'...

Then, try changing the criteria in your query to this:

[Forms]![PlaceOrder]![PlaceOrder_Subform].[Form]![ProductCategoriesID] Or [Forms]![PlaceOrder]![PlaceOrder_Subform].[Form]![ProductCategoriesID]=0

Hope that helps...
dfishingski
ok that works thanks a million I fought with this all day Have another question is there
a way with continuous forms that I could display a pic of the product for the current record
so I would just see a pic of the record where the curser is was thinking maybe it could be
in the header dependent on the current ProductID but would rather see it off to the side
like in another pop up form just wondering thanks
datAdrenaline
As and FYI, I typically encourage limiting the "All" portion of the UNION SQL statement simply because with a UNION, duplicate rows are automatically suppressed, so you don't get a ka-billion "(All)"'s in your list, but ... the db engine has to process the elimination of duplicates. Also, I encourage people to either use a bogus Table object that is guaranteed to have rows, because, if the table is empty that you are mapping the "(All)" label to, you won't get ANY choices (which may be ok, since if there are none, then you can't really get "All" of nothing .. but .. )

So .. I will often have a table of system configurations or application settings of sorts and use something like the following:

SELECT ProductCategories.ID, ProductCategories.CategoryName FROM ProductCategories UNION SELECT TOP 1 Null, '(All)' FROM SomeBogusTable;

Also, I typically keep the id value associated with "(All)" as Null because 0 may be a valid value. With that I adjust the query that may depend on the control ...

... WHERE ProductCategoriesID = [Forms]![PlaceOrder]![PlaceOrder_Subform].[Form]![ProductCategoriesID] Or [Forms]![PlaceOrder]![PlaceOrder_Subform].[Form]![ProductCategoriesID] Is Null
theDBguy
Hi. Brent brings up a good point. You might try it as well to see if it works any better for you. As for the images, if you have the path to the images stored in a field in your table, then you can add an Image control to your form and bind it to that field. Hope that helps...
dfishingski
So just so I understand how this works the part UNION SELECT TOP 1 Null, "(All)" FROM SomeBogusTable; that is sayin select the first record from some
Imaginary table or that Null and (All) are position number 1 in the combo box and and the end of the where statement Is Null is like the ProductCategoriesID.Value is Null when (All) Is selected in the combo box can you clarify this for me so I understand sorry but I'm really new to
This vb stuff and so does the way we get this to show all products really use that AddAllToList function I showed you in the beginning or is that just
Extra vb code I got stuffed in there and if it does use that function they say you can make it use something other than (All) and tell it what position
In the list you want it to be position being? Which column or how far down the list you want it not sure? thanks again sorry so many questions
dfishingski
Ok so I changed things to like Brent said I assumed I need to create a bogus table with at least 1 row so I created SomeBogusTable with one row
That has ID and CategoryName as fields It works except when you select (All) the Comobox brings up the right list but the (All) disappears in the other combo box how do I make it so you still see that you have (All) selected and Brent you talk about limiting the all portion of the Union statement is that
What I'm doing by doing it your way or is there something else I need to do to limit it thanks
dfishingski
Sorry again for so many questions thanks
theDBguy
Hi,

>> Sorry again for so many questions thanks <<
Don't worry about it. That's how we all learn.

>> ... but the (All) disappears in the other combo box... <<
I think that's why I recommended changing it to 0 instead of Null. I don't have a better suggestion to fix that. I hope Brent does.

Cheers!
dfishingski
Ok now I thought I could go ahead. And use 0 instead of null since I had SomeBogusTable
In place works ok for the combo boxes but on other controls when they do a form.recalc or refresh
Then it pops up asking me for a parameter value for the productcategoryID so what gives
it seems to work fine the original way not using a bogus table is there something I'm missing
dfishingski
Ok now that I think about it this kinda seems similar to when I set up
The show discontinued it would not pick up the control And I wound up
Creating a function GetShowDiscont I passed the Value of the ShowDiscontinued
Control to a variable and got it out to a public variable that the GetShowDiscont
Could get to and I set the criteria for Discontinued products to GetShowDiscont()
Just guessing but would think I would have to do much the same with the value
Of the combo box ? In order to get rid of the window popping up so that it
Has a value for the ProductCategoriesID.Value what you guys think
theDBguy
Hi. Not sure how soon before Brent is available but have you tried going back to the earlier solution that worked for you while you try to figure out how to make this one work?
dmhzx
I would do that completely differently (no surprise there then)

In the after update event of the combo.

If me.combo.column(? - whatever) = "(All)" then
me.filteron = false (or something similar)
else
do whatever you do now
endif

I hope I haven't misunderstood the requirement (again!)
dfishingski
So let me see if I understand since I'm very new to this
Me.combo.column would be Me.ProductCategories.2false and the
Next part(? - wharever) what is that actually saying? Then the
= "(All)" then Me.filteron = false I get that and the (or something similar)
Part are you just saying like could use no instead of false? Then
Else do rest of my code I would do in the after update event?
End if
Can you help clarify things for me and in my head if I turn the filter off
For the combo box for ProductID then it would not filter for the show
Discontinued products either and I would wind up seeing all products
Including Discontinued which is not what I want does this sound correct.
My bad if so I should have clarified "(All)" would be evert hinges except
Discontinued products and just to clarify this code would go in the
After update event of Product ID combo or the ProductCategoriesID?
I'm guessing in the Product ID combo thanks
dmhzx
Sorry, I put in a general guide as to how I would go about it, rather than try to give a complete solution.

Assuming that you do have the "{All}" in the combo box, and the combo is on the same form as the one you want to work with

"me" refers to the form itself.

So in the after update event of the combo,: You know which column of the combo has the "(All)". - Remember that in VBA the first column is column 0 (zero)

So I was suggesting that you can check the value of the combo.


If it is "(All)", I thought you wanted to turn the Form's filter Off (where the FORM can be addressed as "me"

If the combo is any other value, I thought that you already had working code for that.

Reading your last post, it looks like you still need a filter when "(All)" is selected - - not discontinued.

So yes, all the code goes in the combo after update.

me.filter refers to the filter on the form's recordset (even in the combo after update)

Is that any clearer?
dfishingski
Yes much more clear thanks so much
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.