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.
PNeelyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.