MS Access: custom control built in another language

[deletedT]

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

Maybe your backup is different with what you uploaded.
I downloaded MAJP FAYT V6.accdb again. Still the same errors as above.

 

[MajP]

I downloaded MAJP FAYT V6.accdb again. Still the same errors as above.

Thanks for the Idea. I will combine the two classes into 1 so you can choose to find as you type (filter) or search as you type (no filter). I never really finished the search as you type class, thus the errors.
I cannot replicate your errors with the international characters. How are you testing is.

 

[deletedT]

I cannot replicate your errors with the international characters. How are you testing is.

MajP

Sorry for the Delay. I was away from my desk.
I downloaded MAJJP FAYT V6.accdb from post #57 above, opened it, Closed the form that was opened, double click and opened frmFAYTcomboDemo form.
Then typed "a" in the first combo box (Productname).

Without all above steps, you can open vba editor (Alt+F11) and compile the file.

In either cases you will see the error.



In case of the second error message, Open frmSAYTListBoxNoClass, and click Product Name (the one with a blue background).


I was trying to change your FAYTform class and filter the form with two or more text boxes to be able to filter different fields at the same time.

 

[ironfelix717]

Following up with some comments/questions.

MajP: Thanks for the class examples. I will provide my updated code within a few days. Currently implementing the class into my main project to test what its practical uses are and how it might be improved. So far, so great.


Also, to the group:
I am looking for another control and its similar to what has been solved here, so i thought I would ask... I need a listbox where the value can be edited directly in the control. If I can just get an actual control to do this, i can obviously build a class like MajP did here for the ListBox and go from there. Does anyone know of any third party controls???

Sonic, you and Colin mentiond Common Controls, but I'm not sure what a "Common Control" is? If these are installed, will they show up under Active X control in Design mode??

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

Regards,

 

[MajP]

mscomctl.ocx is an ActiveX Control module that contains common controls used by Windows, such as ImageCombo, ImageList, ListView, ProgressBar, Slider, StatusBar, TabStrip, ToolBar, and TreeView controls. mscomctl.ocx is a system process that is needed for your PC to work properly

These Active X controls can be a pain to register. They are really temperamental. If you are distributing your application, I would say it is not worth it. You can download mscomctl.ocx from the web. Not sure if that is the name of the 64bit version. That is why a lot of us try to "roll our own" fake controls.

I need a listbox where the value can be edited directly in the control.

What makes that different from a continuous form? what features would this listbox need that is not in a continuous form.

There use to be another activex control control called a Flexgrid. This is in my opinion one of the biggest shortfalls of Access in that there is not an unbound grid control. If you ever coded in .net you would see that the grid control is super capable and can solve a lot of these problems. I do not think the Flexgrid is supported anymore. You can google MSFLXGRD.OCX.

Could you have a row of unbound textboxes above or below the list to update the selected row? All the existing code would easily support this. Then as you move in the list the textboxes show the current values. After update it would "edit" the row, which is simply a delete and insert at the current index. Or could this be a pop up on double click? You could also pop up just that "cell" value.

One final option would be a highly modified version of what you were trying to do with your original form. Instead of many unbound textboxes, you would make this a bound form to look like a listbox. You then take the rowsource and create an in memory ADO recordset and fill it with values from your rowsource. Now you basically have an "unbound" grid control. Here is a partial demo, of something I was trying. I was working on drag drop with this, but never coded it.

 

[isladogs]

Flexgrid still works but only in 32-bit Access. Its an amazingly useful control but has AFAIK still not been ported to 64-bit... more's the pity!

 

[ironfelix717]

Hello All,

Returning with some final followups for the thread.

Attached is my final revision for the ListBox drag-drop class. Some quick comments on changes/improvements:

• 
  - Fixed TableFromSQL bugs. Should now return the table name accurately from any SQL statement passed to it.

• 
  -TableFromSQL still does not support QueryDefs. I did not have time to test with query defs, but i cannot see why it would not work if code was removed that verifies the the returned string is located in the table defs. Life can be simple if user just passes a SQL string to the listbox rowsource instead of a query def.

• 
  -Added Re-Index method: It re-orders the listbox from 0 to .recordcount. This happens on initialize and can be useful to call when you delete a record from the underlying rowsource. It ensures no gaps are present in the order field.

• 
  -Added Re-Query method: Simply calls converttovaluelist again.

• 
  -Did not combine MajP's latest update - short on time.

Other Topics:
So, i've been looking into the Grid Control solutions (with respect to 64 bit). Nothing really exists. The common controls ListView control is ancient, poorly documented, and doesn't allow all rows to be edited - only the primary row if i'm not mistaken. So that's out of the question.

The next would be a grid control from VS compiled to Active X control (i.e. custom control) which would be beyond my experience and questionable to be maintained over the life of the application. So thats a road I don't want to go down.

So, i revert to my original solution, which is a custom form. Similar to MajP's example. I rebuilt/optimized the code. This "control" is unique to my specific project and not very versatile as it has been written. For those reasons, i didn't share the code, but I do have a video showing it can be done (attached).

Thanks to all that helped with the listbox drag-drop functionality.
Regards

 

[isladogs]

Just ran a quick test & got an error
Move the first item to the bottom, then back again. Now move it to the bottom again. This triggered error 6015 - can't add the item. The index is too large.

Its late here and I'm about to log off so not investigated the cause of the error

 

[ironfelix717]

Just ran a quick test & got an error
Move the first item to the bottom, then back again. Now move it to the bottom again. This triggered error 6015 - can't add the item. The index is too large.

Its late here and I'm about to log off so not investigated the cause of the error

Colin,

I have received this error in my main project once or twice when testing and was unsure exactly what the issue was because i couldn't reproduce it.

I have just tested again with your description and received the first time i tried, but every subsequent time after i couldn't reproduce the issue. If you can reproduce this consistently, please let me know and I will investigate.

Thanks

 

[deletedT]

Colin,

I have received this error in my main project once or twice when testing and was unsure exactly what the issue was because i couldn't reproduce it.

I have just tested again with your description and received the first time i tried, but every subsequent time after i couldn't reproduce the issue. If you can reproduce this consistently, please let me know and I will investigate.

Thanks

I checked ListboxDragDropper v4.0.accdb.zip three times and received the following error every time.

Test No1 & 2:
I opened the db, openned the form, dragged the first item to the last and I received the error.

Test No3:
I dragged the first item to the last, No error. Dragged it back to first, Still no error. Once again dragged it to the last place then the error occurred.

Office 2016 x64

 

[ironfelix717]

Tera,

Thanks for testing. I still cannot reproduce the issue. I even tried compact & repair with no chance of an error. Hmm.

See attached video.

2019 x64 Pro Plus

 

[deletedT]

Tera,

Thanks for testing. I still cannot reproduce the issue. I even tried compact & repair with no chance of an error. Hmm.

See attached video.

2019 x64 Pro Plus

It's mine:

Edit: not enough space error.
I uploaded to our server.
http://kkkotobuki.co.jp/downloads/1.zip
http://kkkotobuki.co.jp/downloads/2.zip

 

[ironfelix717]

It's mine:

Unsure what you mean

 

[deletedT]

Unsure what you mean

Sorry.
Forum's limit is 2MB.
The files were 6MB.

I had to uplaod them to our server.

Note: Unlike you, I didn't click reset button, nor anything else. Opened the db, then the form and dragged the items.

 

[isladogs]

Hi
I see this has moved on overnight.
The error is consistent for me. Tested on two different PCs. Happens every time.
As Tera showed, error in AddItem procedure. Don't know if that's your code or MajP's

I also notice that (like my original code for GetListboxRowHeight that MajP adapted) this still gives unreliable results if the list has a scrollbar.
I don't have an answer for that issue either but it does significantly reduce the value of this feature in my opinion.

 

[MajP]

As Tera showed, error in AddItem procedure. Don't know if that's your code or MajP's

It is likely mine based on where it is. The trick is to always add to the list before deleting from the list (most times). My guess without looking is in that event add something to add to the end if greater than the listcount - 1.

if ind > me.listbox.listcount - 1 then
ind = me.listbox.listcount - 1
end if

 

[deletedT]

It is likely mine based on where it is. The trick is to always add to the list before deleting from the list (most times). My guess without looking is in that event add something to add to the end if greater than the listcount - 1.

if ind > me.listbox.listcount - 1 then
ind = me.listbox.listcount - 1
end if

I tested 10 more times. 6 times I received the same error.
4 times the error didn't show up.

Anytime I receive the error, If I stop the code, Close the database and open it again, the error doesn't show again, no matter how many times I drag the items.

 

[isladogs]

if ind > me.listbox.listcount - 1 then
ind = me.listbox.listcount - 1
end if

That doesn't work as it moves the item to the penultimate record instead.

I tried this with some success:

Public Sub addItem(ind As Long, val As String)

    If ind > Me.ListBox.ListCount Then
        ind = Me.ListBox.ListCount
    End If
    
    Me.ListBox.addItem val, ind
End Sub

I also added error handling to that sub

I no longer get the previous error. However after dragging/dropping a few times I now get error 3021 - no current record - in UpdateRecordset.

I added error handling to manage that error AND abracadabra ... after repeated testing I think it now works without further issues (famous last words)

Version 4.1 attached -
I've also enlarged the listbox font as it was too small for me to work with before ...however I've ensured the scrollbar isn't needed

 

[ironfelix717]

I also added error handling to that sub

I no longer get the previous error. However after dragging/dropping a few times I now get error 3021 - no current record - in UpdateRecordset.

I added error handling to manage that error AND abracadabra ... after repeated testing I think it now works without further issues (famous last words)

It looks a lot like this is stemming from Wizhooks and consequently producing unreliable behavior in GetLBRH.

I still cannot produce the issue no matter what happens. My system is executing this 100% reliably - with a fresh re-download of v4.0 from this forum, compact/repair, reboot - nothing affects the issue for me. The same is true with v4.1 - no errors. (vid attached)

It is true this doesn't execute correctly with scrollbars, but only when the drop index is so great that it forces the listbox to scroll. As soon as the LB starts to scroll, then you can expect the drop item to land at the last visible index before the scroll happened -at least that seems to be the case. Not a concern for my use, but good to make note of.

Only question I have with v4.1 is, why does the handler in additem check for Err # 3021, when the error in question has been Err 6015?

 

[isladogs]

What evidence do you have for your first comment about Wizhooks?
I don't see the issues are related at all. If you know otherwise, please enlighten me.

You said earlier that you had experienced the 6015 error both in your main project and this one

As for the error handling, I'd already 'solved' error 6015. Added the 3021 code to AddItem by mistake and forgot to remove it. Its needed in UpdateRecordset however.

I'd recommend adding error handling code to every procedure.