MS Access: custom control built in another language

[ironfelix717]

Hi,

I have a need for a specific control to be built for an Access application. I have built this control in a rather crude way with the existing built in controls using VBA. But performance is rather poor due to lots of looping. Some of the performance can be improved with some code enhancements but ultimately a truly custom control object would be the fastest and simplest approach.

I would rather have an actual control with events. Is it possible, or should I say, feasible to build a control in another programming language to use with VBA? Have people done this?

Thanks
Regards

 

[theDBguy]

Hi. Yes, I believe so. I think that's what is called an ActiveX control. Maybe there's another term for it or another method to create one, but I don't have any information on that.

 

[ironfelix717]

theDBguy,

Ahh, the "ActiveX" term eluded me. I'll do some research with that keyword... curious to see if anyone has any hands on experience with this and has an idea if the difficulty.

Thanks for your input!

 

[theDBguy]

theDBguy,

Ahh, the "ActiveX" term eluded me. I'll do some research with that keyword... curious to see if anyone has any hands on experience with this and has an idea if the difficulty.

Thanks for your input!

Hi. Good luck. Let us know how it goes.

 

[jdraw]

You might get some info/ideas from this article. I have not used c#, but have seen references to create useful dlls in some articles.
Good luck - let us know how things go.

 

[MajP]

Can you describe what your control does? Maybe you can fake it. You can roll your own custom classes and fake controls. You can raise your own custom events.

If you use MSFORMS controls you can really fake a lot of controls well. The most extreme is here
https://www.jkp-ads.com/articles/treeview.asp

Here is a simple calendar control. Notice this requires one line of code in the access form. Just drop the Userform and class modules.

 

[ironfelix717]

Hi all, great input!

MajP:
That's an awesome link. I'll return in about 6 hours with a more detailed explanation of my control and what I have developed so far.

Looking forward to seeing how others have approached similar scenarios!

Regards

 

[isladogs]

Just for comparison, the link below is to another calendar control but this time not made using MSForms. It also only needs 1 line of code in the calling form
https://www.access-programmers.co.uk/forums/showthread.php?t=298062

 

[ironfelix717]

Hello,

Returning with more information. The control in question is a custom listbox control that allows for drag-n-drop re-order functionality. I have developed this already and have been using it for quite some time in VBA using a crude subform with textbox objects arranged in column/row format. My intentions have been to use this "control" as it functions fine, but recently I have pondered a better solution. The issues with this subform method are as follow:


1.) Small bugs: inherent to access - occasional flickering, mouse clicks not registering. Minute details.

2.) Duplication: If i want to use this "control" as a subform embedded in multiple forms, I now must duplicate that form, correct? Double the code to maintain, double the headaches.

3.) Speed: Nearly every method in my code requires looping through all these values which is surprisingly slow. Some of this can be improved with better design. But were talking about fractions of a second in performance that may make the user experience much more enjoyable and smooth. Considering the following:

Want to get the value of the selected listbox item:
-must loop through each textbox until you find the highlighted textbox

Want to clear the listbox
-must loop

Want to get the count
-must loop

Want to append a value to the listbox
-must get list count first to know where to place new value - must loop

Only a single solution pre-exists for this listbox with drag-n-drop reordering functionality and that is with Peters Software, which i purchased and realized... good luck understanding the source code. And that its really not that intuitive to use. Although, a great alternative, i suppose.

Ultimately, I believe a truly custom ActiveX control will be the fastest and cleanest solution.

 

[isladogs]

Depending on your version of Access/Windows you may be able to achieve this using a listview control. For example, see https://www.access-programmers.co.uk/forums/showpost.php?p=724780&postcount=13

However I believe this requires the old MS Common Controls which are no longer supplied and aren't supported in 64-bit

 

[deletedT]

Isn't it much simpler and faster if you use two buttons instead of dragging?

 

[ironfelix717]

Isn't it much simpler and faster if you use two buttons instead of dragging?

Its MUCH simpler. But not faster.

Moving an item say, 5 rows with buttons, will cause the underlying change
event to fire 5 times. Thats 4 more times than needed.

 

[MajP]

Here is an 80% solution using the up down buttons. This turns any listbox into a move up down listbox using a single line of code. This gives you the re-usability. Works with row sources based on queries and value lists. I added a custom event to the class, but can add more depending on what you want. I am working on the drag drop. The issue with the drag drop is in the "drag" portion. When you click into a non selected record the selected index does not occur until after the mouse down event. So in order to figure out where you clicked you need to determine the height of each row and the current Y position. Unfortunately determining the height of a row is not trivial. However, I have found code that does this and just need to decipher what I need. Once I figure that out, it will be an easy mod to the class module.

If anyone has the code to determine the selected row based on mouse location (y position), I would appreciate it.

Want to get the value of the selected listbox item:

I provided an event to return the bound value, but you can read it directly. I could make it easier with a Property.

Want to clear the listbox

Is that the same as the deselect. Or you mean clear the values. That can be done directly by setting the rowsource. Could give you a method to make that easier.

Want to get the count

That is done directly with a listcount. But regardless of the control you would never have to loop.

Want to append a value to the listbox

again no idea why you would have to loop. Just use the additem method directly.

If you can explain in more detail how you want those features, I could demo better.

 

[ironfelix717]

Here is an 80% solution using the up down buttons. This turns any listbox into a move up down listbox using a single line of code. This gives you the re-usability. Works with row sources based on queries and value lists. I added a custom event to the class, but can add more depending on what you want. I am working on the drag drop. The issue with the drag drop is in the "drag" portion. When you click into a non selected record the selected index does not occur until after the mouse down event. So in order to figure out where you clicked you need to determine the height of each row and the current Y position. Unfortunately determining the height of a row is not trivial. However, I have found code that does this and just need to decipher what I need. Once I figure that out, it will be an easy mod to the class module.

If anyone has the code to determine the selected row based on mouse location (y position), I would appreciate it.

I haven't looked at this code yet. I will in a few hours.

The height of a listbox row is a topic that Collin and I worked on about 6 months ago. If you're bored you can sift through how we arrived at our solution:

Or you can visit his website where he has the solution:



My solution is attached, if you need help with finding the row height and cannot understand from Collin's site, please let me know and I will pass on my code, which may be easier to understand.

Regards


EDIT: I would've posted the links above, but this forum doesn't allow me to post links haha. Thats a joke, right? I can upload a file but not a link?

 

[MajP]

The height of a listbox row is a topic that Collin and I worked on about 6 months ago. If you're bored you can sift through how we arrived at our solution:

I thought he may have this since he does a lot of precision moving of controls. If you can provide a direct place to look that would help.
I cannot get the form to work, says it is missing a source table.
I looked at the code and it looks painful. Not sure all what it does, but looks like a Rude Goldberg design.

 

[isladogs]

I thought he may have this since he does a lot of precision moving of controls. If you can provide a direct place to look that would help.
I cannot get the form to work, says it is missing a source table.
I looked at the code and it looks painful. Not sure all what it does, but looks like a Rude Goldberg design.

Hopefully you're not referring to my code! AFAIK that's not being used in ironfelix's example
Here is the link to the example app where I worked out how to get the listbox item under the mouse cursor without clicking: Accurately Move Forms & Controls

The code is used in Forms 4 & 5 of the example app.
After trying code by Stephen Lebans & others (all of which were unreliable for this purpose), I ended up developing my own code using the undocumented Wizhook function.

Listbox drag & drop should be an obvious extension of that code.
Let me know how you get on

@Ironfelix
Members with less than 10 posts have some functionality removed. This is to limit the impact of spammers.
Once you have 10 posts you will be able to add links, send PMs etc

 

[sonic8]

2.) Duplication: If i want to use this "control" as a subform embedded in multiple forms, I now must duplicate that form, correct? Double the code to maintain, double the headaches.

No, you can add a single form to any number of forms as a subform. You even add it multiple times to the same form.

Ultimately, I believe a truly custom ActiveX control will be the fastest and cleanest solution.

Possibly yes. I created ActiveX-Controls in .Net to be used in Access particularly to implement drag'n'drop and complex visualization.

I even did a presentation on that topic at AEK10 (that was back in 2007!). The slides, accompanying text and demos are available for download here. - Slides and text are in German and will probably be of little value to you, but maybe it's worth it to look at the demos.

 

[ironfelix717]

Like I said, i can't post a link because the permissions of this forum and I currently am not at my workstation to provide you the code. His website has an example showing multiple listbox functions for various tasks. One of them is highlighting listbox selection via mouseover, which contains the code you need, using 2 API calls and a Wizhooks function to calculate the twips value of the text size.

I tried this approach using a legit listbox with the row height months ago and got nowhere with it.

The source table issue is probably due to deleting a random table I had in it before sending. It was just a blank table so maybe its corrupt.

I wouldn't expect you to understand a complex fully custom control's code anyway when there is no documentation or code notes.

 

[isladogs]

Like I said, i can't post a link because the permissions of this forum and I currently am not at my workstation to provide you the code. His website has an example showing multiple listbox functions for various tasks. One of them is highlighting listbox selection via mouseover, which contains the code you need, using 2 API calls and a Wizhooks function to calculate the twips value of the text size.

See post #16

 

[ironfelix717]

Yes I saw...

Here is my listbox drag n drop solution which should not be corrupt, again, no code notes.