views:

547

answers:

5

How would you create a database in Microsoft Access that is searchable only by certain fields and controlled by only a few (necessary) text boxes and check boxes on a form so it is easy to use - no difficult queries?

Example: You have several text boxes and several corresponding check boxes on a form, and when the check box next to the text box is checked, the text box is enabled and you can then search by what is entered into said text box

(Actually I already know this, just playing stackoverflow jeopardy, where I ask a question I know the answer just to increase the world's coding knowledge! answer coming in about 5 mins)

A: 

For a question that vague, all that I can answer is open MS Access, and click the mouse a few times.

On second thought:
Use the "WhereCondition" argument of the "OpenForm" method

CodeSlave
I'm not being mean. Answering your own question is fine too. I just think that the question was way to vague to get a straightforward answer. And your answer could be summarised to: "Use the WhereCondition argument for the OpenForm method"- it's not real life, it's just ones and zeros (spaf)
CodeSlave
A: 

At start-up, you need to show a form and disable other menus etc. That way your user only ever sees your limited functionality and cannot directly open the tables etc.

This book excerpt, Real World Microsoft Access Database Protection and Security, should be enlightening.

ProfK
That's pretty interesting, and would probably stop a casual user from editing data. But I wouldn't trust it to actually protect data which needed to remain secret, as you could write a program, in .Net to connect to the access database and run queries without ever opening it.
Kibbee
+1  A: 

My own solution is to add a "filter" control in the header part of the form for each of the columns I want to be able to filter on (usually all ...). Each time such a "filter" control is updated, a procedure will run to update the active filter of the form, using the "BuildCriteria" function available in Access VBA.

Thus, When I type "*cable*" in the "filter" at the top of the Purchase Order Description column, the "WHERE PODescription IS LIKE "*cable*" is automatically added to the MyForm.filter property ....

Some would object that filtering record source made of multiple underlying tables can become very tricky. That's right. So the best solution is according to me to always (I mean it!) use a flat table or a view ("SELECT" query in Access) as a record source for a form. This will make your life a lot easier!

Once you're convinced of this, you can even think of a small module that will automate the addition of "filter" controls and related procedures to your forms. You'll be on the right way for a real user-friendly client interface.

Philippe Grondier
A: 

This is actually a pretty large topic, and fraught with all kinds of potential problems. Most intermediate to advanced books on Access will have some kind of section discussing "Query by Form," where you have an unbound form that allows the user to choose certain criteria, and that when executed, writes on-the-fly SQL to return the matching data.

In anything but a flat, single-table data structure, this is not a trivial task because the FROM clause of the SQL is dependent on the tables queried in the WHERE clause.

A few examples of some QBF forms from apps I've created for clients:

  1. Querying 4 underlying tables
  2. Querying a flat single table
  3. Querying 3 underlying tables
  4. Querying 6 underlying tables
  5. Querying 2 underlying tables

The first one is driven by a class module that has properties that reflect the criteria selected in this form, and that has methods that write the FROM and WHERE clauses. This makes it extremely easy to add other fields (as long as those fields don't come from tables other than the ones already included).

The most complex part of the process is writing the FROM clause, as you have to have appropriate join types and include only the tables that are either in the SELECT clause or the WHERE clause. If you include anything else, you'll slow down your query a lot (especially if you have any outer joins).

But this is a big subject, and there is no magic bullet solution -- instead, something like this has to be created for each particular application. It's also important that you test it thoroughly with users, since what is completely clear and understandable to you, the developer, is often pretty darned mystifying to end users.

But that's a principle that doesn't just apply to QBF!

--
David W. Fenton
David Fenton Associates

David-W-Fenton
yeah, i suppose this is a pretty big question. Still, i couldn't get the queries to take data from the form efficiently (or correctly) so, see the solution at the bottom with all the code. Basic ideas are good. Examples are better.
Oh still, by the way, good answer.
Unfortunately, I don't have any shareable examples, precisely because it's a complex subject and all of my solutions are specific to client projects that I don't have permission to share. You mention queries -- I would never use saved queries for this, but would instead write on-the-fly SQL.
David-W-Fenton
A: 

If the functionality is very limited and/or specialised then a SQL database is probably going to be overkill anyhow e.g. cache all combinations of the data locally, in memory even, and show one according to the checkboxes on the form. Previously you could have revoked permissions from the table and granted them only on VIEWs/PROCs that queried the data in the prescribed way, however security has been removed from MS Access 2007 so you can you now really stop users bypassing your simple app using, say, Excel and querying the data any way they like ...but then isn't that the point of an enterprise database? ;-)

onedaywhen