How to set combo box default value to last row in list?

[Adam Tippelt]

I've got a combo box that runs a query and returns a list of items, based on a value in a different combo box.

The query runs perfectly and the combo box list is filled with the correct selection of rows. However I need the combo box value to default to the last value in the list. Is there a way to code this?

I'm guessing it needs to be based around something like me.combobox.val ue = me.combobox.col umn(index,row)

However the number of values returned by the query varies, so I cannot use a hardcoded row location for it to always use. The column need to remain in ascending order, so I cannot reverse the order and just use .column(0,0).

I've also tried calculating the rowcount, assigning it to a variable, and using the variable as the row location. This however did not work.

Has anyone got any suggestions as to how I could achieve what I'm after?

Thanks.

Adam.

[Stewart Ross]

You can call a custom VBA function to set the default value of the control to the value of the last row in the rowsource of the combo.

Place the following functions in a public code module (one which is visible from the modules tab of the database window). If you do not have a suitable module already created then create a new one at this point and give it any suitable name.

Function LastComboRow(TheCombo As Control) As Variant
    Dim LastRow As Integer
    LastRow = TheCombo.ListCount - 1
    LastComboRow = TheCombo.ItemData(LastRow)
End Function

Function FirstComboRow(TheCombo As Control) As Variant
    FirstComboRow = TheCombo.ItemData(0)
End Function

I include a function for returning the last combo row, and its simpler opposite number one for returning the first combo row for completeness.

Once you have the LastComboRow function saved in a public code module you can place the following call in the combo's Default Value property:

=LastComboRow([YourComboName])

where [YourComboName] is the name of the combo control on the form itself.

You may ask why you should place the functions in a public code module and not the form's private code module. The reason I suggest the public code module is that it is then global in scope and can be called to set defaults for controls on other forms as necessary.

-Stewart

[Adam Tippelt]

That didn't work. I think it's partly due to the code calculating the listcount before the list has been populated by the query, so it returns a null value.

I've had a slight rethink of what I was doing now, and have replaced the function with a DMax() line to return just the latest value.

Thanks for your help though.

[NeoPa]

I think you've hit on the solution Adam. To whit :

1. Determine the value of the last item in the list (if this is using DMax() in your scenario then fine).
2. Assign this value to the ComboBox control in your code.