Combobox.dropdown not working

[John Sh]

This seems to be one of the most common questions on this, and other, forums.
I have searched for many hours looking for a fix and might have found it here. https://learn.microsoft.com/en-us/office/vba/api/access.combobox.dropdown.
Traditionally the call is "me.combobox.dropdown" and this works most of the time.
On those occasions it doesn't work try replacing the "Me." with "Me!".
I have used this on a form that refused to dropdown with "me." and it now works every time.

I have received lots of help from many people on this forum so hopefully I can now be, in some small way, the helper.

 

[561414]

Hi, John.
Thanks for the tip.
However, the dropdown method requires you to set focus to the combo box before calling it. Using dot notation and bang notation should not be important for this.

However, you should not forget that when a field in your table has the same name as a control in your form, things are not so straight forward. For example, let's say your table has a column/field that you named "city". If you let Access add the control for you via an automatic form or by dragging from the "Add existing fields" pane, the control will have the field name as control name. So when you reference Me.city, Access will evaluate it as Me.Controls.Item("city").Value. If you then give that control another name, like "ctl_city", if you use Me.city again, Access will evaluate that as Me.Recordset.Fields.Item("city").Value. That is, it will evaluate a control first, then an Access field. Run the test yourself.

As a tip, avoid using Me! and bangs in general. You type less code but it requires you to know what the default member is, you can not use a variable after the bang and you don't have intellisense with it.

Just to reiterate:
Me is a Form object.
The default member of a Form object is the Controls collection.
When we use:
Me.SomeComboBox, which is the same as Me!SomeComboBox
We are doing this under the hood:
Me.Controls.Item("SomeComboBox").Value, which is the same as Me.Controls!SomeComboBox and also Me.Controls("SomeComboBox"). Did you see what the bang is doing? it's simply a shorthand of the default member. Form's default member is Controls. Controls' default member is Item. And, by the way, Item's default member is Value (in this context).

Now, if some field in your table happens to be "SomeComboBox", and there is no control with that name, then Access will evaluate Me.SomeComboBox or Me!SomeComboBox as this Me.RecordSet.Fields.Item("SomeComboBox").Value

So always check what you are trying to reference, you might be trying to dropdown an AccessField or the ComboBox did not have focus. Notice that the example in the documentation you linked to is using the GotFocus event. In the end, a focus is necessary.

 

[John Sh]

Hi 561414
All noted.
I always prefix comboboxes with cbo and text boxes with txt so there can be no clash of names.
You say to avoid using the bang but there are many occasions where it is required, EG [forms]![formname]![control] to name one.
I wouldn't normally use it but in this case it seems to fix a problem that rears it's ugly head from time to time.
My form in question was working fine with me. until I altered some code and things went whacko.
That code is still in the form and working as expected. Running a debug found no errors but that really doesn't indicate there are no problems in the code. I think debug mainly checks for syntax errors and missing variables / controls.
I have a feeling that sometimes Access does things that are not logically explainable so you have to work around them.

 

[cheekybuddha]

You say to avoid using the bang but there are many occasions where it is required, EG [forms]![formname]![control] to name one.

This is only necessary if passed via the expression service (eg in a ControlSource expression or in a query parameter reference).

It is not true in VBA - you can also (and more easily) use:
Forms.formname.controlname

 

[John Sh]

This is only necessary if passed via the expression service (eg in a ControlSource expression or in a query parameter reference).

It is not true in VBA - you can also (and more easily) use:
Forms.formname.controlname

I guess I've only ever used it in query criteria.

 

[MajP]

The definitive explanation of Bang and Dot

Lets Kill the Me. vs Me! sticky or lets rewrite it

This discussion moved to clean thread https://www.access-programmers.co.uk/forums/threads/bang-vs-dot-me-and-default-properties.327749/

www.access-programmers.co.uk

 

[561414]

This is only necessary if passed via the expression service (eg in a ControlSource expression or in a query parameter reference).

It is not true in VBA - you can also (and more easily) use:
Forms.formname.controlname

Dot notation can be used in expressions.

It would be nice to know if your version does not allow it, but in 2016 it does let me access all form properties that I tested using dot notation.

To all readers:
Please download the attached database file and check if it works in your Access version. If it does not, please inform.

 

[MajP]

So let me understand. You want to write

=[Forms].[Item]("frm_ResizeMe").[Form].[Properties].[Item]("WindowWidth").[Value]

Instead of

=[WindowWidth]

Sure, I guess.

 

[561414]

So let me understand. You want to write

=[Forms].[Item]("frm_ResizeMe").[Form].[Properties].[Item]("WindowWidth").[Value]

Instead of

=[WindowWidth]

Sure, I guess.

Yes, I absolutely want you to write any dot notation there and validate that the bang is not mandatory there.

=[WindowWidth]

And this is not dot notation.

 

[MajP]

I guess you can overkill it any way you want. To prove that you can still be a lot simpler

=[Forms]("frm_ResizeMe").[WindowWidth]

Maybe I am not getting your academic exercise. Which is DOT notation.

 

[561414]

I guess you can overkill it any way you want. To prove that you can still be a lot simpler

=[Forms]("frm_ResizeMe").[WindowWidth]

Maybe I am not getting your academic exercise. Which is DOT notation.

I am sorry that I wasn't clear enough that I want to demonstrate that dot notation can be used to avoid the bangs.

The added point I am now investigating is why some properties can be accessed and others not. For instance:
this works Application.CurrentObjectType
this works Application.Name
this errors Application.ADOConnectString
All are just random properties I get in my form object.

But that is beyond this topic.

 

[MajP]

I still do not get the exercise.
Using DOT in VBA has lots of advantages. Compiles at design time and provides intellisense.
Using DOT in expression has no advantages and Bang has more advantages in expressions. Dot likely requires more complex notation and now you have to remember what dot can be done in a control's expression and what can be done in a query expression. You may be able to use dot in your control, but you are limited to what can be done in SQL. I have never spent a lot of time trying to type a control reference in a query using dot, but I do not think you can even if explicitly using all default properties.
Sure you can use dot in a control expression, but still have to revert to bang for queries.

 

[561414]

I still do not get the exercise.

@MajP Allow me to clarify. In post #4, @cheekybuddha mentioned this:

This is only necessary if passed via the expression service (eg in a ControlSource expression or in a query parameter reference).

Which I interpreted as "bangs must be used in expressions".

So I replied with a test database that uses dots, instead of bangs in expressions. I do not have all versions of Access, so I can not know if what I'm saying is valid in all versions, but it is valid in 2016. Am I explaining myself? English is my second language, maybe I'm not being clear enough.

As for the advantages of each method, I understand what you mean, you get intellisense with the bang in the context of an expression box. To me, even though I understand the bang enough to explain it in simple terms, it would be best to advice against its usage until you know about the default members of any class. The usage of the bang is the source of a lot of misunderstanding in referencing stuff within the app when you're learning, whereas the long dot notation is reliable, constant, a sure-fire, it never misses, and, most importantly, it helps understand what's going on under the hood.

Sure you can use dot in a control expression, but still have to revert to bang for queries.

I'll give it a check, let me confirm this.

 

[561414]

Very well, I just checked a bunch of variations and the result of my testing indicates that You can avoid bangs in queries too.

This is the syntax: Forms.MyFormName.Form.MyControlName
If ControlName is in a subform, same applies: Forms.MyFormName.Form.MySubFormControlName.Form.MyControlName

_____________________________
It took me a while because I found a bunch of bugs in query expressions! Yup, bugs.
Trying to access a control type returns a character, not just that property though.
Forms.MyFormName.Form.MySubFormControlName.Form.MyControlName.ControlType

Trying to get the counts returns a symbol character:
Forms.MyFormName.Form.Controls.Count
Forms.MyFormName.Form.Count

This works for some reason:
Forms.Application.Name

The .Item syntax does not work in queries Forms.Item("MyFormName").Form.Name

I tried a lot of things that I won't list here. This referencing this is buggy. No wonder there has been so much confusion. Form expressions do allow you to access a lot of properties, not all, but most of them. Query expressions work well for referencing forms and controls, but some properties do return a result, albeit a rather unexpected one, so that qualifies as a bug. I'm testing in a 2016 environment, so it would be good if others can confirm in a more recent version if these issues persisted.

 

[John Sh]

I thought I was putting forward a useful little tip to a very common problem.
Instead, it would seem, I have created a monster.
The help available on this forum is invaluable, especially to learner programmers like myself.
When, however, the discussion turns into a back and forth on the internals of Access it is no longer helpful to those like myself and serves little purpose other than to fill up the forum thread.
John

 

[561414]

Do you have a sample database you can upload where Me! makes a combobox dropdown but Me. does not?