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

 

[scriverb]

Why don't you post the code for building your filter string. Also, copy and paste the string that is finally built so we can see what it looks like.

[COLOR=006633]Bob Scriver[/color]

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

 

[TheAceMan1]

How are ya Datathumper . . . . .

If your using [blue]Value List[/blue] (and it appears you are), there's a [blue]2k limit[/blue] (2048 characters including headers if enabled, semicolons & spaces).

 

[Datathumper]

Here is the code that does it...giving full credit to bytemyzer for helping me out with this code in the first place. It works great until I get a certain number of records selected, then it prints all records in the table...

The following code moves the selected items from one list box to another...

Dim VaxListCounter As Integer, VaxCurrentCounter As Integer
Dim VaxListItems As Integer, VaxCurrentItems As Integer
Dim ListStr As String, FoundInList As Integer
VaxListItems = [Drawings].ListCount - 1
VaxCurrentItems = [current drawings].ListCount - 1
For VaxListCounter = 0 To VaxListItems
If [Drawings].Selected(VaxListCounter) = True Then
If IsNull([current drawings].RowSource) Then
ListStr = [Drawings].Column(0, VaxListCounter) & ";"
[current drawings].RowSource = ListStr
Else
FoundInList = False
For VaxCurrentCounter = 0 To VaxCurrentItems
If [current drawings].Column(0, VaxCurrentCounter) = [Drawings].Column(0, VaxListCounter) Then
FoundInList = True
End If
Next VaxCurrentCounter
If Not FoundInList Then
ListStr = [current drawings].RowSource & [Drawings].Column(0, VaxListCounter) & ";"
[current drawings].RowSource = ""
[current drawings].RowSource = ListStr
End If
End If
End If
Next VaxListCounter

Then when the report opens...it applies the following code:


If IsLoaded("frm_multiselect") Then
For curCount = 0 To Forms!frm_multiselect![current drawings].ListCount - 1
curItem = curItem & IIf(Nz(curItem, "") > "", ",", "") & """" _
& Forms!frm_multiselect![current drawings].ItemData(curCount) & """"
Next curCount
Me.Filter = "[dn] In (" & curItem & ")"
Me.FilterOn = True
End If



I am assuming that the problem is due to the liststr exceeding the number of characters for a string....am I wrong in this assumption?

Datathumper

 

[scriverb]

Why not take the selected items and write them to a temp table and then change the recordsource for your report to a query that links the criteria field in your primary table to the selected field of the temp table with an INNER join. This way only the records in the temp table will be selected.

Each time you run the run report the temp table is cleared and refilled will the selections.

Let me know if this sounds like it will work for you. I can help with that coding.

[COLOR=006633]Bob Scriver[/color]

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

 

[Datathumper]

The temp table sounds good...,the reason that I went with the filter on the query is because of the number of different fields in my report and the fact that I was pulling information from two different tables for the report.

The list box was just a away of adding user selection filtering to the report...this way the user can select drawings to list on the report and is not bound to a generic report.

I would not know where to start with the coding, however, and sure could use some help if you dont mind.

It sounds like your plan would be to still use a similar query, but instead of using a filter, use the temp table with a relationship to filter the records...am I close?

Datathumper

 

[scriverb]

Yes, I would still use the ListBox for the user selections but rather than populate another list box and then build a string variable with the selections I would write them to a table. The query would them link to the table to make the selections. I can't work on this right now but will do so later and post some sample code for you to use.

[COLOR=006633]Bob Scriver[/color]

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

 

[RoyVidar]

I agree with scriverb in not populating the rowsource of the second listbox seem to be the way to go, because of the 2k limit of the rowsource when using value list as row source type.

But, could it be an alternative to change the first listbox to multiselect, and drop the second listbox entirely? See, creating (and deleting) temptables create database bloat, and need for frequent compact.

Here's a thread on usage of a multiselect listbox and passing similar criteria to a report thread702-787778.

Roy-Vidar

 

[cgarts]

Could a callback function get around the 2K limit?

C

 

[TheAceMan1]

cgarts . . . . .

Good Idea, but No. The limit is hard coded . . . .

Remember Our Veterans! Remember Sept 11th!

 

[TheAceMan1]

Datathumper . . . . .

I query the functionality of the 2nd LB as a storage facility. Unless its for testing, I fail to see the necessity. Besides the fact that it takes up unnecessary space, which could be used for future additions.

In any case, I agree with [blue]scriverb[/blue]. A temp table easily solves your problem, is easy to install, will ease the complexity of your code, and can be done in near minimum design time. For each run of your reports, the table is easily cleared with a single line [blue]Update Query[/blue]. And The temp table could be used for other purposes as well! I would say about 1/3 of the DB's I've designed have at least one temp table.

[blue]RoyVidar's[/blue] suggestion is an excellent alternative. [blue]Note how the second CB is iradicated here as well! [/blue]Only query I have here is wether or not the multiselect CB will become confusing to your users? Other than that, [blue]Roy's[/blue] suggestion is about as close as your gonna get without a temp table.

So . . . . . . . . Ya Think?

Remember Our Veterans! Remember Sept 11th!

 

[RoyVidar]

I'm starting to wonder whether it might be the filter property of the report causing the challenges, not the listbox rowsource (well, both are/might be a problem, but since the filter is slightly longer than the rowsource...).

When reading the thread a bit more carefully (which I should have done yesterday), it seems theres not a problem populating the second listbox, but using the contents of it in the filter property of the report.

The length of a string variable can be rather long (approx 2 billion characters for variable length string, I think), so the problem is not there.

But with a very quick and dirty test here right now, I'm not able to set the filter property of a report to more than 2048 characters either.

I'm just a bit stumped by you not reporting any errors on the second listbox. I think that should error out on one or two items more than the report filter takes.

How to solve?
Temp table (with the "bloat" consequence) and a join with the original table, as suggested by scriverb, would probably be the easiest, If you haven't got a "shorter" unique field in the rowsource of the first listbox. But, when selecting more than 100 items, I see the point of having a second listbox.

Roy-Vidar

 

[TheAceMan1]

Roy . . . . .

I thought about that too. Problem is, when the CB RowSource is over limit (specifically for Value List), it doesn't raise an error, [purple]it turnicates![/purple]. As Idea that actually happens: if the CB has many columns, you'll see them start to disappear, one by one, the more the Row Source string goes over 2K! Really! I handled a post some time ago for just this response.

But if Filter is cutting off at 2K, then I would fault the CB . . . . It just has no way of letting one know other than visual . . . . . By the way, I didn't know the filter cutoff at 2K! I gotta check that out . . .

Remember Our Veterans! Remember Sept 11th!

 

[Datathumper]

WOW, I cannot believe the reponse to this question...thanks ALL for the help.

I think that the answer to my questions is either the temp table as scriverb suggested, or as was also suggested, ditch the second list box and simply go with the selected values from the first box to populate. The problem is that when selecting alot of items in the list box, if the user forgets once to hold the shift or control key when selecting more than one item...its back to the start for them (frustrating for some users), this is why I chose to move values to another box.

As far as the filter limiting, I did some testing with just using one list box and multiselecting the items, then using the multiselected items as the filter. Here I ran into a different problem...it would allow fewer records to be used in the filter. But when exceeding the number of records allowed, instead of printing ALL records like in the second list box problem, it would only print a few records....am I losing it?

This brings me back to the conclusion that scriverb's temp table is the only answer because then I am not depending on a filter, but now on a relationship to control the number of records in the query.


You have to forgive me, I am somewhat of an amateur developer (self taught), so I dont know all of the ins'n'outs of VBA yet...

...any input?

Datathumper

 

[cgarts]

Aceman,

I'm a novice so I ask this to be better informed. I am hoping soon to learn more about, and to use callback functions since callback functions are presented as not being subject to the 2048 character limit. Is the limit you are referring to elsewhere in the process, or am I reading the info on the web too optemistically?

C

 

[willir]

Datathumper

When you build your multiple list, have you thought of building a WHERE clause at the same time and pass the WHERE clause to the report. You might have to parse the multiple list to accommodate variable using numeric, string and date, but it should work. If this works, then you can get away from the maintenance issues of a temp table. (Note: Temp table will work for many situations - may run into issues in a multi user environment if you are not careful)

Good posts Bob, Ace and Roy.

Richard

 

[scriverb]

Wow!!! I have been away for awhile visiting my fathers (Omer Scriver) gravesite. WWII vet from the Battle of the Bulge. Wounded in Battle and died just a year ago. I just wanted to say to all that I am proud of him and all the others that have given the ultimate sacrifice for our country and freedom. Thanks Dad.

That said I am surprised by all that great ideas and info here in this thread. The temp table thing would work really great in my opinion and the bloat issue is fairly small and doesn't affect others on the server as it would only be in the FE database that it occurs. Simple periodic compacting takes care of that problem.

But, I have used the following technique for this type thing before and it works quite well. One of the issues here is to to allow the end user to review the records that they have selected. This is probably a good idea as a report without a particular selection that should be there looks like there were no records available for that group. I usually add an extra Boolean(yes/No) field to my key tables that I then use that field in a subform to act as a multi-select list box. This allows for the user to make selections of records and review what has been selected and then using the check-boxes as the WHERE criteria for RecordSource for the report. Eliminates the need for all the movement of selected items and such.

In addition I usually provide for a command button that modifies the RecordSource for the subform to a query that selects only those records selected so that user can review their final selections. This command button toggles back and forth between the full listing of possible record selections and a queries shorter version of just those selected.

Now a simple Select query with an expression selecting only those selected records provides the Report with the correct records that the user has indiciated.

Depending on which way you choose to go here it is obvious that there is a ton of help to get you to your final solution.

Post back with any comments on this idea.

[COLOR=006633]Bob Scriver[/color]

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

 

[RoyVidar]

Yup - scriverb, the boolean field is, in my opinion, so far the best suggestion in this thread!

But back to the listbox rowsource, reason I wondered about an errormessage, is that I've been working on my a2k setup lately, and got the runtime error 2176, "The setting for this property is too long", popping up starting at rowsource length of 2049 on a project.

I incorrectly believed it was because I'd used a larger rowsource in that test, then previously on my xp setup, but found out just now, on my xp setup, it fails ingraciously (I'm guessing that's what you refer to TheAceMan1, I just couldn't find "turnicate" in my dictionaries), without any notice.

And I'll have to agree, this is a rather interesting thread!

Roy-Vidar

 

[TheAceMan1]

cgarts . . . . .

[blue] Is the limit you are referring to elsewhere in the process, or am I reading the info on the web too optemistically?[/blue]

No, the limit is a [purple]Specification[/purple] of the Control Property. In this case the combobox RowSource when the type is to Value List. Understand, I've never seen or heard of a quote from microsoft to this fact, nor the great many searches I've done. Its only been thru testing over the years that this has proved out. For quite a few things like this, there are specifications to never know about until you run up on them. [blue]RoyVidar[/blue] mentioned a 2k limit on [blue]Filter String Length[/blue]. I had no Idea (I rarely use filters), and I've been programming Access since Version 1.0.

During the process (in this case loading a string into the RowSource), Access checks the length against the spec and takes the approiate action.

No matter what you do, specs are specs and you can't program around them. Consider them as [Purple]Locks[/purple] or [purple]Ceilings[/purple] which you cannot violate.

Also notice in this thread, there is an apparent version conflict. [blue]RoyVidar[/blue] has stated he gets an error on going over the combobox rowsource string length in XP. I'm using 2K and do not. Apparently this has been taken care of in XP.

I have certainly added to my library from this thread.

Remember Our Veterans! Remember Sept 11th!

 

[TheAceMan1]

scriverb . . . . .

[purple]I bow to your father in prayer . . . . May they all never be forgotton! . . . .[/purple]

Yeah . . . . Right-On scriverb. I've come to always include a [blue]Print manager Form[/blue] in my designs. Its job is to take care of all query, selection ([purple]via binary of course[/purple]), and method of printing. Its a little more work, but its always been worth it. I've always been complimented on the form & printing.

I find this works very well, particularly since all printing & code is consolidated to one area. I have a Template Form I use with some common code thats easy to modify according to needs (cuts down design time). At the same time, we get rid of all those buttons & lists throughout the forms and now have room for other important & future functions.

Remember Our Veterans! Remember Sept 11th!