Access 2000 AddItem

[egghi]

Hi,

I developed a form in Access 2003 which has a combo box in it. The combo box displays all the years since 1900 until the current year. I created a function called PopulateCboYear and called the function when the form is loaded:

Private Sub PopulateCboYear()

Dim iYear As Integer

For iYear = 1900 To Year(Now())
cboYear.AddItem (iYear)

Next

End Sub

The function worked perfectly in Access 2002 and 2003, but not in 2000. I then realized that Access 2000 does not have the AddItem function and, therefore, added a module called AddItem according to some suggestions I found on the internet:

Sub ListAddItem(LB As ListBox, ByVal strItemToAdd As String)

LB.RowSource = IIf(LB.RowSource = "", strItemToAdd, LB.RowSource & ";" & strItemToAdd)

End Sub


I was hoping that by adding this module, the PopulateCboYear function would work, but it doesn't, and I can use some help from smart people like you!!

Thank you,
egghi

[salvelinus]

Without looking any farther, the first error is that you're trying to populate a combobox, but declaring it in your module as a listbox.

[egghi]

Hello Salvelinus,

Thank you for pointing that out! I changed the module to:

Sub CboAddItem(CB As ComboBox, ByVal strItemToAdd As String)

CB.RowSource = IIf(CB.RowSource = "", strItemToAdd, CB.RowSource & ";" & strItemToAdd)

End Sub


However, I am not sure what I need to do to edit the actual combo box, cboYear, and its property in order to populate the list of years correctly in the combo box... Any advice?

Thanks,
egghi

[salvelinus]

Typically, I might populate a combobox by setting Row Source Type to Table/Query and then setting Row Source to a saved query.
But, to do it your way, try something like this:

Private Sub Form1_Load()
   Dim s As String
   Dim i As Integer
   me.cboYear.RowSourceType = "Value List"
   For i = 1900 to Year(Now())
      If i = Year(Now()) Then   'this will prevent an empty record being added
         s = s & Cstr(i)
      Else
         s = s & Cstr(i) & ";"
      End If
   Next i
   me.cbo.RowSource = s
End Sub

[egghi]

Hi Savelinus,

Your code rocks!! Thank youuuuuu Have a very happy Friday!

egghi

[salvelinus]

You're welcome. Not sure it'll work in Access 2003 since I've never used it.

[egghi]

It works in Access 2003 as well Thank you for your help!!