Passing items from drown list into the where statement of a query

[Guest]

Sorry but I'm totally new at this...

I want to create a Search Form based on the items selected from a form that
contains drop down lists. I would have a drop down list that would contain
operators (<,>, AND, OR, etc...) and a drop down list that would contain my
table field names, plus a text box which would allow the user to enter their
condition.
Based on what is entered into the form the items would be passed to the
WHERE condition in the query.

example of what I am thinking....

SELECT X, Y, Z
FROM
TABLE1
WHERE
[PASSED FIELDNAME] [PASSED OPERATOR] [[PASSED FIELDNAME2] etc...

OPTION 2?
Maybe just listing all of the possible table fields and passing the AND/OR
operators

thanks in advance

 

[John Spencer]

What you want to do is not possible directly into the query. You can pass
values to be searched for, but you cannot pass the operators (<,>,=, etc.).
This can be done using VBA to build the query string or the where string.

You can use the filter by form option

or

You might want to consider the Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'
or an earlier version at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH QBF can replace many "canned" reports. The developer imports several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main form. The main form has options to send/export the records to print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most
formats allow he user to automatically open the target application. The Word
merge process will open a new Word document and link to the merge fields.

 

[Guest]

John,

Duane's code does exactly what I wanted. Thanks.

One problem I am encountering is that users that do not have admin level
rights are encountering error "3033: You do not have the necessary
permissions to use the 'MSysTables' object. Have your system administrator
or the person who created this object establish the appropriate permissions
for you". I have changed the user permission levels and am still getting the
error when someone is not an admin level user.

confused.

 

[John Spencer]

I have no idea what is causing the problem. Suggest you start a new thread
on this problem.

Is it specifically associated with Duane Hookom's Query by Form applet?