Search Form (2000)

Topic

New Reply

The attached file have the best search form sample. But I would like to have three search fields instead of one include any field. For instance, I want to have “CD Index, Date and Userfulness” three fields to let user enter. I tired many times but I can’t make it.

thanks for any help.

Reply | Quote

Replies

See the attached version. I have created separate text boxes for searching CD Index, Date and Usefulness, and corresponding hidden text boxes. I have modified qryCDNoAsc to work with the new text boxes.

Important: I haven’t modified the other queries. You will have to study qryCDNoAsc, then do the same for the other queries.

Reply | Quote

Thank you so much, Hans. This is what I want.

Thanks

Reply | Quote

Hi Hans,

I really love this search function. But if table have more than 20,000 data, it will be very slow when I type the information in the search field. The button of form shows calculating. How we can do to speed it up?

Thanks for your help, and you are the best.

Reply | Quote

The present code will requery the list box each time you type something in any of the search text boxes. You could remove this code, and use a command button instead. That way, the user can enter text to search for in each of the three text boxes, then click the command button to search. See the attached version. (It could be simplified a bit, but I wanted to modify the design of the form or query as little as possible).
You could also create an index on the CDDate field in the tblCDIndex table. This will speed up searching and sorting if the number of records is large.

Reply | Quote

Hi Han,

Thank you for your help. It really works great.
But I have another question, I add one more column “OldCDIndex” in the tblCDIndex. After user input data in the “Search CD Index”, search form will search on both “CDIndexID” & “OldCDIndexID”. I did add “OldCDIndexID” on the form, but I don’t know how I add in the query in order to get what I want. I tried several ways, but it doesn’t work.

Thanks

Reply | Quote

At the moment, the text box rather confusingly labeled “Search CD Index” actually filters on the CDNo field, not on the CDIndexID field. Would you like it to filter on CDIndexID and OldCDIndexID instead? Or do you want to add another text box?

Reply | Quote

Hi Hans,

Sorry for confusing. I would like to filter on CDNo & OldCDNo by one text box.

Thanks

Reply | Quote

You can add a series of conditions to the queries. In the attached database, I have modified only qryCDNoAsc.

Note: there are so many criteria rows now that you’ll have to scroll down to see them all.

Reply | Quote

Hi Han,

Thank you so much for your help. It really works great.

But I have another question, I add one more column “OldCDDate” in the tblCDIndex. After user input data in the “Search Date”, search form will search on both “CDDate” & “OldCDDate”. I did add “OldCDDate” on the form, but I don’t know how I add in the query in order to get what I want. I tried several ways, but it doesn’t work.

To make it clear, I would like to filter on CDDate & OldCDDate by one text box.

I am really appreciate all your help.

Regards

Thanks

Reply | Quote

That is done the same way you now search on CDNo and OldCDNo using one box. Unfortunately, the way Access organizes it in the query design window becomes cumbersome – each new addition doubles the number of criteria rows used. See attached version (as before, I have only modified qryCDNoAsc)

Reply | Quote

Thank you so much, Hans. This is what I want.

Regards

Reply | Quote

What he could do is build the query qryCDNoAsc on the fly in VBA code.

Reply | Quote