• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

Populate an unbound combo box using VBA when form loads

Don't laugh, but can someone please tell me how to populate a combo box using VBA? I've checked this site for the answer, but no joy.

It's an unbound control on a form with a subform. The list values should be added when the form is loaded, and they should come from the Tag property of each textbox control in the subform.

I'm familiar with the AddItem method used in combo boxes in other Office programs, but Access doesn't seem to support this method.

Thanks very much.
0
PNeely
Asked:
PNeely
  • 5
  • 4
2 Solutions
 
flavoCommented:
Hi PNeely,

I had the same problem

Me.myCombo.RowSourceType = "Value List"
Me.myCombo.RowSource = "firstValue;secondValue;thirdValue"

This is limited, to i think 65k characters (for the RowSource property) If you need more you'll need to set it to a table / query like

Me.myCombo.RowSourceType = "Table/Query"
Me.myCombo.RowSource = "tblMyTableOrQueryName"


Idea???

Dave
0
 
arcrossCommented:
I suppose you are using at max access 2000 ig it doenst support AddItem method.

Set the RowSource Type of the proeprty of the combo to 'Value List'.

Then in the form Load event put:

dim ctl as control

For Each ctl In Me
        If ctl.ControlType = acTextBox Then
            MsgBox ctl.Name
        End If
     Next
0
 
arcrossCommented:

I suppose you are using at max access 2000 ig it doenst support AddItem method.

Set the RowSource Type of the proeprty of the combo to 'Value List'.

Then in the form Load event put:

dim ctl as control
dim strPopulate as string

For Each ctl In Me
        If ctl.ControlType = acTextBox Then
              strPopulate = strPopulate & ";"
        End If
     Next

        ' Now trim last ;
        strPopulate = left(strPopulate,len(strPopulate)-1)

      YourCombo.RowSource = strpopulate

Dont know if its this what you want
Álvaro
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
flavoCommented:
oh.. didnt see this bit about Tag


Private Sub Form_Load()
Dim s As String
Dim ctrl As Control

For Each ctrl In Me.mySubFormControlName.Form.Controls
    s = s & ctrl.Tag & ";"
Next
'trim off last ;
s = Left(s, Len(s) - 1)
Me.Combo1.RowSourceType = "Value List"
Me.Combo1.RowSource = s
End Sub
0
 
arcrossCommented:
Sorry, you said u were using TAG property,

 dim ctl as control
dim strPopulate as string

For Each ctl In Me
        If ctl.ControlType = acTextBox Then
              strPopulate = strPopulate &  ctl.Tag & ";"
        End If
     Next

        ' Now trim last ;
        strPopulate = left(strPopulate,len(strPopulate)-1)

      YourCombo.RowSource = strpopulate

This is for TAGs in textboxes

0
 
arcrossCommented:
oops same as DAve
0
 
flavoCommented:
lol!  Almost exactly the same!  I just did all controls.
0
 
PNeelyAuthor Commented:
Wow! That was a quick response! Both of you were obviously on the same wavelength....

Thanks very much. It worked perfectly. Although it isn't much, I'll split the points between you.

Here's the code I used:

Private Sub Form_Load()
    Dim cbo As ComboBox
    Dim ctl As Control
    Dim strPopulate As String
   
    Set cbo = Me.Controls("cboStage")
   
    'Find tag values in subform controls
    For Each ctl In Me.Form("fsubMatch").Controls
        With ctl
       
            'Only check textbox controls that aren't blank _
            and ignore duplicate tag values
            If .ControlType = acTextBox And _
                .Tag <> vbNullString And _
                InStr(strPopulate, .Tag) = 0 Then
               
                strPopulate = strPopulate & .Tag & ";"
            End If
        End With
    Next ctl
   
    'Trim last ;
    strPopulate = Left(strPopulate, Len(strPopulate) - 1)
   
    'Populate combo box
    With cbo
        .RowSourceType = "Value List"
        .RowSource = strPopulate
    End With
   
    Set cbo = Nothing
    Set ctl = Nothing
End Sub

0
 
arcrossCommented:
glad we could help you PNeely!

Álvaro
0
 
flavoCommented:
same goes over here, from the land of Kangaroo's :-)

Dave



0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now