Access frontend - SQL Server backend - Ctrl-f (find option not working) (1 Viewer)

[Derevon]

Hi everyone,

So I have this tracker type database with an SQL server backend, and I just noticed that using Ctrl-f to find some record or using DoCmd.FindRecord doesn't seem to work unless the records are already loaded. It simply loads and loads indefinitely or until pressing Ctrl + Break.

I would want the users to be able to search for specific records without having to use a filter to find what they need.

Does anyone know of a solution to do this?

Thank you

 

[cheekybuddha]

Create a dedicated search textbox, or search form that fires off a query against the SQLServer.

Bind it to Ctrl+F, perhaps using an AutoKeys macro.

ps. what's wrong with a filter?

 

[Pat Hartman]

When the BE is SQL Server or some other RDBMS, do NOT use local filters. That defeats the purpose of using a server based BE. Why load a million records and search locally when you can send a query to the server and get back only the record you want?

If the search is complicated create a form with the various options and build a WHERE clause based on what the user selects. I run a dCount() on the resulting query. If the result is 0, I give the user a message. If the result is 1, I open the standard edit form. If the result is >1 but < 1000, I open a list type form that the user can filter locally. If the query returns too many rows, I ask for confirmation before returning all that data.