MS Access: custom control built in another language (1 Viewer)

ironfelix717

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
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

I’m here to help
Staff member
Local time
Today, 14:40
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
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

I’m here to help
Staff member
Local time
Today, 14:40
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 17:40
Joined
Jan 23, 2006
Messages
15,364
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

You've got your good things, and you've got mine.
Local time
Today, 17:40
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • MSForms Calendar MajP.accdb
    1.1 MB · Views: 159
Last edited:

ironfelix717

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
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
 

ironfelix717

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
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.
 

Attachments

  • Example_Video_export.mp4.zip
    78.3 KB · Views: 150

deletedT

Guest
Local time
Today, 21:40
Joined
Feb 2, 2019
Messages
1,218
Isn't it much simpler and faster if you use two buttons instead of dragging?
 

Attachments

  • Database1.accdb
    896 KB · Views: 161

ironfelix717

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
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

You've got your good things, and you've got mine.
Local time
Today, 17:40
Joined
May 21, 2018
Messages
8,463
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.
 
Last edited:

ironfelix717

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
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?
 

Attachments

  • ListboxExample.accdb.zip
    295.2 KB · Views: 146

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:40
Joined
May 21, 2018
Messages
8,463
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

MVP / VIP
Local time
Today, 21:40
Joined
Jan 14, 2017
Messages
18,186
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! :rolleyes: 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
 
Last edited:

sonic8

AWF VIP
Local time
Today, 22:40
Joined
Oct 27, 2015
Messages
998
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

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
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

MVP / VIP
Local time
Today, 21:40
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 17:40
Joined
Sep 20, 2019
Messages
193
Yes I saw...

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

Attachments

  • 10_17_19_EXAMPLE.zip
    361.2 KB · Views: 171

Users who are viewing this thread

Top Bottom