Code too large for procedure

[TJBernard]

I have a large search form, in which I am building a query in the code.

I went to compile the code, and was suprised to receive the error "Code too long for procedure". I had no idea there was a limit, but now I know.

So, I believe I will have to break this code up into chunks, stored on at least one if not more code modules, and call the functions.

To do this, I would have to pass the values entered into the form, to the code module. Build that portion of the query Where Clause, and then return that string value back to the code on the Search form.

Has anyone attempted this before? If so, could you give me a small example of how to pass a value from a form, to a code module, process it on the code module, then pass a resulting value from the code module back to the code in the form.

Thank you for your time!

T.J.

 

[TJBernard]

I'm having trouble envisioning how much code you could possibly have to reach this limit. What is the code doing? Is your table not normalized? Do you have lots of code that repeats except for small differences?

The users wanted to search against MANY fields, on a form and 6 subforms. (about 100 fields in total). So I am going through and building a WHERE STRING based on what is entered in the many fields.

I did not realize this was even possible. If I cannot find a work around, I will have to limit the users search options.

But for each field I am writing code such as:

If IsNull(Field) or Field = "" Then
Else
If I = 0 then
strWhere = " WHERE field = " & chr(34) & Field & chr(34)
Else
strWhere = strWhere & " AND field = " & chr(34) & Field & chr(34)
End If

 

[modest]

They're searching information based on circa 100 fields!?

First problem: you're using Access for this. To do those kind of powerful ad-hoc queries you would need a better, faster, more reliable database. This, of course, also depends on table size, number of users, and how often it will be used.

Second problem: There is a possibility that your tables are not normalized to the furthest extent that they could be. This might be something to consider to prevent future performance problems (once you do get it working).

Possible solution: You might want to break it down into steps. Use recordsets and filter the information down in stages. Could you give an idea of what kind of information you are filtering. There may be a better alternative.

 

[TJBernard]

I have never broken my code up into various code modules before.

I know this is a very quick way to write repeatitive code.

But if someone can give an example of the following, I believe I can figure it out....

Take a value from a text box on a form (in my case multiple text boxes).

Pass this value to a procedure on a code module. My code module would take these value, and add to my where string.

Once the code module procedure was finish, I would pass the resulting section of the where string back to the main form, where I would then build my query in the code.

1) Is this possible?

2) Can anyone give an example of this?

Thank you!

 

[TJBernard]

They're searching information based on circa 100 fields!?

First problem: you're using Access for this. To do those kind of powerful ad-hoc queries you would need a better, faster, more reliable database. This, of course, also depends on table size, number of users, and how often it will be used.

Second problem: There is a possibility that your tables are not normalized to the furthest extent that they could be. This might be something to consider to prevent future performance problems (once you do get it working).

Possible solution: You might want to break it down into steps. Use recordsets and filter the information down in stages. Could you give an idea of what kind of information you are filtering. There may be a better alternative.

MS Access is all we have to go with. It has a SQL back-end. If worse comes to worse, and I cannot find the answers to my questions above, I will have to limit the fields the users search against.

The tables are normalized as much as possible. It is a very simple structure. 1 main table, with 6 subform tables all relating directly to the main table.

They are searching against mutliple fields, most of which appear in multiple subforms (some appearing in all the subforms).

Thank you!

 

[modest]

SQL back-end? I'm not sure what this means, is it a SQL Server Back End? No matter, if it has to be done in Access, it can be, I'm just telling you that when you have 100 different criteria you're searching on, the query will take forever. You should look at downloading Oracle. If your computer has a single processor, Oracle is free.

Anyhow, do a search on "DAO", "Recordsets", or even "DAO.Recordsets"... there will be many examples of how to open tables/queries through code. From there you can filter down to what you're looking for. An example of how to use form data in code would be:

dim rs as DAO.Recordset
dim strSQL as string
strSQL = "SELECT * FROM [table name] WHERE [field name]='" &  me.formtextbox.value & "'"
set rs = currentdb.openrecordset(strSQL,dbOpenDynaset)

where:
"me" is your form object and can be used interchangeably with forms("your form name")
"formtextbox" is the name of the textbox control on your form

forgive me if there are syntax errors.