MS Access: custom control built in another language

[CJ_London]

the code is old and there is a small change you need to make

in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window

If fGetClassName(hWnd_VSB) = "scrollBar" Then

this needs to be changed to

If fGetClassName(hWnd_VSB) = "NUIscrollBar" Then

Then it will work - I guess this is a windows change rather than an office change - don't know when but somewhere between win95 and win10

 

[isladogs]

Hi CJ
Magic. Thanks!

don't know when but somewhere between win95 and win1

LOL!

Where did you discover that necessary change? Just 2 locations to alter
And have you tried converting this code for 64-bit?

 

[CJ_London]

this is an old example using that module. It scrolls without scrollbars. I originally developed it for touch devices where the scrollbar a) may be too small for stubby fingers or conversely b) the window is too small so more estate is available to the actual form.

This is an image example which tends to flicker for horizontal movements. Much less noticeable on web controls or single/continuous forms.

To scroll, click down with a mouse over the image and move around, or just use your finger on a touch device.

 

[CJ_London]

I've got three - this was a while ago and I may have added further functionality. In the example I've just posted see these functions

fIsScrollBar
fIsScrollBarHZ
getScrollhWnds

I don't have 64bit so not tried converting - you're welcome to try converting the example I posted.

Where I found it? A window has a number or properties, including Hwnd and class, so when I couldn't get it to work, I looked at all the classes in the parent window and found the NUIScrollBar class, gave it as try and hey presto!

 

[isladogs]

I've got three - this was a while ago and I may have added further functionality. In the example I've just posted see these functions

fIsScrollBar
fIsScrollBarHZ
getScrollhWnds

I don't have 64bit so not tried converting - you're welcome to try converting the example I posted.

Where I found it? A window has a number or properties, including Hwnd and class, so when I couldn't get it to work, I looked at all the classes in the parent window and found the NUIScrollBar class, gave it as try and hey presto!

For info, getScrollhWnds wasn't in the original file

Thanks for the example. I remember discussing that with you a few months ago. Hence my comment in post #36

 

[ironfelix717]

It appears we have a wonderful solution with MajP's code. Excellent work! Your work here has further extended the capabilities of Access for many, myself included.

I made some modifications/improvements to the code. Besides putting my own twist on things (we all have a style), there are a few good contributions I think i made:

RecordUpdating
There isn't much functional advantage to this mighty feature unless you can update the rowsource table behind the scenes, so that the next time you load the data, things are in order as you left it. There is now a RecordUpdating property, which can be set to True if the user intends to use this feature with a proc to handle the re-ordering.

Some assumptions that must be made when using this feature are:
The table field containing the order of the items must exist and should be Number - Long Integer data type. The default field name holding these values is "OrderIndex" but can be overridden by setting "OrderFieldName" property to whatever is desired. The table should contain these order values prior to using this function - easy to do (add OrderIndex at record conception)

The next assumption is that the RowSource for the listbox is either a SQL statement or TableDef name. QueryDef names are not supported but could be quite easily.

Drop position when DropIndex > DragIndex
The original placed a dropped item above the target (when Drop>Drag), where a true drag-drop list replaces that item and shifts everything down, so a nice + 1 in the moveItemDragDrop proc did the fix! If a user happens to prefer the original style, then removing the Plus 1 will revert changes here.

Bug Fix in convertToValueList
When the listbox column count exceeds the RowSource field count, an error will occur so code was added to handle this event.


Great work to MajP for an awesome solution. Acknowledgments to all others who contributed directly & indirectly.

Regards

 

[MajP]

QueryDef names are not supported but could be quite easily.

Works for me especially with the column count fix. Not sure the issue .

I would add a requery method.
create:
Private OriginalRowSource
in the initialize:
OriginalRowSource = me.listbox.rowsource
then add

Public Sub Requery()
Me.ListBox.RowSource = OriginalRowsource
convertToValueList
End Sub

The table should contain these order values prior to using this function - easy to do (add OrderIndex at record conception)

You could add a optional "OrderOnLoad" boolean in the initialize. Then loop the recordset and set the value of the order. The next time you come in they will already be ordered so it will just keep the current value.

The original placed a dropped item above the target (when Drop>Drag), where a true drag-drop list replaces that item and shifts everything down, so a nice + 1 in the moveItemDragDrop proc did the fix! If a user happens to prefer the original style, then removing the Plus 1 will revert changes here.

You could make this optional parameter in the initalize. I would have an Enumerator.
DropAbove = 1
DropBelow = 0

Hopefully what CJ_London provided will give a solution for the scrolled listbox.

 

[MajP]

@CJ_London (or others),
Do you have code that would work for a listbox scrollbar? I realized all of that code is for a form. I assume modifying it for a listbox would be a huge task. Isladogs' code works well for determining the row of a listbox in the visible list. It gives the proper row down from the top of the listbox in the visible window. So once you scroll you need to determine how far down you are scrolled to add to the row count. Thanks.

 

[CJ_London]

Do you have code that would work for a listbox scrollbar?

Not sure if Colin has already done it, but Stephen Lebans code will probably work providing the listbox has focus. See the example I loaded, check out module1.

 

[MajP]

Not sure if Colin has already done it, but Stephen Lebans code will probably work providing the listbox has focus. See the example I loaded, check out module1.

I might be missing how to do this, but every function I see in module 1 passes a form instance. No idea how to do this with a listbox scrollbar. Thanks.
As far as I can tell Collin does not handle the scroll bar. Works great until scrolled.

 

[CJ_London]

for this example yes - But look at what is does with the form object - it gets the form Hwnd, nothing more.

see the fIsScrollBar function and the HZ equivalent.

The code could be modified to pass the form Hwnd instead of the form object

And a Hwnd is a Hwnd, if the control has the focus it has a Hwnd - you just need to find it. Once found you can then find the Hwnd to the scrollbar (assuming it exists)

fIsScrollBar is looking for the scrollbar window by looping through the child windows - so look for the listbox control window first, then look for the scrollbar window

 

[ironfelix717]

Hi,

Works for me especially with the column count fix. Not sure the issue .

It shouldn't work with a query def because the proc to get the table name verifies the rowsource table exists in db.tabledefs and throws an error if not found. I rarely use qdfs and prefer sql string.

I would add a requery method.
create:
Private OriginalRowSource
in the initialize:
OriginalRowSource = me.listbox.rowsource
then add

The rowsource is already being grabbed in initialize and set to the "ListSource" module var.
I added requery method.

Will post any other substantial updates.

Regards,

 

[MajP]

It shouldn't work with a query def because the proc to get the table name verifies the rowsource table exists in db.tabledefs and throws an error if not found.

No it does not do that. It simply creates a recordset using the dao openrecordset function. Which takes a table name, query name, or sql string. So yes it does work with a query name.

 

[ironfelix717]

No it does not do that. It simply creates a recordset using the dao openrecordset function. Which takes a table name, query name, or sql string. So yes it does work with a query name.

RecordsetUpdating does not work with a querydef name. When the rowsource is set to a querydef name AND RecordsetUpdating = True, it will fail on re-order.

The reason it will fail is because the TableFromSQL func only supports a rowsource of either a discrete tabledef name or a SQL statement.. I've since found misc bugs in this function and have fixed them, along with allowing querydef support, which just means adding a check-loop in db.querydefs to the function.

Will post updates soon.
Regards

 

[MajP]

I get it now. You are saying that your function does not work, not that my code does support query names.

 

[MajP]

If anyone interested here is an updated class with a few more features.
1. Optional dragdrop
2. optional double click drop
3. Return a sorted column
4. return current value from specified column
5. Update table with sort order
6. requery listbox
7. Up, down, top, bottom, deselect buttons

Again the drag drop only works when the listbox is not scrolled. The double click works either way. Also this will not work with column heads. To get this functionality for any listbox requires the user to provide a couple lines of code.

 

[MajP]

@ironFelix,
You may find these classes of interest. You can do a lot with listboxes. Same idea of making a class module to turn any listbox into a find as you type or a to from listbox.

 

[deletedT]

@ironFelix,
You may find these classes of interest. You can do a lot with listboxes. Same idea of making a class module to turn any listbox into a find as you type or a to from listbox.

You may want to correct this in FAYT sample file. Missing quotation marks:

 

[deletedT]

@ironFelix,
You may find these classes of interest. You can do a lot with listboxes. Same idea of making a class module to turn any listbox into a find as you type or a to from listbox.

And this error when I click a label to sort.

 

[MajP]

Thanks, but I am kind of confused. My function look like this

Private Function InternationalCharacters(ByVal strText As String) As String
   InternationalCharacters = strText
   'If you type international characters then turn them first to english
    'Type international and get english Add others as necessary á, é, í, ó, ú, ü, ñ
    'I do not know which ones are supported by keyboards but you may have to include
    'all seen below
    InternationalCharacters = Replace(InternationalCharacters, "á", "a")
    InternationalCharacters = Replace(InternationalCharacters, "é", "e")
    InternationalCharacters = Replace(InternationalCharacters, "í", "i")
    InternationalCharacters = Replace(InternationalCharacters, "ó", "o")
    InternationalCharacters = Replace(InternationalCharacters, "ú", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ü", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ñ", "n")
        

   'Type english and get international
    InternationalCharacters = Replace(InternationalCharacters, "a", "[aàáâãäå]")
    InternationalCharacters = Replace(InternationalCharacters, "e", "[eèéêë]")
    InternationalCharacters = Replace(InternationalCharacters, "i", "[iìíîï]")
    InternationalCharacters = Replace(InternationalCharacters, "o", "[oòóôõöø]")
    InternationalCharacters = Replace(InternationalCharacters, "u", "[uùúûü]")
    InternationalCharacters = Replace(InternationalCharacters, "n", "[nñ]")
    InternationalCharacters = Replace(InternationalCharacters, "y", "[yýÿ]")
    InternationalCharacters = Replace(InternationalCharacters, "z", "[zž]")
    InternationalCharacters = Replace(InternationalCharacters, "s", "[sš]")
    InternationalCharacters = Replace(InternationalCharacters, "d", "[dð]")
  
End Function

which looks different from what you show. Not sure I understand what you ares showing.
The sort label is code from another class that I forgot to add in. I will combine that for the demo. Thanks.