Combo Box

[hfrupn]

I would like to set up a combo box that has a lookup list of years to select
from. The list is needs to be dynamic from the current year and then two
years in the past to five years into the future.
So the list would look like;
2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013
Any help appreciated.
Regards
Nick

 

[Jon Lewis]

Try this in the Open event of your Form:

Dim I As Integer
Dim strRowSource As String
Dim mydate As Date
mydate = Date
mydate = DateAdd("yyyy", -2, mydate)
I = 1
strRowSource = CStr(Format(mydate, "YYYY"))
Do Until I = 8
I = I + 1
mydate = DateAdd("yyyy", 1, mydate)
strRowSource = strRowSource & ";" & CStr(Format(mydate, "YYYY"))
Loop
Me.YourCombo.RowSource = strRowSource

HTH
Jon

 

[Jon Lewis]

Forgot to mention that the Row Source Type of your combo needs to be set to
Value List.

 

[Biz Enhancer]

Hi Nick

Try setting the rowsource to ....

Select Year(Date())-2 From table
Union Select Year(Date())-1 From table
Union Select Year(Date()) From table
Union Select Year(Date())+1 From table
Union Select Year(Date())+2 From table
Union Select Year(Date())+3 From table
Union Select Year(Date())+4 From table
Union Select Year(Date())+5 From table;

Replace 'table' with any local table name.

It's not elegant but it works.

Regards,

Nick

 

[Douglas J. Steele]

If you want elegant, add this function to your application, and set the
combo box's RowSourceType property to ListYears (no equal sign nor
parentheses)

Function ListYears( _
fld As Control, _
id As Variant, _
row As Variant, _
col As Variant, _
code As Variant _
) As Variant

Select Case code
Case acLBInitialize
ListYears = True
Case acLBOpen
ListYears = Timer
Case acLBGetRowCount
ListYears = 8
Case acLBGetColumnCount
ListYears = 1
Case acLBGetColumnWidth
ListYears = -1
Case acLBGetValue
ListYears = Year(Date) - (2 - row)
End Select

End Function

 

[Al Campagna]

Nick,
A very interesting question.
I'll opt for simplicity...
A table called tblYears with just one field... Years (Num-Integer), and
populated with...
2006
2007
........ to as many years as you need...
2040

RowSource for your combo...
SELECT tblYears.Year
FROM tblYears
WHERE (((tblYears.Year) Between Year(Date())-2 And Year(Date())+5));

That should fly.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Guest]

SEÑORES(PORFI;PUEDO OBTENER BUESTRA AYUDA EN MI IDIOMA, MIL GRACIAS)

 

[hfrupn]

Thanks Jon and all for your help. problem solved.

Regards
Nick