Need Help With Querydef

[LQ]

I have been reading postings on this board and have read the MS article on querydef and yet I am still confused...

Basically, I am trying to have one form where users can enter up to 5 criteria for a search, and then I want the results displayed on another form.

I tried copying and pasting the code from the querydef in the Qrysmp97 database and could not get it to work. It told me sub or function not defined for the following line:

If ObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh

And if querydef is not the best way to do this, feel free to give me alternative suggestions. I have already tried to create a dynamic SQL statement, but have had no luck (even with guidance from other kind forum members).

Thanks for any help!

[This message has been edited by LQ (edited 08-21-2001).]

[This message has been edited by LQ (edited 08-21-2001).]

 

[The_Doc_Man]

Creating a stored query (implied by using a QueryDef) could run into naming conflicts. You would do better to build a form that is not bound to a specific table, then in your underlying event code, define a recordset, pop up a sub-form, and show the results that way.

The way I did this, I had a series of options where the user could enter a product name, version number, operating system name, OS number, and a couple of other options.

In the unbound form I put several text boxes and a command button titled RUNQUERY. When you clicked RUNQUERY, the code did the following:

set up two strings called stFields and stWhere:

stFields = "SELECT [Server],[Location],[Cost],[Department]"
stWhere = "WHERE NOT ISNULL([Server]) "

In my case, each text box corresponded to a specific field. They didn't have to enter a field name because that was fixed. For each text box, if there was a name in the box, I added a name to stFields and the value to the stWhere like this:

stFields = stFields & ",[fldname]"
stWhere = stWhere & "AND [fldname] = """ & [fldname's associated textbox value] & """"

But in another case where the textbox was an all-numeric field,

stWhere = stWhere and "AND [fldname] = " & [fldname's associated textbox value] & " "

This was hard-coded by text box name because there weren't that many of them. When I was done, I finished the concatenation with

stQry = stFields & " FROM MyTable " & stWhere

Then I opened my sub-form in form view with that query as its recordsource property. Worked like a champ. The business about adding fields to the select was so that you could apply WHERE clauses to them - but the fields I was going to display in the sub-form were pre-selected and I didn't add the field in a second time if the user's selection criterion named a field I was already going to display.