PNeely
asked on
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.
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.
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
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
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(strPo
YourCombo.RowSource = strpopulate
Dont know if its this what you want
Álvaro
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops same as DAve
lol! Almost exactly the same! I just did all controls.
ASKER
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").Contr ols
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
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").Contr
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
glad we could help you PNeely!
Álvaro
Álvaro
same goes over here, from the land of Kangaroo's :-)
Dave
Dave
I had the same problem
Me.myCombo.RowSourceType = "Value List"
Me.myCombo.RowSource = "firstValue;secondValue;th
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