ActiveX Listbox/Callback Function 2

[sogibear]

Does anyone know of a good free ActiveX Listbox control for ACCESS. The thing is that i need to control its data programatically from within VBA but am finding the whole Callback function thing hard going.

Thanks

 

[Steve101]

What exactly is wrong with the standard Listbox which is built into access, and available from the toolbox. Its also fully programmable. If its not suitable, then it might help if you specify what exactly it is you want to do.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[FancyPrairie]

Access ships with an ActiveX control list box (besides the one shown on the toolbar). It can be found via menubar item: INSERT|ACTIVEX CONTROL. It's called "Microsoft Forms 2.0 Listbox". I think it will do what you want. (FM20.hlp explains its usage)

 

[sogibear]

Hello,

Me.lstNotes.RowSource = FillListComboBox("ListClientNotes", ID)

I am using a function to fill up a listbox's rowsource like so. 'FillListComboBox' is a function i have written that allows me to pass a parameter into a stored procedure and use the returned recordset to fill a listbox.

For this function to work, the Listbox's RowSourcetype must be set to 'Value List' and it's rowsource property on the forms set to blank!

This in itself is ok except that Access has a 2k limit on the info contained in a 'value list'. basically if i have a big recordset it doesn't work.

As access does not have an 'AddItem' method like Visual Basic, apparently the only other alternative is to use a callback function.

Any ideas ?

Thanks

 

[Steve101]

Use a passthrough query to exec your stored procedure which returns the required records, and use the (passthrough) queryname as the rowsource for your listbox. Make sure that the RowSourceType property is set to Table/Query.

To get this working, set up and test the passthrough query first. Then when its working, 'connect' it to the form via the listbox.

You get to Passthrough queries via the Query, SQL Specific, Passthrough options of the Query Design window. Enter the required sql/SP invokation in the window, and ensure that the query properties (View, Properties option) are set appropriately (in particular, the ODBC connection string, ReturnRecords set to Yes, you may need to set the ODBC Timeout to zero.

Hope this approach sorts it out,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[FancyPrairie]

By the way, the ActiveX Control (Microsoft Forms 2.0 Listbox) does have the AddItem method. And several other cool features.

 

[sogibear]

Cheers,

however when the form is in 'form mode' the bottom of the activeX listbox is often distorted, also the special effects don't really work, must be a bug or something. Not that i'm complaining !

Do you get the same weird problems when using it ?

Thanks

 

[FancyPrairie]

No I haven't experienced any of those problems. Works fine for me.