Search Form Issue With Multiple Users

[LarryDavidow]

I have me a predicament!!

I have a search form working very niceley that queries one of my tables and returns results based on 5 fields in a subform.

Here's my problem... The subform data is based on the query "Search". In the "OnClick" procedure of the search button, some basic SQL is run to add criteria to this Query based on values in the text boxes.

If more than one user searches at the same time, they either get an error (file deleted), or they get the results of the concurrent user's search (Depending on how the searches are sequenced).

My grand idea was to create multiple queries with the user names appended to the end (SearchUser), and in my SQL string, specify the query to be used as Search" & CurrentUser. This works great, however, how in the hell am I going to get my subform to pull records from the correct query based on the specific user who is logged in and doing the search???? Is there any way to dynamically set the subform's data source to Search & CurrentUser()???? Or, can this be done programatically???

Lots of questions....

 

[PHV]

What is for you the difference between dynamically and programatically ?
Take a look at the Form.RecordSource read/write property.
You may assign to it either a Table/Query name or a string containing a SELECT sql instruction.
BUT, in multi-user mode you may consider splitting your database in one BackEnd containing all the datas and as many FrontEnd as connectable Pcs containing the queries, reports and modules.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LarryDavidow]

I guess that would be possible, but very admin intensive. Say I decide to make changes to a form and I have 15 users, I have to do that 15 times???? Hmmm... there must be a better way.

As for the difference between dynamically and programatically... in my head, that would mean either in the SQL string (Dynamically), or in VBA (Programatically).

 

[PHV]

There are simple methods for having the FE automatically refreshed on connection to the BE then new release should be deployed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LarryDavidow]

I'll look into it. Thanks for the tip!!

 

[maxhugen]

It's still best to separate FE and BE. You could have the FE sit on the server with the BE, and provide links for users to open the FE from the server. Once a user opens the FE, it will run from their local pc anyway.

As for the query, you could use a 'standard' query, then in your code, get it's SQL, add your search criteria, then assign this complete SQL string to the form or report etc as the record source.

Or, you can open the form/report with the criteria specified... see the DoCmd.OpenForm (or OpenReport) for syntax.

HTH

Max Hugen

http://www.AlphaFinancial.com.au

Sydney Australia