Need help guys!

[Uncolouredcolors]

Hey.

So, i have a searching form and when i search for some words i get the "You have exceeded 32k buffer for matching records. Try differente search string". Which on the vba is:
"cmdSearch_Click_Error:690 If Err.Number = 2176 Then
700 MsgBox "You have exceeded 32K buffer for matching records.(" & lngCount & ") Try different search string."

How can i solve this? Can i change the 32k buffer to a larger number?



Thanks!!!

[orange]

My recommendation is to use more discriminating search terms. That is, if you are trying to find a few things out of many, then use a more unique term(s). If you are trying to get counts of various words/terms, then maybe you need another strategy.

If you are searching for "strings", then pick something less common.
32K is a lot of matching records--please tell us more about your search.

[Uncolouredcolors]

Well, let's see if I can explain: I'm searching for a particular word(s) but those appear in more than one table. They're like 22 tables and some tables have more than 400 lines with alot of text. This is like a data base for people to search for particular words/date/and other stuff on a particular theme. It has a lot of info. It's like a library about one specific theme. I don't know if this helps u too understand.... And i'm not that good with VBA, so...

[orange]

See my sample for ideas at
https://www.accessforums.net/code-re...rch-43055.html

[Uncolouredcolors]

I know, Orange.

I'm using that one! The universal search

[orange]

So what exactly do you need different. Perhaps it can be adjusted.

[Uncolouredcolors]

Well, I really don't know exactly how to say it. I have changed somethings but none of them interfers with the searching method. The only error i'm getting is that one. I need to search for a word but it has a lot of matches and it gets that 32k error. And I don't want to restrict the searching method because it needs to be like this. So people may search for that single word and all the results appear. All the other words I search appear but they have less matches, but some of them, appear a lot. I don't know if i'm explaning it right... sorry

[orange]

Can you post your database?

[Uncolouredcolors]

I can't. It has a lot of information that I don't have the permission to share. I can put the code tho. But like i said, i haven'te changed important things. It's similar to yours. The difference is, I have a lot of info and when I search for some words it does that error of 32k and it doesn't show all the matches. All I want to know if is it possible to change that value to maybe 64k or something like that or if i have to change anything.

[orange]

I don't know if there is a parameter you could set. I haven't found one, but haven't looked too hard. I suppose you could write the found info to a table instead of holding it in a string variable.

In a small test of my demo, I can search for "." (single period) and it will fail on buffersize. I can debug and see the buffer increasing toward the 32K limit.

FldLeng  NumHits
 32249  363 
 32333  364 
 32418  365 
 32519  366 
 32607  367 
 32691  368 
...and it fails on the next attempt to concatenate more to the variable

For each hit, I am creating a row (value list) for the listbox and that is what is causing the variable max to be exceeded.
eg:
strListRowSource = strListRowSource & ";" & strTableName & ";" & strIdentifierValue & ";" & strIdentifierName & ";" & strFieldName & ";""" & strFieldValue & """;" & strIdentifierType & ";" & intIdentifierPosition

If I recall correctly, when testing originally, I had the 2176 error when searching for common string, so decided to highlight the error. I did not try to extend the buffer.

The exercise was meant as a simple way to look for a string(s) across all non system table fields (subject to a few constraints PK...)

[orange]

I have done a little testing. I can process the whole search against a single "." and not get the buffer error.
I have
-changed from using a value list as listbox rowsource
-bypassed writing to a variable
-written the results to a table
-table used as rowsource of listbox
-table has to be dropped and recreated
-is considerably/noticeably slower
-should do routine compact and repair to recover space because of delete/recreate table.

Some stats looking for single "." on my data
UsysResults Dropped
UsysResults Created
No Primary key on tbl_Spl_LCL_Std_EstAddressLocation skipping
No Primary key on tbl_Spl_LCL_Std_EstAddressMailing skipping
No Primary key on tbl_Spl_LCL_Std_EstProdDetailsEng skipping
No Primary key on tbl_Spl_LCL_Std_EstProdDetailsFre skipping
processing was 5281 milliseconds against 7931 fields.
hits found = 830


Search parameters for latest search ( uses OR condition across strings)
man#japa#montreal#.com#0

Stats for run that returned 1565 hits

01/09/2015 3:31:23 PM    UsysResults Dropped
01/09/2015 3:31:24 PM    UsysResults Created
No Primary key on tbl_Spl_LCL_Std_EstAddressLocation skipping
No Primary key on tbl_Spl_LCL_Std_EstAddressMailing skipping
No Primary key on tbl_Spl_LCL_Std_EstProdDetailsEng skipping
No Primary key on tbl_Spl_LCL_Std_EstProdDetailsFre skipping
processing was 6484 milliseconds against 7871  fields.
hits found = 1565

My test Database went up to 1.2 Mb after 10 or so runs.
Compact and repair brings size back to 812Kb

@uncolouredcolors,

Let me know your status and need, I have a test copy of the database that gets by the 32K.

[orange]

Duplicate???

[Uncolouredcolors]

Hey orange! I'm sorry I didn't knew about the cross post! sorry!!

[Uncolouredcolors]

Wow you did it? What do I need to change? Is it too complicated? I'm a basic user! Thanks man!!!

[orange]

I have a version that I did modify. It was really just to see/demo that I could get all of the hits and not be stopped by the 32K string size.
The version I have is a little rough.
What exactly are you searching and searching for?
What experience with Access do you have?
Do you understand compact and repair?

As per PM let me know when you have received.