Adding 'All' to a combo box

[JasGun]

I have a combo box that is unbound. Its rowsource is based on a query that is based on a field in a table. I want to add 'All' as an option in the combo box. Can anyone help?

 

[AccessSQLUser]

One way to do this is to add a record to your table that contains the asterik. What I like to do is have my combo box based on a Union Select statement. For example:
SELECT [TasksList].[ID], [TasksList].[Task] FROM [TasksList] UNION Select "*","*" from [TasksList];
Then in the criteria for that field in the query, I put :
Like [form1]![combo1]

 

[Aivars]

Hi!
My experience show that better solution is creating some other form's object (check box, command button etc.) for this.
Aivars

 

[AccessSQLUser]

That's an idea. But then how would you refer to that in the criteria of the query?

 

[JasGun]

I tried the way AccessSQLUser suggested and it works fine. I also tried adding 'All' in my table, having a text box which wasn't visible. and as its Control source was
'=iif([combo]="All","*",[combo])', then in my query in the criteria was 'Forms!Form![HiddenTextbox],I thought this way was quite good?

Thanks for your Help

 

[Aivars]

Hi!

On the supposition that form recordsource="Select * from MyTable;"
In such case:

sub cboMyCombo_After_Update()
dim strSQL as string

strsql="Select * from MyTable where TableField = " & me.cboMyCombo & ";"
me.recordsource=strsql
end sub

sub cmdAll_On_Click() 'ie. cmdAll.caption="Show All"
dim strSQL as string

strsql=="Select * from MyTable;" 'ie. default form's recordset
me.recordsource=strsql
end sub

Aivars