Custom data for first row (index 0) for combo box

[ritco]

I have an unbound form with a combo box that lists item numbers. The row source for the combo box is a lookup query.
I started with using the following to at least get the box to always display the first item either on Form Load or on Got Focus (this works fine):

Me.cboItem = Me.cboItem.ItemData(0)

What I want to do is make the first row (index 0) display “Please choose an item…” with the rest of the items following.

I have done this in other languages but can’t figure it out for Access.
Does anyone know how to do this?

 

[pbaldy]

This type of thing should work:

http://access.mvps.org/access/forms/frm0043.htm

The trick is to make sure the ORDER BY clause puts that row first.

 

[ritco]

Thanks! I really apprecitate your time.
I checked out the link you provided and got it to work with this:

SELECT dbo_SCCurLotNumTestDB.StkCode, dbo_SCCurLotNumTestDB.LotNum, dbo_SCCurLotNumTestDB.DateChanged
FROM dbo_SCCurLotNumTestDB
UNION SELECT " Choose an item..." as firstItem, Null as Nothing1, Null as Nothing2
FROM dbo_SCCurLotNumTestDB
ORDER BY dbo_SCCurLotNumTestDB.StkCode;

It works but what I don't like about it is I had to put a space before the "C" in "Choose..." in order to make it come up first. This does not guarantee my text will always be first. If a user enters some bad data by entering a stock code item with a space for the first character (and I have seen this happen), this may no longer be my first item.
Kind of a messy way to do this.

In other languages like C# or ASP.net I'm able to Insert into a specific index. Something like:
objectName.Items.Insert(0, new ListItem("Choose an item...","0"));

This guarantees that my text will always be first.

Anything like this in Access VB?

 

[pbaldy]

How about

SELECT dbo_SCCurLotNumTestDB.StkCode, dbo_SCCurLotNumTestDB.LotNum, dbo_SCCurLotNumTestDB.DateChanged, 1 As SortField
FROM dbo_SCCurLotNumTestDB
UNION SELECT "Choose an item..." as firstItem, Null as Nothing1, Null as Nothing2, 0 As SortField
FROM dbo_SCCurLotNumTestDB
ORDER BY SortField, dbo_SCCurLotNumTestDB.StkCode;

 

[ritco]

Very clever!!! That worked beautifully! Thanks so much for your help!

 

[pbaldy]

No problemo!