Combobox

[Zach Ahmed]

Hi All:
I have a combo box on my form, and its style is 2-drowdown List. How can I set its newindex or index value to the record ID from my record set. This is what I am doing:

oRecData.Open "SELECT Location_Name,Location_ID FROM Locations", oDataConn
If oRecData.RecordCount > 0 Then
For i = 1 To oRecData.RecordCount
With cmboevebldg
.NewIndex = oRecData.Fields("Location_ID")
.AddItem oRecData.Fields("Location_Name")
End With
'combostate.AddItem RTrim(oRecState.Fields("state_Code"))
oRecData.MoveNext
Next i
End If

I get an error when I try to set the newindex. The error is "can not assign to read-only property" What do I need to do in order to assign a value to the index?

Thanks

[Ecniv]

oRecData.Open "SELECT Location_Name,Location_ID FROM Locations order by location_Id", oDataConn
 
if not oRecData.EOF then
    If oRecData.RecordCount > 0 Then
        For i = 1 To oRecData.RecordCount
            With cmboevebldg
                .AddItem
 
'---- Note: this is a two column combo-box with the first column width of 0
                .list(i-1,0)=oRecData.Fields("Location_ID")
                .list(i-1,1)=oRecData.Fields("Location_Name")
            End With
 
            oRecData.MoveNext
        Next
    End If
End If

Try that


Vince

[Zach Ahmed]

Thanks for getting back to me.
When I run your code, I get an error " Argument is not optiional."

This error occures at .Additem. If I was to comment that line out, I get different error. That error is Wrong number of arguments or invalid property assignment. What do I need to do next ? Also how do I define two columns combobox ?

Thanks

[Ecniv]

Hi...

Couple of questions:

1) Is the form a user form (excel) or an Access Form or a vb form
2) the control cmboevebldg - is this a standard combo box


The code I provided is from an Excel userform. It should also work in VB - BUT it assumes that you are using unbound forms.
In Access the combo list is different, and its easier to read it from a table, although possible to add from an array/code.

To set the combo to hold more than one column, you need to look at its properties window>Data tag/tab.
In here there is ColumnCount and ColumnWidths. The latter is the widths (0 for hidden) the first is how many columns. The details and setting can be done using .List method of the combo box.

In Access you set similar things, but under data you specify an sql and pull the data from a table. Quickest way. Not the best, but manual coding of list/combo boxs took me ages to figure out in Access VBA without linking to tables all the time. The latest version of Access prolly allows VB style combo boxes, so might be easier to fill.


As to missing properties error - select AddItem and press f1 (help) and read up on it. I don't know whats missing, works on the Excel Userform here.


Vince

[Zach Ahmed]

Hi:
It is a vb form and combobox is standard. The style if 2-dropdown list. The combobox is not bound, I am populating the combobox at run time with the data.

Thanks