Database Search

[tim]

I am working with a database file that I want to be able to search by city, name, company name (ect.) and pull up the list that fits that criteria. If you look at the mdb file there are approximately 2000 records there with like 20 different columns. Can anyone help? And also would like to try to print out the search.



------------------
tim ross
[email protected]

[SmithVoice]

I made you a demo QBF project that does the dynamic SQL for a search and lets your user then click on any resulting record to get even more details.

It is zipped to 4k, uses DAO (but you can simply change it to ADO if you want), uses a msflexgrid for record displays. It uses the standard NWind.mdb, so all you have to do is unzip the project to a new folder, copy Nwind.mdb to that folder and run the project.

If anyone wants this, download it from:
http://www.smithvoice.com/downs/qbfdemo.zip

VB5, if using VB6, no prob. I'll leave it up for a while.

Give it a show and see if it helps you get going with QBFs.

-Robert Smith

------------------
http://www.smithvoice.com/vbfun.htm

[SmithVoice]

The typical way (which you probably don't want to hear) is to create a QBF (Query By Form) form, let the user type in any values to search for, contruct the SQL dynamically based on the textboxes that have text and fill a grid.

A common alternative for this with a large amount of optional parameters (fields) is to turn the QBF into a wizard to guide the user step by step through the SQL creation process in little bites.

If you go with either of these ways, you should offer the user the opportunity to save the SQL string to the Database so that they don't have to do a long procedure over and over for thier most common queries.

To hold the SQL make a table with 2 columns:

tblSavedUserQueries

fld: "usrSQL" fldtype: Memo (because your queries may exceed the Text type limit)

fld: "usrSQLLabel" fldtype: Text

when they make the sql let them save the SQL string to the usrSQL field with an easy to remember name for the query saved to the usrSQLLabel field.

At the beginning of the wizard or top of the form, show a list of the saved queries, so they can pick and immediately execute them. (You just read the saved SQL and use it directly)

The alternative is to create a full blown replacement for the Access QueryBuilder, one that reads the relationships and correctly creates complex joined SQL statements as the user dragdrops fields and sets criteria, sorts and more. This is a goal on many developer's "things to do someday" lists, and it is not easy if you really want it to do the full task. I spent 4 months making one that is database neutral and correctly calculates best paths (links) and I would not like ot have to ever do it again .. however, now that I have done it and excapsulated it into an ocx I can use it in any app I ever make. Good luck if you go this route, but remember that after the pain your work will pay off (and boy will you become an SQL pro!). Suggestion for the base control if you go this route? vsflexpro6 from www.videosoft.com .. it's cell merges make the GUI of the builder a piece of cake and it turns results into printable reports when teamed with VSView.

-Robert Smith


------------------
http://www.smithvoice.com/vbfun.htm