Combo Box Column not populated (Run Time Error 424)

[fabio]

I need some help; I cannot see what I am doing wrong in the following code which is part of a Form; the code stop at line (Me.Combo0.Column(y, x) = strProj). I am trying to populate second column in a combo box with data from the recordset rsProjWBS. In Advance, Thank you for your help.

Private Sub Command11_Click()


Dim conn As ADODB.Connection
Dim strSql As String
Dim rsProjWBS As ADODB.Recordset
Dim strProj As String
Dim strProj1 As String
Dim x As Long
Dim y As Long


Set conn = CurrentProject.Connection

Set rsProjWBS = New ADODB.Recordset

strSql = "SELECT dbo_PROJECT.proj_short_name, dbo_PROJWBS.wbs_name, dbo_PROJECT.proj_id " & _
"FROM dbo_PROJECT INNER JOIN dbo_PROJWBS ON dbo_PROJECT.proj_id = dbo_PROJWBS.proj_id " & _
"WHERE (((dbo_PROJECT.project_flag)='Y') AND ((dbo_PROJWBS.proj_node_flag)='Y'))" & _
"ORDER BY dbo_PROJECT.proj_short_name;"

Set rsProjWBS = conn.Execute(strSql)
Me.Combo0.RowSource = ""
Me.Combo0.AddItem ("No Project Selected")


x = 0
y = 0

While Not rsProjWBS.EOF
Dim c As Integer
Dim v As Integer
Dim z As String
Dim gp As String
c = 0
gp = " -> "
z = 1
c = Len(rsProjWBS.Fields.Item(1).Value)
v = 20 - c
While z <= v
gp = gp & " "
z = z + 1
Wend
strProj = rsProjWBS.Fields.Item(0).Value
strProj1 = rsProjWBS.Fields.Item(1).Value
Debug.Print strProj
Debug.Print strProj1
rsProjWBS.MoveNext
Me.Combo0.ColumnCount = 2
Me.Combo0.ColumnWidths = "4 cm;4 cm"
Me.Combo0.AddItem ""
Me.Combo0.Column(y, x) = strProj
x = x + 1
Wend
End Sub

 

[George Moore]

To leave column one blank and just populate column two, try...

me.combo0.additem ";" & strProj & ";"

....and delete the line in red

 

[George Moore]

Sorry....may have misread your post, is Column One already populated and you are just trying to populate column2 ?

 

[Pat Hartman]

The .column property is not multi-dimensional. It is only a single dimension and refers to the columns selected by the rowSource. It does not address a particular row. Only one row is ever current so there is never a need to do this.

Modify the RowSource of the combo to include the additional column OR load the RowSource with VBA as in this example.

https://msdn.microsoft.com/en-us/library/dd758784(v=office.12).aspx?f=255&MSPPError=-2147217396

 

[fabio]

Sorry....may have misread your post, is Column One already populated and you are just trying to populate column2 ?

George: Correct, Column 1 is populated but column 2 is not. Thank you for your help. Fabio

 

[George Moore]

Hi Fabio

The .Column(x,y) property is read only so you can't set it

You could try the following....

Use a variable to read the value of .Column(1) at row number

Then remove row completely using the .removeitem method

Then re-add the row using .additem YourVariableName & ";" & StrProj & ";"

 

[arnelgp]

combos are actually FLAT:


Dim strValue As String

Me.Combo0.RowSource = ""
strValue ="0;No Project Selected" & ";"
Me.Combo0.ColumnCount = 2
Me.Combo0.ColumnWidths = "4 cm;4 cm"

While Not rsProjWBS.EOF
Dim c As Integer
Dim v As Integer
Dim z As String
Dim gp As String

c = 0
gp = " -> "
z = 1
c = Len(rsProjWBS.Fields.Item(1).Value)
v = 20 - c
While z <= v
gp = gp & " "
z = z + 1
Wend
strProj = rsProjWBS.Fields.Item(0).Value
strProj1 = rsProjWBS.Fields.Item(1).Value
Debug.Print strProj
Debug.Print strProj1
rsProjWBS.MoveNext
''Me.Combo0.AddItem ""
''Me.Combo0.Column(y, x) = strProj
strValue = strValue & x & ";" & strPoject & ";"
x = x + 1
Wend
Me.Combo0.RowSource=strValue
End Sub

 

[fabio]

SOLVED; Thank you for your answers.

----------
I have added the Row Source Type as "Table/Query" an also selected in the combo box properties the option "Table/Query", then the second column was populate.

----------

Private Sub Command11_Click()

Dim conn As ADODB.Connection
Dim strSql As String
Dim rsProjWBS As ADODB.Recordset
Dim strProj As String
Dim strProj1 As String

Set conn = CurrentProject.Connection
Set rsProjWBS = New ADODB.Recordset

Me.Combo0.RowSourceType = ""

strSql = "SELECT dbo_PROJECT.proj_short_name, dbo_PROJWBS.wbs_name, dbo_PROJECT.proj_id " & _
"FROM dbo_PROJECT INNER JOIN dbo_PROJWBS ON dbo_PROJECT.proj_id = dbo_PROJWBS.proj_id " & _
"WHERE (((dbo_PROJECT.project_flag)='Y') AND ((dbo_PROJWBS.proj_node_flag)='Y'))" & _
"ORDER BY dbo_PROJECT.proj_short_name;"

Set rsProjWBS = conn.Execute(strSql)


Me.Combo0.RowSourceType = "Table/Query"
Me.Combo0.RowSource = strSql
Me.Combo0 = Me.Combo0.ItemData(0)
Me.Combo0.Requery

While Not rsProjWBS.EOF
Dim c As Integer
Dim v As Integer
Dim z As String
Dim gp As String
c = 0
gp = " -> "
z = 1

c = Len(rsProjWBS.Fields.Item(1).Value)
v = 20 - c

While z <= v
gp = gp & " "
z = z + 1
Wend

strProj = rsProjWBS.Fields.Item(0).Value
strProj1 = rsProjWBS.Fields.Item(1).Value

Debug.Print strProj
Debug.Print strProj1

rsProjWBS.MoveNext

Me.Combo0.ColumnCount = 2
Me.Combo0.ColumnWidths = "4 cm;4 cm,"

Wend


rsProjWBS.Close
Set rsProjWBS = Nothing
conn.Close
Set conn = Nothing

End Sub