By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

How to set value of a combo box from code

P: 99
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

Expand|Select|Wrap|Line Numbers
  1.         Dim dc As Integer
  2.         dc = DCount("EventPutToBed", "EVENT", "EventPutToBed = False")
  3.         If dc = 1 Then
  4.             ' display the only row, so user doesn't need to pull down the list
  5.         Else
  6.             If dc = 0 Then
  7.                 MsgBox ("You need to set up an Event record first.")
  8.                 Exit Sub
  9.             Else
  10.                 ' user will choose the relevant event from the list
  11.             End If
  12.         End If
  13.  
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.Selected(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?
Feb 25 '10 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1.   Me.cmb_Test.Value = Me.cmb_Test.ItemData(0)
Another simpler way of gettting the amount of rows/lines from your combobox could be:
Expand|Select|Wrap|Line Numbers
  1. Me.cmb_Test.Recordset.RecordCount

Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Expand|Select|Wrap|Line Numbers
  1.   Me.cmb_Test.Value = Me.cmb_Test.ItemData(0)
Another simpler way of gettting the amount of rows/lines from your combobox could be:
Expand|Select|Wrap|Line Numbers
  1. Me.cmb_Test.Recordset.RecordCount
Feb 25 '10 #2

P: 99
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!
Feb 25 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
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?
Feb 25 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.