Search Forms...Is VB really necessary? 1

[Brlee1crv]

Hello,
I'm fairly competent in Access. I'm going to start learning VB to get the most out of Access soon. My dilemma is that I have a database that users will need to use an easy interface to search for stuff. Is it possible to build a search form without any VB programming? What I'd like is to have users type in criteria, click on a search button, and the results display either in datasheet format or report format. Thanks.

 

[Carthesis]

It can be done by just writing the query and setting variables within the query to point to the fields on the 'Crayola Search Form' for the users. The only VB you need to do is set the OnClick() event of the 'Search' button to run the query for you, but you can do that through the wizard.

The problem will come when you want to search on more than one criteria, and you'll need to use VB to fill in the blanks. VB is, in places, fairly simple and worth learning. The nice people on here will help you with problems when they arise. Give it a try.

 

[Norris68]

You could always use the Filter By Form function that is built in to Access.

 

[Brlee1crv]

Thanks for the suggestions. I guess VB is the way to go because the data will need to be searched by more than one criteria. I'm just worried about the learning curve of it all. I don't want it to be so long and drawn out that I lose interest in it.

 

[deduct]

Try this:

In a database that already has existing tables, queries, reports, etc., create a form, and use the wizard to create a command button to perform an action, such as run a report. Then, view the VBA code to see what the wizard wrote. The learning curve on something like this if very small.

Karl

 

[AlienRay]

I do multiple criteria searches without any VBA all the time, just by setting up the proper query.

Suppose you have a form with two fields: CityLookup and CompanyLookup. You want to be able to look up all customers in a particular city, all customers in a particular company, or only those customers in a particular company in a particular city.

So you set up a query based on the table you're searching. In one column you set the Field value to:
CityCheck: IIf([forms]![FormName]![CityLookup] Is Null Or [forms]![FormName]![CityLookup]=[city],"x","&quot

In the next column you set the Field value to:
CompanyCheck: IIf([forms]![FormName]![CompanyLookup] Is Null Or [forms]![FormName]![CompanyLookup]=[company],"x","&quot

For both columns, set the Criteria to "x".

Voila! Your query now returns values for only those fields which have values entered, and ignores the rest. Whether you want those results displayed in a list box or on a report, you can do so without any VBA code at all.

 

[nonzarelli]

Alien:

In relation to your advice, what exactly do your [city] and [company] represent?