can I display the first item of a combo box?

Question

Hello, I am developing a database where I have employees working at several locations. In a form used to input data records, I have a combo box where user chooses an employee, then a cascading combo box of the locations where this employee works.  While working at several stores is possible, many employees only work at one location. I would like the 2nd combo box to automatically display the first item found by the select statement, so the user doesn't have to click the drop list arrow and choose an item out of a list of one.  Make sense?  I have done many searches and tried many solutions and nothing works.  I have read about itemdata(0) and selectedindex, but I guess I don't know enough programming to understand where and how to apply this knowledge.  Please note, I am developing this database primarily using the Access 2016 interface - not through code.  I've been learning along the way, but never formally trained in VBA.  Thanks in advance for any help anyone can be to me.

Answer 1

Hi. If you were to use code, it might look something like this:

Me.ComboboxName = Me.ComboboxName.ItemData(0)

or

Me.ComboboxName = Me.ComboboxName.Itemdata(1)

depending on if you have the column heads turned on or not.

You would place the code after the cascade happened in the second combobox. Hope it helps...

Answer 2

Hi KareninMD,

The suggestion given by the DB guy selects the 1st item of the combobox.

As you have mentioned above that you are not much familiar with vba and you are using Access interface.

you also mentioned that you don't know where and how to apply this code.

so I tell you that you have to place this code in any event to execute.

you can press Alt + F11 key to open VBE.

when you add a control on a form then you can add a code to it. For that open your form in design view.

select the control and go to its properties. select "Event" Tab.

then you will see the list of events. select the event on which you want to write your code and there is a drop down in that you have to select [Event_Procedure] then click on the button beside that and you will see that VBE opens and you can see your event there. now you can place the code suggested by the DB guy in that event and try to run the Form.

if you think the suggestion provided by community members can help you to solve your issue please mark that suggestion as an Answer.

if you still facing problem regarding that let us know so that we can provide you further help.

Regards

Deepak   

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 3

Thank you Deepak for the assistance.  I am a little familiar with using the Even Procedure, that being said I still very much appreciate your detailed instructions.  But, I still am not getting the code to work.  When I go to the even tab, there are 18 event triggers listed.  Before I cycle all 18 of them, can you tell me which one I need to add the itemdata(0) code to? BTW, I have tried After Update, and both the (0) and (1) code.  Neither works.  Thanks again.

Answer 4

Hi. Like I said earlier, you must have some code to "cascade" the combobox, right? If so, you can place the code there.

Answer 5

The "cascading" of the 2nd box is in the select statement of the row source - the criteria of the select references the contents of the 1st box using the [Forms] group.  Then I have an embedded macro on the 1st box to requery the 2nd box so that if the user chooses a name, and then clicks the drop list arrow and chooses a different name, the 2nd combo box re-runs the select statement.  So, again, I ask you, where do I put the "Me.ComboboxName = Me.ComboboxName.ItemData(0)" code?  Above you stated that this code is to go on the 2nd combo box.  Then you said it goes in the cascading code, which is on the 1st box. 

Answer 6

And, be aware that I can't just add the code to the embedded macro.  I tried using the SetValue command, and that didn't work either.

Answer 7

Hi. Two-part answer: (1) Yes, you would put the code in the embedded macro attached to the first combobox, but (2) since it's a macro, you can't use what I gave you because it's VBA (I didn't know you were using macros earlier). Let me do some tests to see what the equivalent macro command will be. Otherwise, you can try to convert your macro to VBA, and you'll be able to add what I gave you. Cheers!

Answer 8

And, be aware that I can't just add the code to the embedded macro.  I tried using the SetValue command, and that didn't work either.

Hi. I just tried using SetValue, and it worked for me.

Answer 9

yeah yeah yeah! 

It worked!

I think the problem was that I wasn't using the brackets.

Thanks a MILLION!

Answer 10

Hi. You're welcome. Glad to hear you got it to work. Good luck with your project.