Build the 'where' sql statement following search form selection (1 Viewer)

[Chaga]

I just wanted to add this.

I personally never filter a form. I change the record source
In your case instead of

Forms![Contact List].Filter = "ID In (" & SelectedID & ")"

I would use

Forms![Contact List].RecordSource = " SELECT * FROM [Contacts Extended] WHERE ID In (" & SelectedID & ")"

And never use space in object names.

Are filters not recommended even for a temporary search? As for the naming convention, I am aware of that, but I started this DB by using the access contacts template, and it was supposed to be a very simple small thing, but the person who requested it, kept asking for new stuff to be added, and it grew much more than I expected.
Had I known from the start, I would’ve built things differently.
Thanks again for your help and time.

 

[MajP]

Are filters not recommended even for a temporary search?

Filters are fine in many situations, but not in others. If this is a relatively small database you will never see an issue. However, if you have a lot of data (especially on an external database) you may want to reduce the records you pull. If you make a query to filter the results it the external database will only return the filtered set. However, if you do this by applying a filter to the local database then it would have to pull everything first then apply the filter. Imagine you have 1Million records on an external database and your filter returns only 5. Better to request only the 5 from the external db instead of pulling all 1 million and then filtering it to the 5 you need.

 

[Pat Hartman]

The checkboxes are all redundant. If a field has data, the user wants to search on it. When the user checks the And/Or box, BOTH fields are required otherwise having both filled should generate an error.

I agree with Maj, I always build SQL for the search. You mentioned early on that the searches are for different tables. If that is the case, I would NOT use one form. I would use a separate form for each table. That way, you listbox or small subform can have code in the double click event to open the selected record for editing.

Posting the SQL in the box is a great sanity check. Posting just the WHERE clause will keep the string shorter and more understandable for the users. For the search forms that also opened reports, I put the Where clause in the report footer of the form so the user didn't forget what his selection criteria was.