Combo Box - Update a Column (1 Viewer)

[MLUCKHAM]

I want to change a column from a combo box in VBA Code. Is there a quick way of doing this or have I got to remove the row and add it back again?

I can access a column from a row that is selected via: -

 List0.Column(1, List0.ListIndex).Value

However, this is readonly...

 

[arnelgp]

Is it a value list combo?

 

[MLUCKHAM]

It is a List Box

 

[arnelgp]

What i mean is the rowsource type: table/query or value. If table/query it would use update query.

 

[MLUCKHAM]

It is a value list...

 

[The_Doc_Man]

Normally you would only do this from the manual design mode view of the form. It is possible but very tedious to programmatically change this. You have to essentially grab the list (which IIRC is a semi-colon delimited string), edit the string, and then reassert the property.

The format of that list is something like: "first choice",first value; "second choice", second value; "third choice",third value; etc. etc. You CAN edit the string because it is just a string. BUT if you get the commas and semicolons wrong, that combo box will be useless.

Now, you might not wish to do this, but sometimes if your goal is dynamic changes for this kind of the control, you would create a small lookup table and populate the combo box from that table. THEN it is a matter of an SQL UPDATE statement followed by a simple control.Requery operation and you are done.

 

[arnelgp]

its better to give you a sample.
View the code in the click event of each command botton.

 

[MLUCKHAM]

Yep, thanks guys, just thought there may have been a way of updating the column direct. I opted for the following code: -

Private Sub Command6_Click()
Dim n As Long, newRowSource As String
Dim c As Long

    n = List0.ListIndex
    c = 1
    
    newRowSource = ""
    For i = 0 To List0.ListCount - 1
        If i <> n Then
            newRowSource = newRowSource & "Column " & c & ";" & List0.Column(1, i) & ";"
            c = c + 1
        End If
            
    Next i
    
    List0.RowSource = newRowSource
    List0.Requery


End Sub

Not practical to use a table for this control as the data is completely transient and only displayed for user interface ease. Anyway, good to discuss these things...