Problem with Strings 4

[Datathumper]

Hello,

Here is my problem. I have a list box that you select items from and populate another list box. The second list box populates a report. This is done using a "string" coding. The problem is when I exceed a certain number of records in the list box (usually about 120 records), the report no longer filters. I assume that this is due to me exceeding the number of characters (255)of the string...

No being that familiar with VBA, is there another method that I could use? Can I define whether or not to allow more than 255 characters in my string...

...given enough string, I could hang myself...lol

Datathumper

 

[RoyVidar]

cgarts,
I've read that too (call back function "circumvent maximum limit of a Value List", quoted from the below link), both in my reference (Getz et al) and Forms: Callback function for a Multi Column List box, but I've never tried it, but I believe you might be correct. Have you tried it?

TheAceMan1:
I'm getting the error in a2k, not xp (I'm running my a2k without any service packs for test purposes, if that should matter).

All:
In fact, this lead me to do some more testing, it seems the limit of the rowsource in xp is a bit changed, or closer to non existant. I didn't bother inputting more than 12K characters, but it worked like a charm ( - well if you find slow loading of the list charming;-)), both using the .AddItem method (new in xp) and concatinating the string - so, again I was wrong in previuos reply, it didn't fail ingraciously, but succeeded! (It's a pity most of my current apps need to work both on a2k and xp, else...)

It seems we've all forgotten Datathumper's question in this pursuit of listbox capabilities and limitations, hows the progress?

Roy-Vidar

 

[TheAceMan1]

Nice Piece of work Roy!

I owe [blue]cgarts[/blue] an [purple]apology![/purple]. I thought he was referring to something else.

I'll be doing more testing to qualify findings here. The [blue]CallBack Function[/blue] looks good, but like you, I've never used this particular function.

As for [blue]Datathumper[/blue], he has enough here to make a decision. I think we should wait and see what that is.

In any case, stars are deserving here, espcially [blue]cgarts[/blue] who was right on target.

Remember Our Veterans! Remember Sept 11th!

 

[scriverb]

Some much to absorbe here with all the version to deal with. Roy and AceMan: Thanks for the testing a new info on these subjects.

DataHumper(I really like that handle): If you choose to use my last suggestion here is some example code to use.

1. Add a new field to your table called Selected(Yes/No boolean type)

2. Two new saved queries. qryAll and qrySelected.

qryAll

SELECT A.ID, A.Selected
FROM [i]yourtablename[i] as A
ORDER BY A.ID;

qrySelected

SELECT A.ID, A.Selected
FROM [i]yourtablename[i] as A 
WHERE A.Selected = True 
ORDER BY A.ID;

3. Create a continuous subform with your ID field and the Selected field. Vertical scrollbars. The RowSource for this subform should start out as qryAll. Create a command button on this form to toggle the subform data from all records to just the selected ones.


VBA code for a command button. Start Caption for button off as "Show Selected"

Private Sub Command1_Click()
If Me.Command1.Caption = "Show Selected" Then
    Me.Command1.Caption = "Show All" Forms![frmMainForm]![MainForm_SF_Control].Form.RecordSource = "qrySelected"
    Me.MainForm_SF_Control.Requery
Else
    Me.Command1.Caption = "Show Selected"    Forms![frmMainForm]![MainForm_SF_Control].Form.RecordSource = "qryAll"
    Me.MainForm_SF_Control.Requery
End If
End Sub

4. Now after the selection process is complete the report would be run with a query as its recordsource that looks something like this:

Select A.* FROM [i]yourtablename[/i]
WHERE A.Selected = True;

5. Add in the sort order and that should just about do it. Oh, also after you execute the running of the report run some SQL to update the tables Selected field back to all False.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

I just followed the link to the CallBack function and that really looks great. TheAceMan is correct. Will certain add that to my bag of tricks. Thanks, cgarts.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Datathumper]

First off...scriverb, sorry to hear of your father's passing. I doubt if anyone will forget the sacrifices made by our brave.

Now, getting down to business...you guys are really amazing...although most of this is WAY over my head, there is alot of good advice to be had here.

Sticking to the basics, I am going to give scriverb's suggestion a shot. Thanks scriverb for the code and input. Thanks everyone else for the input as well.

I printed off the callback function link and am going to have a look at that..looks pretty interesting.

I will let you all know how things pan out.

Thanks again
Datathumper

 

[cgarts]

Thanks AceMan.

A star from TheAceMan is ... well, a star from a star...

I ran into the issue with the 2048 character limit a while ago.

I've Googled around quite a bit, using search strings like "2048 limit access listbox rowsource." To something of a novice like me, it looks like "User Defined Callback Functions" are the keys to the castle for empowering listboxes. I'm just starting to investigate and practice using them. Lets just say I'm not fluent yet.... I'm going to have to really learn arrays to make use of callbacks. Despite an esoteric syntax, callbacks look like they can make a listbox do almost anything.

For others to get some idea of what callbacks are about:

http://www.saicomsystems.com/AccessPearls/mod-01.asp

shows an example of a User Defined Callback Function.

In:

http://www.hammerdata.com/Newsgrp/310.html

Dev Anish shows one with multiple columns and column heads.

Best to all,

C

 

[CajunCenturion]

I know I'm coming in late, but callbacks do work for Listboxes. I'm using them in a couple of places, and you have to set it up right.

I've only come across one thing that may present a problem, and that is if you do something inside the callback function which in turn generates other Form events, things may not turn out as you would like.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein