Need combo box to sort subform dates by year

[wordbrew]

I'm nearly done with a database but there is one more thing I would like to add.

I would like to add add a combo box, let's say I call it [cboYear], to my form [frmRevisionAudi t], where the contents of the combo box are populated with the current year 2010, but also includes, say 5 years prior, and 5 years into the future. And when the current year becomes 2011, I would like it to auto calculated based on that, etc... (I know this will probably have to be done via VBA, but I'm still young to VBA so I just can't get my head around it yet).

Once this combo is in place, when I select the year I want, I would like it to filter my subform [frmRevisionAudi tSubform] date down to only the year selected. It would also be nice if there was a blank or "All" option to select, that would simply show everything. The date field on my subform is simply called [Date]. The date field is of course in a 1/1/10 format, so that is also why I'm having trouble seeing how to select, say 2010 from a dropdown, and have it only pull the year part out of the date field to filter from.

I have a bunch of other filters on my main form filtering my subform via a query [qryRevisionAudi t].

I hope I've included everything I needed to mention. I really appreciate any help at all.

[missinglinq]

I only have time to address the isue of populating your combobox. Hopefully someone else will come along and help you with the rest.

First off, you absolutely have to change the name of your field to something other than Date. This is a reserved word in Access VBA and having a field/control named it will cause you all kinds of problems.

As to the Combobox, start by using the Wizard to place one on your form.

When the Wizard asks if you want to use dtata from a table/query or type in your own, hit Cancel.

Now place this code in the VBA code window:

Private Sub Form_Load()
Dim CurrentYear As Integer
Dim i As Integer
 
 CurrentYear = Format(Date, "yyyy")
 
Me.cboYear.RowSourceType = "Value List"

Me.cboYear.AddItem "All"

 i = 5
 For y = 1 To 5
  Me.cboYear.AddItem CurrentYear - i
 i = i - 1
 Next y

i = 0
 For y = 1 To 6
  Me.cboYear.AddItem CurrentYear + i
 i = i + 1
 Next y

End Sub

This will give you the years you want, based on the current year, with <All> at the top of the dropdown.

Welcome to Bytes!

Linq ;0)>

[wordbrew]

Missinglinq,

Thank you so much. And you're absolutely right. I'm still trying to habituate the brain pan to remember the reserved words and avoid using them. So I renamed it to fldDate.

As to the rest, I put in the combo and inserted the code. When I try out the form, it gives me a runtime error 2465, and the debugger highlights - CurrentYear = Format(Date, "yyyy")

I appreciate all your help.

[wordbrew]

Missinglinq,

Just wanted to shoot a note back to you to let you know I opened up a pristine database and created a form and combo box to test your code, and it worked perfectly. So obviously I messed something up by using the reserved word "Date" as one of my table fields. I went back and renamed the field fldDate, but i'm still seeing the run-time error saying access cannot find my field Date, and it tries to debug CurrentYear = Format(Date, "yyyy"). Am I just missing some bit of code somewhere I still have named Date, or is it that once I use a reserved word, it messes up the code permenently? I hope not.

Seems like everytime I learn a ton of information I have a ton more to learn LOL. As always, trial by fire. :)