How to set value of a combo box from code

[julietbrown]

I have a combo box, EventCombo, on a form.

The row source is "All events that haven't been 'put-to-bed'".
DCount works out how many rows there will be in the combo box.
The following code works a treat (not rocket science, anyway), apart from the action to be taken if dc = 1

        Dim dc As Integer
        dc = DCount("EventPutToBed", "EVENT", "EventPutToBed = False")
        If dc = 1 Then
            ' display the only row, so user doesn't need to pull down the list
        Else
            If dc = 0 Then
                MsgBox ("You need to set up an Event record first.")
                Exit Sub
            Else
                ' user will choose the relevant event from the list
            End If
        End If

I can't seem to program the line of code needed to replace the comment
' display the only row, so user doesn't need to pull down the list

My first thought was to write ...
Me.EventCombo.S elected(1) = True
... expecting this to display data from the first and only row in the list.

It didn't! All it did set the focus to the combo box (put the cursor in there).
Much further pointless messing about produced no solution.

Any ideas?

[TheSmileyCoder]

Remember that arrays are enumerated with the first item starting at 0.

Lets set the value of the combobox to the first item in itemdata

  Me.cmb_Test.Value = Me.cmb_Test.ItemData(0)

Another simpler way of gettting the amount of rows/lines from your combobox could be:

Me.cmb_Test.Recordset.RecordCount

[julietbrown]

I am SUCH a prat sometimes. I know perfectly well it should have been row 0, not row 1 .... the number of times I've checked in code that it ISN'T row -1!!!!

But your ItemData thing is terrific. Of course it works, knew it would as soon as I saw what you'd put. Thanks lots.

Re RecordCount ... the Help is so vague about when RecordCount will or will not correctly give the number of records that I've avoided it so far. Maybe I'll go have another peep at it.

You made me smile again!

[NeoPa]

As a general rule, Access (and here it's little different from most database systems) will put a limit on the number of rows it will populate automatically for a recordset. This is due to the unrestricted nature of the volumes of data that may be required. If you open up a form for instance, and it contains a ComboBox with three items, there is a pretty immediate availability of the form to the operator. On the other hand, if the ComboBox is populated with ten million records, there is likely to be such a delay that the form is barely usable (I know it seems impractical to have such a ComboBox ever, but there are circumstances where a ComboBox starts with a full set but can be filtered by other selections for instance). It is for situations such as these that Access limits the number of records returned on first opening the object. Opening the ComboBox will allow more records to be retrieved, so while there may be further delay, the records are all available if needed.

It is due to this behaviour, that the Help recommends going to the end of the recordset before relying on the returned count of the records. If this is not done, and the value is requested immediately after opening, then the value returned will simply reflect the limit imposed by Access for records retrieved on opening. Does that clarify the position somewhat?