automatically display the list of choices in the combo boxes when the cursor comes to

[jashgtp]

[FONT=&quot]
I would like to automatically display the list of choices in the combo boxes when the cursor comes to those combo boxes.


i used this code and then on the on got focus of each combo box i typed [/FONT] [FONT=&quot]=dropdown


However, it still wont work it gives me an error. The error is that the object does not contain the automation dropdown?

Any ideas.. This is on Access 2007 if that matters.
[/FONT]

Public Function dropdown()

Me.ActiveControl.dropdown

End Function​

 

[HiTechCoach]

Try this function code

Public Function dropdown()

   Application.Screen.ActiveControl.dropdown

End Function

 

[jashgtp]

sorry i tried that but im still getting the same error

 

[HiTechCoach]

I tested it and it works for me.

Can you post a sample of your form?

 

[Mike375]

I have this on MouseMove of the combo (A2003) Edit: Misread, should be GotFocus

DoCmd.GoToControl "Combo1543"
Forms![MasterForm]!Combo1543.Dropdown

 

[DCrake]

You could always use the Sendkeys F4 on the on got focus event of the combo

or

Private Sub Combo5_GotFocus()
Me.Combo5.Dropdown
End Sub

 

[HiTechCoach]

DCrake and Mike375,

It is possible to put VBA code in the event procedure, but the OP is trying to use a generic function that will work with all forms/combo boxes. In the original post, the combo box is not using an event procedure (why you both have suggested), but a call to a generic function.

I actually use this method a lot with my forms to keep the form form being so "heavy" (no code behind them). With code in a shared module, the code will remain in memory as different forms load, thus providing better performance. If the code is behind the form,m then it must be loaded/unloaded multiple times which is not very efficient.

 

[Simon_MT]

This is what I use to get a dropdown only when the combi is empty, rational if it is already popluated then dropdown is less likely to required.

Function ListDisplay()
 
    Dim MyControl As Control
    Set MyControl = Screen.ActiveControl
 
        If IsNull(MyControl) Then
            MyControl.Dropdown
        End If
 
End Function

Simon

 

[Mike375]

DCrake and Mike375,

I actually use this method a lot with my forms to keep the form form being so "heavy" (no code behind them). With code in a shared module, the code will remain in memory as different forms load, thus providing better performance. If the code is behind the form,m then it must be loaded/unloaded multiple times which is not very efficient.

What about a macro with SendKeys

Kystrokes {f4}
Wait Yes

and on GotFocus

I don't think macros are not "loaded" but A2007 might be different.

 

[Simon_MT]

Yes, that's how it can be done but using Functions is a better method although 2007 has increased the power of Macros, I was using Access 97 and Modules were more advanced. I don't bother with Macros anymore or Subs on Forms; lightweight Forms. More than anything with Modules you can get to all your code.

Simon

 

[Mike375]

More than anything with Modules you can get to all your code.

Simon

I use modules a lot and probably because of being a strong macro user.

When I made the little macro to test F4/Combo it did not work and then I realised I did not set Wait to Yes. Just bring the data base window to the top and change it. Ditto had a module been used.

As to speed and loading forms, I guess we are influenced by what we make but I can't see any difference with what I do. I do a lot of Access to Word and Access to Excel and a form will be loaded with stand alone labels and the code behind them, some of which is very long because of the number Bookmarks involved and I can't see any difference between the form with one lot of code and a form that is full of it.

 

[Simon_MT]

There probably isn't but I'm sure that the Forms are quicker. The other reason I used Modules was a matter of discipline, I realised that a lot of my old code was doing exactly the same thing but was Form dependent. With CodeContextObject I could use the same bit of code to do one task and not have reiterations. Similar to a building block Query what does all the data manipulation so that you don't have to repeat yourself in other higher level queries.

Simon

Simon

 

[Mike375]

I am sure the forms are quicker but it would need a stop watch.

My use of modules is not sophisticated, just a case of being able to get to them while the DB is full open, in other words, same deal as macros.

I also use modules (and macros) because it is easy for me to send it someone. If the stuff is on a form then I need to send the form and there form may have been changed.

I also use macros a lot because it is easy to change them over the phone, which we do a lot with telelmarketing data bases. Macros are real easy like that...go to the 7th line down etc. Also good becaue if a different form or query is going to be used it is on the drop down list. Access 95 was better for changing code on a fom control than later versions because only the code for the label/button displayed. I have some people that use my teklemarketing DBs that were OK to change code on a form control in A95 but not in A2003.

One of the members of AWF, might be misslinger, has as their signature...more than one way to skin a cat....and that sure does apply to this stuff

 

[Simon_MT]

I have a confession that I created a database pre-95 that used Macros almost exclusively, so I do know where you are coming from, and yes they were easy and obviously MS is still committed to them reflected by their recent enhancements.

Simon

 

[Mike375]

I could never understand why they use the word "macro" for Word and Excel. Module would have been better I think.

I did some work a couple of weeks ago on an A2003 DB that a smaller business has and it is all macro driven and is is a top DB for their business. However, I got the work for two reasons. One being they wanted to do Access to Word so no answer there in macros. The other reason I got the work was my competitor was anti macro and wanted to redo the whole deal.

 

[HiTechCoach]

jashgtp,

FIrst, what is the name of the module where you have the Dropdown fucntion? The name of the module MUST NOT BE dropdown

I also think that the problem is that you named the function the same name as the method. I used the fiunction name fDropDown()

Try:

Public Function fDropdown()

   Application.Screen.ActiveControl.dropdown

End Function

and use like: =fDropdown

See the attached 2007 database

 

[DCrake]

I can understand the requirement and the methodology behind this question, however it puzzles me as to why you would want to implement it globally across the whole application.

If I was a user and was tabbing between fields I would find it quite frustrating and annoying that the combo box opened even it I did not want it to. It would be too easy to change any existing values found in bound combo boxes.

For the sake of a single keypress or mouse click the whole event could be forgotten. Yes I agree you could get it do it if the combo box was a required field and at the point of the control receiving the focus it was empty.

Also what happens if you are using cascading combo boxes is this not going to invoke more code?

David

 

[Simon_MT]

Then you insert a test to see if the field is populated:

Function ListDisplay()
 
    Dim MyControl As Control
    Set MyControl = Screen.ActiveControl
 
        If IsNull(MyControl) Then
            MyControl.Dropdown
        End If
 
End Function

and then when you go into the combibox you then do:

Function LookupArtist()
    With Screen.ActiveControl
        .RowSource = "SELECT ArtistsLookup.Artist, ArtistsLookup.[Artist Short] FROM ArtistsLookup;"
        Call ListDisplay
    End With
End Function

This is invoking more code and simply dropdowns a list if the control is empty. My view on combiboxs is that they are aide and are to be used when they are required.

Simon

 

[HiTechCoach]

I can understand the requirement and the methodology behind this question, however it puzzles me as to why you would want to implement it globally across the whole application.

If I was a user and was tabbing between fields I would find it quite frustrating and annoying that the combo box opened even it I did not want it to. It would be too easy to change any existing values found in bound combo boxes.

For the sake of a single keypress or mouse click the whole event could be forgotten. Yes I agree you could get it do it if the combo box was a required field and at the point of the control receiving the focus it was empty.

Also what happens if you are using cascading combo boxes is this not going to invoke more code?

David

David,

If I was a user and was tabbing between fields I would find it quite frustrating and annoying that the combo box opened even it I did not want it to. It would be too easy to change any existing values found in bound combo boxes.

With >1000 data entry users, I have never had that problem or even complaint for them.

 

[Simon_MT]

The point about combiboxes is that you have to take a view when to invoke the dropdown, putting the code in a Module is academic and this is where understanding how users interface with the application comes into play.

I agree with David, that users get annoyed if dropdowns are invoked everytime they enter a combibox but it does save one click (which might be considered miniscle) but if you entering numerous records is useful. As Boyd pointed out users will accept dropdowns but I would only invoke them judiciously.

Simon