create dropdown values with vba

[44nato44]

Hi

I would like to create a dropdown and populate the values via vba code, I looked on the internet and found that I should use me.mydropdown.additem

My problem is that I do not get .additem in my list, am I doing anything wrong ?

Thanks

 

[44nato44]

I am using MS2000 by the way

 

[alvechurchdata]

I'm not sure why AddItem isn't appearing. What list of methods do you see?

Also, have you remembered to set the Row/Source type to Value List? Having a table as a source will cause an error at runtime

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[vbajock]

' add form names to Open Form combo box
Dim arrList() As String
Dim formsCount As Long
formsCount = CurrentProject.AllForms.Count
ReDim arrList(1 To formsCount)

' populate array with list of form names
With Me.cboFormToOpen 'the combo control
Dim frm As Access.AccessObject
Dim i As Long ' index counter
i = 1
For Each frm In CurrentProject.AllForms
arrList(i) = frm.Name
i = i + 1
Next frm

' sort array alphabetically using bubble sort
Dim j As Long
Dim strTemp As String
For i = 1 To formsCount
For j = i + 1 To formsCount
If arrList(i) > arrList(j) Then
strTemp = arrList(i)
arrList(i) = arrList(j)
arrList(j) = strTemp
End If
Next j
Next i

' add sorted array items to combo box
For i = 1 To formsCount
.AddItem arrList(i), i - 1
Next i

End With

 

[MajP]

As you already pointed out VBAjock's code will not work. AFAIK the additem method of the combobox and listbox did not come until A2003 (I might be wrong, but pretty certain it did not exist in A97).

So to do this prior you need to either use a callback function (not easy) or create a string seperated by semicolons and use as the rowsource. Ensure it is a value list.

Private Sub Form_Load()
  Dim strSource As String
  'some code to loop and create a semiColon seperated string
  'example
  Const dtmDate = #12/19/2010#
  Dim i As Integer
  For i = 0 To 6
    If strSource = "" Then
      strSource = Format(dtmDate + i, "dddd")
    Else
      strSource = strSource & ";" & Format(dtmDate + i, "dddd")
    End If
  Next i
    Me.cmbo1.RowSourceType = "value list"
    Me.cmbo1.RowSource = strSource
End Sub

Fills the combo with the days of the week.