dealing with all the entries in a combo box

[momani]

hii



I am using a combo box to show a specific records in the related sub_form , I would like to add a piece of code to the combo box so that it show an "all " option in the list that will show all the records in the sub_form .!!

are there any simple way to do that.

I am not familiar with VBA codes and I am using access 2003.


Best regards
Momani

[June7]

This is an unbound combobox used to input filter criteria? The RowSource is an SQL statement that pulls values from field in table? Try:

SELECT "_ALL" AS fieldname FROM tablename
UNION SELECT fieldname FROM tablename;

Then code will have to translate the "_ALL" choice into wildcard filter. Show the code you have for filtering the subform.

[momani]

hi again
the combo box is unbounded one and used as a filter craiteria in a query, also the SQL pulls the values from a table called tblcategory as follows :

SELECT tblCategory.categoryID, tblCategory.category FROM tblCategory ORDER BY [category];
I have add a record "All" to the table, and I have tried to implement the code given but it always gives an error, could you help me with more detalis about the SQL statment

regards
,

[June7]

Having a single record in tblCategory just so there is an "ALL" item in the RowSource won't accomplish anything because there is no data with a value of "ALL" to match the combobox parameter of "ALL".

Also, dynamic parameter in the query won't work. Need to use code (I use only VBA, not macros) to apply filter to the subform RecordSource.

Regardless of how you get "ALL" into the RowSource list (table record or the UNION query I suggested), need code to convert the "ALL" value. I didn't give you any code, what did you try and what is the error you get?

In the combobox AfterUpdate event VBA procedure, try something like:

Me.subformname.Form.FilterOn = False
Me.subformname.Form.Filter = "fieldname='" & IIf(Me.combobox="ALL", "*", Me.combobox) & "'"
Me.subformname.Form.FilterOn = True