Is it possible to have a control like this? (Access2007) 2

[sagamw]

Is it possible to have a control like this in a form in Access2007? Instead of a listbox or a combobox.

(I am referring to the RIGHT image "Properties menu", NOT the calendar control)

I found it in this page

http://www.lebans.com/monthcalendar.htm

BUT unfortunately I can't open the sample database in Acc2007 to check how it can be done...

BTW What is the name of this control: Cascading menus or something?

 

[MajP]

You should be able to open the database. I had no problem.

What you are looking at is a windows calendar API, so the menu was not built separately but part of the windows control. To do something like this in general, you may either play with a list view control or code your own custom shortcut menu. I think the latter would give better results.

Look at this

http://blogs.msdn.com/access/archiv...t-menu-for-a-form-form-control-or-report.aspx

This example is simple, but you can make complex menus. In 2007 you can assign shortcut menus to controls. Here is how to popup the simple menu clicking on a cmd button.

Private Sub Form_Load()
CreateSimpleShortcutMenu
End Sub

Private Sub Command1_Click()
Me.ShortcutMenuBar = "SimpleShortcutMenu"
Application.CommandBars("SimpleShortCutMenu").ShowPopup
End Sub

 

[sagamw]

Thanks MajP.
So it's called "shortcut menu".
I think it may be useful in some cases.

I will "study" the link you provide me.

 

[sagamw]

P.S. Although it seems that isn't exactly what I thought of.

I was thinkinf for an alternate way to input data:

Instead of having 2-3 comboboxes you could have a "button" that pops up the shortcut menu to select a value.
for example. To choose a country from a list of 200, you could have a shortcut menu continent>country (choose Europe and then Italy).

Can be done this with shortcut menus?

 

[MajP]

So it's called "shortcut menu".

What in lebans demo is not a short cut menu but actually part of the API control.

To choose a country from a list of 200, you could have a shortcut menu continent>country (choose Europe and then Italy).

No not with 2-300 hundred. That all has to get coded, and really unmanageable. I guess you could have a table of values and build the command bars dynamically looping through the table.

To have cascading choices like lebans demo (and even more layers) then yes.

I have never done this before to function like cascading combos, but I can not see why not. The problem is that each choice needs to get associated with a function that returns a value. But I would think the function could possibly read the caption of the command bar and return that as the value.

I am interested in trying this, so if I get time I will post a demo and possibly a faq if it works well.

 

[sagamw]

Thanks again MajP.

Yes. That's I m talking about.
A shortcut menu to act like 2-3 cascading combos.
I think it will be great!
First of all we save space in our form.
If we can click a "small" button and have a shortcut menu appear, it's far better than 3 "large" combos that the user have to select in a specific order for making a choice.

I m looking forward for your demo.
(if you find the time that is)
(maybe a module or something to get its values from a table)

 

[MajP]

I am giving you a star for the idea. I do not know why I have never thought of this, but it works great. I am going to start using this all the time. Here is the demo.

http://www.4shared.com/file/210897288/8fab86f9/ShortCutMenuControl.html

I have answered thousands of post, and no one ever asks about command bars. One of those hidden features people forget about. You can actually do a lot more, such as add icons and checkboxes. You can also add controls onto these menubars such as comboboxes, text boxes, and cmd buttons. Then these controls also have events that you can trap. If I get time I will expand this demo.

So to answer your original question, yes you can do exactly what you originally asked.

 

[sagamw]

Thanks MajP.

I will test it ans see how it goes!

 

[sagamw]

Your demo works fine, but I have some trouble to embed it in my (sub)form.

I copy the module to my db, but when I tried to compile my db, I get a "user defined type, not defined" for all the Dim with the office.commandbar(control).

Is there an additional step?

 

[sagamw]

Pfh! I found it. you must enable ms office object library.

Is this gonna be a problem if I try to distribute the db to people who doesn't have office installed? i.e. in a runtime only version??

 

[sagamw]

*sigh* :-(

P.S. Sorry for the continuous posts

I have another problem.

I get a "Run-time error '5': Invalid procedure call or argument" when I click the button.

This happens ALSO if I copy and paste all the elements (forms, queries, module) of your demo in my db. The button in your form come up with the error.
Your demo solo works fine.
Pasted in my db I get the error 5.

 

[sagamw]

...and the most WEIRD thing, is that even when I change the tables Categories, Products (in your demo) the original values STAY the same :-(.

I can't understand how this happens. It seems like the commandbar has been created one/(the first) time, and it stayed the same. Where is it stored? Very Weird indeed.

 

[MajP]

you are correct. When you create a command bar one of the parameters is "temporary". I selected false so the commandbar becomes a persistent property of the db. If I was really doing this then when the form loads it should call the subroutine to make the command bar. If you change the values, you have to run the procedure to make the bar. As you see in the procedure before making the bar it deletes it first. If not you would get an error.

Which line do you get the error. The command bar object is a real pain to work with. The object model is really finicky and confusing. That error message appears on every error. So you do not really know the error.

Prior to 2007 Access had two command bar object models.
Access.commandBars
VBE.commandBars

I think in 2007 they are both deprecated.

 

[MajP]

Another thing if you are going to dynamically build command bars, you really need to have tight code. As I said the model is very finicky. My demo is far from locked down. A null value, duplicate ID, etc would throw an error and blow up. Need to have very precise logic and error checking.

 

[MajP]

However, the good news if your command bar is permanent (the country list does not change) you can build it once and then distribute it and it will persist. If it is permanent, I do not know if you need to have office loaded. You may only need office loaded to get to the Object model.

 

[sagamw]

Which line do you get the error.

I get the error when I press the button.

commandbars("mencategory").showpopup

I get the error when I paste your demo to my db, but not when I paste my db (something like ...40 tables,forms etc.) to your demo.
I can't keep the latter because I get some weird errors in other parts of my db for example I can't load .tif images in some picture objects. .jpg works fine)

However, the good news if your command bar is permanent (the country list does not change)

Unfortunately my friend, the countries was just an example.
My actual db needs, not only editable tables/shortcut menus, but I m gonna let the user change'em as he wishes. It's more like categories & products.

you are correct. When you create a command bar one of the parameters is "temporary". I selected false so the commandbar becomes a persistent property of the db.

I changed the 2nd false to true but the bar still shows your original tables, (I replace em with my own data but they still show the same) :-(
How is this possible?

 

[MajP]

I changed the 2nd false to true but the bar still shows your original tables, (I replace em with my own data but they still show the same) sad
How is this possible?

Did you run the code? In my demo, there is no place that I actually run the code. I ran it once from the VBE to build the commandbar.

I will try making the code a little more generic and reload it each time I load the form.

Do you get the error when you right click?

 

[sagamw]

I misunderstood how your demo works.
I thought the module runs each time the db or the form loads.
I ran the macro readmenudata() from the vba editor and it worked. (I did get error 5 a few times though, but now it seems to work left & right click)

I will try making the code a little more generic and reload it each time I load the form.

That will be GREAT.
For my db I want to have 2 tables (like categories & products), a form that the user can use for adding/deleting entries from the tables, and the shortcut menu to load/adjust each time with the fresh data form the tables.

I am looking forward for your new demo.

 

[sagamw]

P.S. also if you can add the code for sending the selection of the shortcut menu, to an unbound text field for further use.

 

[MajP]

http://www.4shared.com/file/212660097/b799a176/ShortCutMenuControl_V2.html

This is pretty cool. There has to be some drawback to doing this, because you would think most people would want to do this. You can code cascading combo boxes so easy, and drastically reduce real estate. I can find very little information on the web for working with command bars. The MS site is horrible.

I just wonder how much the database bloats if you add and delete command bars.

One thing if you make a cb permanent then it is impossible to delete. I run the delete command and then check if it exists. I get false. Then I go to the form and close the form. Then check if it exists and somehow it exists.