Adding All to combo

[scott]

I have below code on a form that allows report filters. I'm trying to add
"All" to a combo filled with a recordset of years. The "All" gets added, but
instead of showing my years below "All", Access shows the SELECT rowsource
below.

Is there another way to add "All" to a rowsource SELECT statement?

CODE *********

Private Sub Form_Open(Cancel As Integer)
With Me.cmbYear
.RowSourceType = "Value List"
.RowSource = "(All);" & .RowSource
End With

End Sub

SQL RowSource of combo box ***************

SELECT taxYear FROM t_taxYearDefaults ORDER BY taxYear

 

[Douglas J. Steele]

Private Sub Form_Open(Cancel As Integer)
With Me.cmbYear
.RowSourceType = "Table/Query"
.RowSource = "SELECT taxYear FROM t_taxYearDefaults " & _
"UNION SELECT '(All)' FROM t_taxYearDefaults ORDER BY taxYear"
End With

 

[scott]

i realized i've got to add the "All" to my sql statement, but 'm having
trouble. I tried the below CODE 1, but get error because "(All)" isn't a
value in the taxYearDefaults table. How can I modify

SELECT taxYear AS taxValue, taxYear FROM t_taxYearDefaults

so it contains a record with a null value and the word "All"?

CODE 1 **********

SELECT taxYear AS taxValue, taxYear FROM t_taxYearDefaults UNION Select Null
as AllChoice , "(All)" as Bogus From t_taxYearDefaults

 

[scott]

I used your code and get below error. I tried this with the taxYear field
being integer and varchar(6), but both data types give error. Any
suggestions?


Error: The value you entered isn't valid for this field.
For example, you may have entered text in a numeric field ...

 

[scott]

Since I'm striking out on all other methods, I created the below SROC whic
UNION's my All and a null value. My thinking was to base my combo on the
SPROC. The toolbox wizard won't allow this since there's no recordset until
my SPROC runs.

Is it possible to create a combo based on such a SPROC? If not, any other
ideas?

Also, if I add "All" to a combo, I will need a value field with NULL to math
the "All" so I can use that selection as criteria for the report. At this
point, I'm open to any ideas.

SPROC *******

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[mlc_COMPANY_GetYearsFilter]

/*

Returns Union of tax years and All

used in company db tax report

*/

AS

BEGIN

SET NOCOUNT ON

IF object_id('tempdb..#tmpYears') IS NOT NULL

BEGIN

DROP TABLE #tmpYears

END

CREATE TABLE #tmpYears (

taxValue varChar(6) NULL,

taxYear varChar(6) NOT NULL

)

SET NOCOUNT ON

INSERT INTO #tmpYears(taxValue, taxYear) VALUES

(NULL, 'All')

END

SELECT * FROM #tmpYears

UNION ALL

SELECT taxYear AS taxValue, taxYear FROM t_taxYearDefaults

 

[Douglas J. Steele]

Good point. I wasn't thinking...

With Me.cmbYear
.RowSourceType = "Table/Query"
.RowSource = "SELECT Format(taxYear, '####') FROM t_taxYearDefaults " & _
"UNION SELECT '(All)' FROM t_taxYearDefaults ORDER BY taxYear"
End With

Sorry about that.

 

[Marcin]

I have below code on a form that allows report filters. I'm trying to add
"All" to a combo filled with a recordset of years. The "All" gets added, but
instead of showing my years below "All", Access shows the SELECT rowsource
below.

Is there another way to add "All" to a rowsource SELECT statement?

CODE *********

Private Sub Form_Open(Cancel As Integer)
With Me.cmbYear
.RowSourceType = "Value List"
.RowSource = "(All);" & .RowSource
End With

End Sub

SQL RowSource of combo box ***************

SELECT taxYear FROM t_taxYearDefaults ORDER BY taxYear

============================================================================
================
FULL LEGAL SOFTWARE !!!
Games, video, program, image, chat, questbook, catalog site, arts, news,
and...
This site it is full register and legal software !!!
Please download and you must register software !!!

PLEASE REGISTER SOFTWARE:
http://www.webteam.gsi.pl/rejestracja.htm
DOWNLOAD LEGAL SOFTWARE:
http://www.webteam.gsi.pl

Full question and post: http://www.webteam.gsi.pl

Contact and service and advanced technology:
http://www.webteam.gsi.pl/kontakt.htm
FAQ: http://www.webteam.gsi.pl/naj_czesciej_zadawane_pytania.htm

Please add me URL for you all site and search engines and best friends !!!

Me site:
SERWIS WEBNETI: http://www.webneti.gsi.pl
PORTAL WEBTEAM: http://www.webteam.gsi.pl
LANGUAGE: http://www.webneti.cjb.net
============================================================================
================