Creating a Searchable Form for a Single Table Database in Access 2007

[Green831]

Hello,
I am fairly new to Access 2007 – I can make a table, queries, forms and reports but am largely doing so through the wizard function and then modifying the structure of each for my purposes. I have not knowingly used VBA except when entering criteria for the query but am ready to use some. I was wondering if you could help me determine how to make a searchable form for visitors? Or if it is not a searchable form I need, what would be the best way to have visitors easily search the database? My goal would be for the visitor to search the various fields in the database but not be allowed to alter the results.
I have constructed a single table database that describes the status of historic buildings. The database has the following fields: Building Name; State; County; Type; Year Constructed; Status. I would like them to be able to have a form that they could enter search information or choose from a drop down button for some fields and then get the results.
Any help would be very much appreciated.

[patjones]

In the most general terms (because you're asking a pretty general question) one thing you can do is have the results appear in a report.

What is often done is to set the record source of the report to be the whole table. When something is selected from a combo box, you can open the report using the combo box selection as a filter that will narrow down the results. For example,

DoCmd.OpenReport "rptBuildings", acViewPreview, , "[Building Name] = " & Me.cboBuildingName

where cboBuildingName is the name of a combo box on your form. Here, the last argument in the OpenReport command is the filter...it basically acts like the WHERE clause in a SQL statement.

I would recommend that you store information such as State, County, Type, etc. as numbers, then use look-up tables for each column (i.e. there will be a table that holds States, one that holds Type, and so on). Take a look at Database Normalization and Table Structures for more information on how to structure your data.

Pat

[NeoPa]

I would suggest a form that is designed to show the information in the Detail section but has some unbound controls in the Header section which can be used to filter the data. An example of this with some instructions can be found at Example Filtering on a Form. It even has an example database attached that you can download and play around with. Experimenting with ideas for your own needs.

[Green831]

Thank you Zepphead80 and NeoPa for answering my question! I am going to look into both options and then as I am able to ask more specific questions. I just wanted to start off on the right track. Thanks again :).

[NeoPa]

Good for you. We'll look forward to hearing further from you :)

Welcome to Bytes!