Want Combo Box first item if only 1 item

markw707 · June 9th, 2004, 09:48 AM

Assume an Access form with two combo boxes, ComboBox2 is based on a query. When the user makes a choice in ComboBox1, the choices in ComboBox2 are updated by ComboBox1's After Update Event Procedure of: Me.ComboBox2.Requery.

If, upon requery, ComboBox2 only has one item, I want ComboBox2 to show that item so the user doesn't have to click its drop down arrow and choose the item.

Thank you for your help!

Mark J. Weisberger

Ray Pinnegar · June 9th, 2004, 11:46 AM

In the AfterUpdate event code try a variation of this:

strSQL = "SELECT criteria " similar to the criteria used in the rowsource in combo2
set rs = currentdb.openrecordset(strSQL)
rs.movelast
If rs.recordcount = 1 then
Me.combo2 = "the matching field"
else
Me.combo2 = null
end if
Me.combo2.requery

Cheers Ray

SerranoG · June 9th, 2004, 12:42 PM

Simple enough... after the second combobox requeries, add this to its AfterUpdate event:

Code:

If Me.ComboBox2.ListCount = 1 Then
   Me.ComboBox2 = Me.ComboBox2.Column(0,0)
End If

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

markw707 · June 9th, 2004, 04:03 PM

Thank you Ray and Greg for your help. I used Greg's solution because it was more concise. Thanks again.

Mark J. Weisberger