Setting Combo Column Width via VBA

[LanaR]

I'm currently playing with some code to deal with plurals in a combo box.

So depending on a text box that accepts a numeric value, the combo will show either singular or plural depending on the value in the text box. The code is

    If Me.Quantity.Value > 1 Then
        Me.Combo0.ColumnWidths = "0 ;0 ;2.542 cm"
    Else
        Me.Combo0.ColumnWidths = "0 ;2.542 cm ;0 "
    End If

and is fired in the form's On Current event and the Quantity field's On Lost Focus event

This works fine when the combo drops down. However, once the combo looses focus, the data displayed reverts to the Column widths based on the data in the first record when the form initially loads. So if the first record is one or less the combo will always display the singular despite what is in the dropdown list and visa a versa if the first quantity is greater than one.

Is there another property, that I've missed, that needs to be set to overcome this?

 

[theDBguy]

Not sure I follow. Can you post some screenshot to clarify what you're seeing?

 

[LanaR]

Better than that, here's a copy of the DB

 

[June7]

Might want to change field name Ingrdeient to Ingredient.

Don't think I would bother with this. I would probably just have the units use (s): Ounce(s), etc. Okay, Leaf(s) is not grammatically correct but how important is this for data entry? I might do calculation in report to return singular/plural based on value.

I did a quick test in Current event with an UNBOUND listbox in form header and column widths did change per value of Quantity and different column displayed.

 

[LanaR]

@June7 Yes; I'm starting to agree. It was just something that I thought might be quite simple to implement. But it's starting to look as if the juice isn't worth the squeeze.

 

[CJ_London]

I would also argue 'gram' only applies when the quantity=1

to me' 0.5 gram' doesn't seem right - I would argue you would write '0.5 grams'

Not sure why the change of columns widths doesn't work as you expect , but suspect it might be something to do with retaining the setting on form open

Providing you are using a single form, you could just change the combo recordsource - change the column count to 2 and the columnwidths to 0cm (you can specify a width for the 2nd column if you want, but nor required)

Private Sub Quantity_LostFocus()
Const rSSql = "SELECT MeasureID, [] FROM TBL_Measures ORDER BY Measure"

    If Not IsNull(Me.Quantity) Then Combo0.RowSource = Replace(rSSql, "[]", IIf(Quantity > 1, "MeasurePlu", "Measure"))

End Sub

edit - no need to repeat the code for the current event, just put

Private Sub Form_Current()

    Quantity_LostFocus
    
End Sub