REPOST: QBF "Not enough memory..."

[Adam]

Okay, so after a while i realized a solution to what i need to do with Access
is Query By Form.

I have a table with 15 columns, and 48 rows (entries). So on my form i have
15 unbound combo boxes, each pertaining to a column in my table and a
"search" command button. I have used the following web page to create an
appropriate macro, command button, and query:
http://support.microsoft.com/kb/304428/en-us

Since my query contains 15 columns (in design view) and in the criteria of
each one i have the following (from the above web page, this code for my
RigName combo):
Criteria: Forms![QBFMODU]![RIgName] Or Forms![QBFMODU]![RigName] Is Null


The form is being created to allow the user to select a value from one, a
few, all or none of the combo boxes, and click "search" and the results
matching the criteria are displayed. When selecting a value on my form and
clicking search i get the standard "there isnt enough memory... close
unneeded programs.... etc." Also, i can not even open my query in design view
or any view for that fact. The method i used DOES work as i tried it with 3
combo boxes and it worked very nicely.

I realize the query i have built ends up being pretty complex and probably
overloads what Access can do, but is there a way around this memory problem?
Any suggestions are appreciated!

Thanks,
Adam.

 

[Allen Browne]

15 combos should not chew up all your computer's memory. Something else must
be going wrong here.

In any case, here's an alternative approach:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article contains a sample database you can download to see how it works.
Essentially, it builds a Filter string from just the boxes where you
actually entered something. This makes the process much more efficient and
trouble-free to execute.

 

[Adam]

I appreciate it, but i have already used this link and actually built a form
based off of it, it was helpful. But I would still like to use the
query-by-form approach.

15 combos should not chew up all your computer's memory. Something else must
be going wrong here.

In any case, here's an alternative approach:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article contains a sample database you can download to see how it works.
Essentially, it builds a Filter string from just the boxes where you
actually entered something. This makes the process much more efficient and
trouble-free to execute.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, so after a while i realized a solution to what i need to do with
Access
is Query By Form.

I have a table with 15 columns, and 48 rows (entries). So on my form i
have
15 unbound combo boxes, each pertaining to a column in my table and a
"search" command button. I have used the following web page to create an
appropriate macro, command button, and query:
http://support.microsoft.com/kb/304428/en-us

Since my query contains 15 columns (in design view) and in the criteria of
each one i have the following (from the above web page, this code for my
RigName combo):
Criteria: Forms![QBFMODU]![RIgName] Or Forms![QBFMODU]![RigName] Is Null


The form is being created to allow the user to select a value from one, a
few, all or none of the combo boxes, and click "search" and the results
matching the criteria are displayed. When selecting a value on my form and
clicking search i get the standard "there isnt enough memory... close
unneeded programs.... etc." Also, i can not even open my query in design
view
or any view for that fact. The method i used DOES work as i tried it with
3
combo boxes and it worked very nicely.

I realize the query i have built ends up being pretty complex and probably
overloads what Access can do, but is there a way around this memory
problem?
Any suggestions are appreciated!

 

[Allen Browne]

If you want to use the kb approach:

a) Explicitly declare each of your 15 parameters (Parameters on Query menu.)
Delete the matching data type for each field. This may well fix the memory
error.

b) Change the WHERE clause of the query (in SQL View), so that each one
reads like this:
WHERE ((Field1 = Forms![QBFMODU]![RigName])
OR (Forms![QBFMODU]![RigName] Is Null))
AND ((Field2 ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I appreciate it, but i have already used this link and actually built a
form
based off of it, it was helpful. But I would still like to use the
query-by-form approach.

15 combos should not chew up all your computer's memory. Something else
must
be going wrong here.

In any case, here's an alternative approach:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article contains a sample database you can download to see how it
works.
Essentially, it builds a Filter string from just the boxes where you
actually entered something. This makes the process much more efficient
and
trouble-free to execute.

Okay, so after a while i realized a solution to what i need to do with
Access
is Query By Form.

I have a table with 15 columns, and 48 rows (entries). So on my form i
have
15 unbound combo boxes, each pertaining to a column in my table and a
"search" command button. I have used the following web page to create
an
appropriate macro, command button, and query:
http://support.microsoft.com/kb/304428/en-us

Since my query contains 15 columns (in design view) and in the criteria
of
each one i have the following (from the above web page, this code for
my
RigName combo):
Criteria: Forms![QBFMODU]![RIgName] Or Forms![QBFMODU]![RigName] Is
Null


The form is being created to allow the user to select a value from one,
a
few, all or none of the combo boxes, and click "search" and the results
matching the criteria are displayed. When selecting a value on my form
and
clicking search i get the standard "there isnt enough memory... close
unneeded programs.... etc." Also, i can not even open my query in
design
view
or any view for that fact. The method i used DOES work as i tried it
with
3
combo boxes and it worked very nicely.

I realize the query i have built ends up being pretty complex and
probably
overloads what Access can do, but is there a way around this memory
problem?
Any suggestions are appreciated!