Combo box lookup field and the MAX value 3

[Genimuse]

I have a subform in datasheet view. One of the fields is a combo box where the user selects a record from a table that is linked to this field as a Lookup. This works fine.

However, the list will grow over time, and I'd like the combo box to default to the record with the MAX id field, as the vast majority of the time that's the entry they'll want. How can I set the default?

It occurs to me when writing this that I could change the rowsource to be ordered by ID DESC, so at least that's the closest one available, but I'd rather it was the default. I don't know how to set the default value in a field to the results of a query... is that the way to handle it, or is there something I can do in the UI?

Thanks much in advance.

 

[Crowley16]

there is a default value property for a combobox, try putting a query to select the MAX id in there...

--------------------
Procrastinate Now!

 

[Genimuse]

Ah, the default value, of course!

I am struggling with making it work, though. My rowsource is:

SELECT [Batch].[ID], [Batch].[BatchDate], [Batch].[Comment] FROM Batch ORDER BY [Batch].[ID] DESC;

So I figured I could use this as my default value:

(SELECT MAX(Batch.ID) FROM Batch)

but no such luck. I thought perhaps that even though the contol is bound to column 1 (ID), it needed all 3 fields there, so I tried:

(SELECT Batch.ID, Batch.BatchDate, Batch.Comment FROM Batch WHERE Batch.ID = (SELECT MAX(Batch.ID) FROM Batch))

but again, it displays no default value.

Sorry for being so dense: what am I missing?

 

[PHV]

In the GotFocus event of the combo:
If Me![combo name].ListIndex = -1 Then
Me![combo name].ListIndex = Me![combo name].ListCount - 1
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

At the relevant events, you could try to assign the first or last value of the combo (I'm assuming you're needing this in an event where the combo doesn't have focus):

[tt]' last
me!cboMyCombo.value = me!cboMyCombo.colum(0, me!cboMyCombo.listcount-1)
' first
me!cboMyCombo.value = me!cboMyCombo.itemdata(0)[/tt]

Roy-Vidar

 

[Genimuse]

While it would be nice to have the value sitting there, having it appear when the user tabs in is ok, too.

Thanks!