MS Access: custom control built in another language

[ironfelix717]

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.




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.

Sonic, awesome! Will look into that information.
Thanks

 

[isladogs]

Yes I saw...

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

Just had a quick look.
Though it looks like a listbox, you are actually using a subform.
The drag and drop part seems to work fine but clicking on a 'listbox' record creates error 9: Subscript out of range

I had a quick look at the code. There is certainly lots going on in the background. I haven't checked it carefully to determine whether it can be streamlined or not.

 

[ironfelix717]

Colin,

The subscript error is a bug that I dont believe can be fixed with my current code. This is related to an array assignment as you probably know but occurs (for me) rarely.

A few comments on the code:
Columns are designated in textbox name by a letter, where rows are by a number. It's a poor design in the sense that the columns are by letter because it makes looping a pain when using non numerical names. That can be fixed pretty easily.

It's a crude design but works for now until a better solution presents itself.

 

[deletedT]

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.

Your drag and drop in the file you submitted in #20 is not a list box. It's a subform.
To be able to drag the rows, you have 1885 lines of code.
And you still believe it's faster than running a 10 lines of code 5 times?

 

[ironfelix717]

Your drag and drop in the file you submitted in #20 is not a list box. It's a subform.
To be able to drag the rows, you have 1885 lines of code.
And you still believe it's faster than running a 10 lines of code 5 times?

The screen will not update with your assumed method UNTIL event code has finished execution--making for a laggy experience.

This is why its faster:
--Drag N Drop method = 1900 lines of code
--Arrow method = ~25 lines of code (arbitrary)
--Change Event Code = ~1000 lines of code (arbitrary and realistic)

Move position of listbox 5 rows down or up:

Drag-N-Drop Method: 1900 + 1000 = 2900
Arrow Method: (5*25) + (5*1000) = 5125

Regardless, this is about preference. A drag-n-drop reorder is more intuitive than an arrow system and debatably faster.

 

[isladogs]

Whilst agreeing that in the end this is about personal preference, I disagree fundamentally with your analysis of the amount of code required.

The attached screenshot is for a route planner from one of my apps.
The positions of the listbox items can be modified using Move Up/Down buttons.
Unlike your (non-listbox) approach, it uses a multiselect listbox so you can move several items at once.
In the screenshot I'm moving 6 items at once.
The number of items in the listbox is unlimited - again unlike your approach.

So to move up all those items by your arbitrary 5 places needs 5 button clicks but performance is instantaneous and there is no flickering.

The cmdDown button invokes the MoveDown. Here is the code in its entirety (apart from error handling lines which I've removed for clarity in this post)

Dim itm As Variant
Dim intStart As Integer, intEnd As Integer

Private Sub MoveDown(intStart As Integer, intEnd As Integer)

10        If intStart < 0 Or intEnd < 0 Then Exit Sub
20        If intEnd + 1 > Me.LstItinerary.ListCount - 1 Then Exit Sub
         
30        CurrentDb.Execute "UPDATE tblRoutePlanner SET RouteOrderNo = (RouteOrderNo + 1)" & _
              " WHERE RouteOrderNo BETWEEN " & Me.LstItinerary.Column(1, intStart) & " AND " & Me.LstItinerary.Column(1, intEnd) & ";"
          
40        CurrentDb.Execute "UPDATE tblRoutePlanner SET RouteOrderNo = " & Me.LstItinerary.Column(1, intStart) - 1 & _
              " WHERE ID=" & Me.LstItinerary.Column(0, intEnd + 1) & ";"
              
50        Me.LstItinerary.Selected(intStart) = False
60        Me.LstItinerary.Selected(intEnd + 1) = True
          
70        intStart = -1
80        intEnd = -1
          
End Sub

Private Sub cmdDown_Click()

10       If Me.LstItinerary.ItemsSelected.count = 0 Then Exit Sub
         
20        intStart = -1: intEnd = -1
          
30        For Each itm In Me.LstItinerary.ItemsSelected
40            If intStart < 0 Then intStart = itm
50            If intEnd < 0 Then intEnd = itm
              
60            If itm > intEnd + 1 Then
70                MoveDown intStart, intEnd
80                intStart = itm
90            End If
100           intEnd = itm

110       Next
          
120       MoveDown intStart, intEnd
130       Me.LstItinerary.Requery
          
140       itm = Null
          
End Sub

That's a total of 22 lines of code per click
5 clicks means the same code repeated 5 times - effectively 110 lines of code and remember I can move many items at once. In this case I'm moving 6 items

Here is the equivalent for move up events

Private Sub MoveUp(intStart As Integer, intEnd As Integer)

10        If intStart <= 0 Or intEnd <= 0 Then Exit Sub
          
20        CurrentDb.Execute "UPDATE tblRoutePlanner SET RouteOrderNo = (RouteOrderNo - 1)" & _
              " WHERE RouteOrderNo BETWEEN " & Me.LstItinerary.Column(1, intStart) & " AND " & Me.LstItinerary.Column(1, intEnd) & ";"
          
30        CurrentDb.Execute "UPDATE tblRoutePlanner SET RouteOrderNo = " & Me.LstItinerary.Column(1, intEnd) + 1 & _
              " WHERE ID=" & Me.LstItinerary.Column(0, intStart - 1) & ";"
              
40        Me.LstItinerary.Selected(intStart - 1) = True
50        Me.LstItinerary.Selected(intEnd) = False
          
60        intStart = -1
70        intEnd = -1
          
End Sub

Private Sub cmdUp_Click()

10        If Me.LstItinerary.ItemsSelected.count = 0 Then Exit Sub
         
20        intStart = -1: intEnd = -1
30        For Each itm In Me.LstItinerary.ItemsSelected
              
40            If intStart < 0 Then intStart = itm
50            If intEnd < 0 Then intEnd = itm
              
60            If itm > intEnd + 1 Then
70                MoveUp intStart, intEnd
80                intStart = itm
90            End If
100           intEnd = itm
              
110       Next
120       MoveUp intStart, intEnd
130       Me.LstItinerary.Requery
          
140       itm = Null
          
End Sub

This time a total of 21 lines.

Using your drag & drop non-listbox method to move those 6 items would, using your figures, involve the same 1900 lines repeated 6 times = 11400 lines of code (compared to 110 using button clicks).

The simplest change of all moving 1 item up by 1 place involves just 22 lines against your 1900 lines. The button approach always uses less code and you can guarantee the item will end up where you want … not always easy with drag and drop.

I could make further comparisons but the button approach is FAR simpler to code. It uses a listbox rather than a subform, is equally fast with no flickering and it can be argued that it gives a better user experience. Attached is a short video in MP4 format to illustrate this

Whilst a drag and drop feature in a listbox would be great, at the moment there is no working code for that (though perhaps MajP will succeed where others have failed). By contrast the other approach is already available & has been thoroughly tested over many years. I know which I prefer....

 

[deletedT]

isladogs you're a genius.

I never had thought of a multi item move approach in a listbox. It makes my life easier.
Million thanks for sharing.

In regards of your comparison, that was exactly what I meant.

 

[isladogs]

Thanks. Very kind.
That code was adapted from a suggestion by another forum colleague and it works really well. I've seen lots of similar code but that is probably the most concise.
In fact if the two buttons have Auto Repeat set to Yes its even faster...but I prefer to do it one step at a time

 

[ironfelix717]

Hi all,

About Colin's example:

I'm not sure why there would be a need to move multiple items in a listbox But to each their own.

With your example, there is no change event code happening, therefore its going to be fast -- no functional code is being executed when a listbox item is moved (i.e. updating a record, saving other control values to a table, loading some data about the list item.) Comparing my code to your example, i can agree yours is faster in plain sight - but it is when you begin to tie code to positional movements that an arrow design will suffer in performance due to the repeated execution at every single instance of a positional movement. This decrease in performance increases linearly with the amount of positions moved.

If we are talking strictly lines of code as a (mediocre) measure of performance, 70% of my code is textbox events. I could reduce all the code unrelated to the core functionality to probably ~600 lines of code -- not that large.

Much obliged for attempting to offer a different solution, but i'm afraid I am stuck in my ways and an arrow solution isn't the reason I prompted this thread. Having a native listbox that can be dragged-dropped to reorder would be the best solution, but like i mentioned, I didn't get far. Maybe someone else can!

Regards,

 

[isladogs]

Again with respect to my example:
1. The multiselect movement is useful in this case to quickly reorder a delivery itinerary for the current date.
2. The code includes update statements to store the new position of each item. That is used to obtain the latest position of each item if the form is closed and reopened.
3. Performance remains extremely fast no matter how many items are in the listbox or the number of items moved. There is no noticeable delay.
4. Using autorepeat its ridiculously fast but its easy to 'overshoot' the intended position which is why I don't use it.
FWIW that's also part of the issue with drag and drop - it can be slightly tricky dropping objects in the correct place.

There are plenty of examples online including several at this forum of dragging items from one listbox to another. These can work well.
The fact that there seem to be no working examples of dragging items to new positions within a listbox suggests that it will be difficult if not impossible to get it to work well (if at all). I'm happy to be proved wrong however.

I persevered with developing code to 'select' listbox items using mouse move events as I could see uses for that where no equivalent functionality existed.

However, I'm not going to try solving this particular challenge as in my opinion there is a good working method already in existence.

I did suggest looking at a list view which is part of the old common controls. Did you look into that suggestion?

Anyway, hopefully someone will be sufficiently motivated to solve this for you.

 

[ironfelix717]

Colin,

My intentions with this project are not to import data with a drag - only to reorder via mouse. The code i purchased from Peters Software has that "import" functionality, but i don't require it. I think maybe I could solve that using your work with list box row heights. Not sure, I don't have any desire to pursue that.

The listview looks interesting. Even if not applicable for this project, perhaps other forms could use it. Does this need installed? I downloaded common controls for VB6 via MS website, but not seeing it installed under Active X in Access.

Regards

 

[isladogs]

Not sure whether the VB6 file is what you want.
Also can't remember whether you need to register the file using regsvr32.
Suggest a google search.

To use the common controls library you need to add that as a vba reference.
However it doesn't work in 64-bit Access and there is no 64-bit replacement.

I haven't used a listview for a long time and can't advise further. Again do a Google search on it. Others may have more experience with it than me.

 

[sonic8]

To use the common controls library you need to add that as a vba reference.
However it doesn't work in 64-bit Access and there is no 64-bit replacement.

That info is out of date. The 64-bit Common Controls were released about two years ago with Office 365 Version 1707.

 

[MajP]

@Isladog
I believe I stripped your code down to the bare bones to make a generic set of functions that return the row height and list index. It seems to work great, except not sure if there is an issue. You appeared to purposely add +1 to lstpos. If I include that,I do not get a zero based index. If your intention was to have it 1 based that would be good, if not then I may have stripped out to much code.
The drag drop works great if the list is not scrolled. It does not account for scrolling. Was that previously accounted for, and I stripped too much code out? In the demo I added an event to return the drag and drop indices. You can see all works well until scrolling down in the list.
So I say this is an 100% solution if your items fit in the visible window, but not a solution if it does not. I think it may require API to determine if it is scrolled. Any ideas.
Again all this functionality requires a single line of code to make any listbox into a sortable drag_drop listbox regardless if table/query or value list.

A possible alternate solution giving the ability to move anywhere would be a double click to select and single click to drop instead of drag drop. That would be simple and give the same ability to move a long distance instead of single up down.

Stripped code

Private Function GetListIndex(Y As Single) As Long
    'LBRH =listbox row height - calculated in GetListboxRowHeight procedure
     Dim LBRH As Long 'list box row height
     Dim LC As Long 'listcount
     Dim IntColumnHeads As Integer
     Dim lstPos As Long
     Dim OldLstPos As Long
     LBRH = GetListboxRowHeight()
     If LBRH <> 0 Then
            IntColumnHeads = Me.ListBox.ColumnHeads 'column headers visible?
            '-1 if true, 0 if false
            lstPos = ((Y - 45) \ LBRH) + IntColumnHeads ' NOTE Collin had a + 1
            If lstPos <> OldLstPos Then 'cursor has moved
                LC = Me.ListBox.ListCount + IntColumnHeads  '-1 to allow for header
                If lstPos > 0 And lstPos <= LC Then
                  GetListIndex = lstPos
                End If
            End If
    End If
End Function
Private Function GetListboxRowHeight() As Long

   '=========================================
    'Stephen Lebans (1999)
      ' Access always leaves a 2 pixel margin at the top of the ListBox
      ' before it begins to render the first row.
       ' It also leaves one extra pixel of space between each row.
          
    'NOTE 1px = 15 twips
    
    'My observations
    'Add 3pt to font point size and use function converttoTwipsYFromPoint(point size)
    'Then add +15 twips for each row (1px)
    'For row 1 - add a further 45 twips (3px)
    
   'get  listbox row height in twips
    'Typically a listbox row is the height of capital A with +3pt eg. 11pt font =>14
      
   'This gives approx correct answer
   ' LBFS = Me.lstImages.FontSize + 3
   ' LBRH = converttoTwipsYFromPoint(LBFS)
     '=========================================
    
    'Wizhook converts row height perfectly
    WizHook.Key = 51488399
    Dim lx As Long
    Dim ly As Long
    Dim LBRH As Long
    With Me.ListBox
        If WizHook.TwipsFromFont(.FontName, .FontSize, .FontWeight, .FontItalic, .FontUnderline, 0, "ABCghj", 0, lx, ly) = True Then
            LBRH = ly + 15  'font height +15 twips (1px space between rows)
        End If
    End With
    GetListboxRowHeight = LBRH
End Function

 

[isladogs]

That info is out of date. The 64-bit Common Controls were released about two years ago with Office 365 Version 1707.

Thanks Phillip. I wasn't aware of that will look into it.
Do the 64-bit controls work in 32-bit or are two sets of code needed?

Also, apologies. I've not had time to review your comments in our last email exchange. Will get back to you soon.

 

[isladogs]

Hi MajP
Good to know that my Wizhook based code provided the foundation for listbox drag and drop.
It certainly seems to be close to a working solution.

I've had a quick 'play' on my tablet so don't have my original code to hand.
So treat the following as a first response until I've had time to check properly against my app and on a desktop.

1. My guess is that I added 1 to LstPos to allow for column headers (if used).
Its many months ago so I need to check.
I tried setting column headers to Yes in your example but, as its based on a value list, the test was perhaps confusing. What happened was that the item above the one selected got moved. Not good!
Did you test this using a table/query as row source?
2. Using a touchscreen its still much more difficult to drag rather than use the buttons. Nothing to do with your code of course.
I'll test with a mouse on a desktop PC later.
3. The scrolling issue applied to my original example as well. I had spent so long on that example that I decided to 'park' that issue rather than try to deal with it.
I do have code for checking whether scrollbars are used but I'll need to check whether I have code to determine whether scrolling is in use. If I haven't got suitable code then CJ London may know as he has worked extensively with managing scrolling images for touchscreen apps.

As for your alternate solution using double click to select and single click to drop, I think I tried that some years ago before deciding button clicks for up/down were easier for end users to handle.

Anyway, keep up the good work.
I'll get back to you later if I have any further insights after checking my original code

 

[MajP]

3. The scrolling issue applied to my original example as well. I had spent so long on that example that I decided to 'park' that issue rather than try to deal with it.

I think Lebans has some API to determine location of scroll, but API is not my strong point.

As for your alternate solution using double click to select and single click to drop, I think I tried that some years ago before deciding button clicks for up/down were easier for end users to handle

I would not get rid of the buttons(top,bottom,up, down),but add this feature.
The demo has all three. Buttons, drag drop, double click and drop. I can add a Move 1/2 up move 1/2 down. Then even with a huge list (thousands of records) you could move it quickly. Or add a move N button so you can quickly pick how many spaces to move.

I think the double click drop, is actually better than drag drop when the list has to be scrolled. It is a lot quicker.

 

[isladogs]

I think Lebans has some API to determine location of scroll, but API is not my strong point.

Much as I admire Stephen Lebans' skills, some of his code no longer seems to work and converting it for 64-bit is a nightmare

If you have a specific reference I'm happy to look into it.

I would not get rid of the buttons(top,bottom,up, down),but add this feature.
The demo has all three. Buttons, drag drop, double click and drop. I can add a Move 1/2 up move 1/2 down. Then even with a huge list (thousands of records) you could move it quickly. Or add a move N button so you can quickly pick how many spaces to move.

I think the double click drop, is actually better than drag drop when the list has to be scrolled. It is a lot quicker.

I'd certainly agree about adding it as a feature rather than using drag/drop only. Tried this on my desktop using a mouse. If there are no column headers, the drag/drop works correctly...though I still think its harder than using button clicks

If column headers do exist, the item above that selected is the one that moves! OOPS!

With regard to the +1 issue in my code, it was, as I thought, done to manage column headers.
Here is an extract of the code from my Form5:

Private Sub lstImages_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    'get record in listbox by moving over record
    
     'LBRH =listbox row height - calculated in GetListboxRowHeight procedure
         
     If LBRH = 0 Then Exit Sub
     
     Screen.MousePointer = 1 'arrow
    
   [COLOR="DarkRed"][B]  intColumnHeads = Me.lstImages.ColumnHeads  'column headers visible?
     '-1 if true, 0 if false so so subtracting it adds 1 if headers shown[/B][/COLOR]
     
     [B][COLOR="darkred"]lstPos = ((Y - 45) \ LBRH) + 1 + intColumnHeads[/COLOR][/B]
     
     'Debug.Print Y, LBRH, lstPos, intColumnHeads
            
     If lstPos <> OldlstPos Then 'cursor has moved
     [COLOR="darkred"][B]   LC = Me.lstImages.ListCount + intColumnHeads '-1 to allow for header
        Me.lstImages.Selected(lstPos - 1 - intColumnHeads) = True 'highlight on mouse move[/B][/COLOR]

....

However, the code could possibly be adapted to manage column headers in a different way if needed

 

[MajP]

Here is the Lebans code. Although it does not error, it does not seem to do anything. I am 32 bit. I cannot figure anything out. I am not API proficient.

 

[isladogs]

Thanks
I've tried that in the past and it also doesn't seem to do anything at all for me.
I've also tried the original MDB. Also not working for me
I've not reviewed the code