Capture 2nd Value in an unbound Combo.

[sesproul]

Hello,

I have an unbound Combo (textbox) that has the following Rowsource Set

SELECT PartCatPREFIX, PartCatDescription FROM PartCategory;

On the change event, I filter the Form based on the value.

'---This is the Part Filter

    If txtFilter = "" Then Exit Sub
    sStar = "*"
    sFilter = "[PartNo] like " & Chr(34) & sStar & txtFilter & sStar & Chr(34)
    Me.Filter = sFilter
    Me.FilterOn = True
    
    cmdAddNewPart.Enabled = True
    
    txtFilterView = ?????????? ' Can I call the second value directly

the last line is where I would like to set a textbox txtFilterviewon the form to show the current filter selection to the PartCatDescription field.

I could requery for the value in the textbox, but I was wondering if it is possible to call the second field in the rowsource directly?


thanks

Steve

[TheShabz]

in the properties of your combobox, let the Bound Column under the Data tab to 1 (the first one is column 0).

Now the value of your second column will show on your me.cmbbox

[sesproul]

I believe that this is a 1 based control. So you mean use the column 2 value.

I understand that, But I use the Bound column (1) value in the filter. So that wouldn't work for this situation. Is there a property of the control that I may access. i.e. txtFilter.Rowsource.fields(2) ???

steve

[TheShabz]

if you have bound column (1) that should be your second column. As I said, it starts with 0 as the first column. if you have that setup you can use a query instead of a filter

strSQL = "SELECT whatever FROM wherever WHERE mytable.PartNo Like (*" & me.txtbox & "*)"

[sesproul]

The Form is a bound form. So That is why I use the filter function. Is your example if I set the form recordsource to the Query results?

[TheShabz]

yes. I assume you have a subform displaying your results. You would run that query and then Me.Requery and Me.Refresh to update the subform.