I have a few datahsheet views which allow the user to click on a field and see a popup of the record that field specifies e.g. in the 'Contracts Due for Renewal' sheet view all of the contract which expire in the next 3 months are shown. The user used to be able to click on the 'Contract Ref' and get a popup displaying the specific contract they clicked displayed in the 'Contracts List' view. To do this the 'On Click' macro did something simple like the pic attached.

All of a sudden, and for no reason I can figure, this function doesn't work in any of the places I've implemented it. Now, when I click any of these fields I get the popup to the correct screen but the 'Where' clause isn't filtering the table as described in the 'On Click'.

Hi Cap,Just to get more clarity (but also as a workaround, if it doesn't get explained) is using a query and parse the control value with a query parameter. The option of filling in the parameter automatically appears when you select a form that has a query with a parameter as datasource. (I didn't test by I'm practically 100% sure).

Otherwise you may rename the control and fill it in without the brackets. Controlnames that match field or variable names can be dangerous.Kind regards,Rob

Thanks for replies - sorry for my slow response (out on customer sites as usual). So, I need to look into your response Rob as I'm not sure I follow 100% (I'm a little slow!). I agree, I should have underscored my variable names but I didn't know this when I started out so I stuck to the convention I had already adopted.

With that said, I have at least half a dozen instances of this same basic function (pick a Contract References, Instrument ID, Customer Site ID etc. and get a popup of that record via a list view) which all worked previously and have suddenly stopped working very recently. It seems like it's not my implementation which has suddenly changed but something on the backend which was changed / fixed and has rendered my implementation unusable. Any ideas on what that may be?

OK, so I tried just editing the name of the control first so that it was different from the field name and didn't contain any spaces. That didn't work. I tried rearranging the formula for the popup so that the WHERE clause was in the form [ControlName] = [tblName].[VariableName] but that didn't work either.

I finally reverted to Rob's workaround which works great. My only issue with this solution is that unless I'm being dense (which is a distinct possibility!) I need to create a new Form (with a parameter) and a new Query for every place I want to implement this kind of functionality, which seems a bit overkill. Any idea what changed as my previous implementation worked in numerous places until very recently

A while back I added some Requery ON LOAD filters to various views. It seems the links in question hadn't been used since those re-queries had been implemented. So, the initial popup instruction I posted above works but when the popup appeared the ON LOAD macro re-queried the results and screwing things up.

Two solutions seem to work then 1) Use Robs proposed solution and create a new query with a parameter attached and ensure the Contract Ref (or other reference) matches the parameter, create a new view based on the results of this query. But this approach seems to require both a new query and a new form. 2) Create a duplicate of the original form but without the re-query.

I'm wondering if I can use Rob's solution and just create a query for each table with an added parameter to use in case I need to search on it. But, I can't find a way to have the App ignore the parameter if it's not supplied. If this is possible, I think that option is more efficient.

Hi Cap,Yes, that's possible. You can check whether the paramater is null in a query criterium expression like "iif([vParam] is Null, Exp1, Exp2)". Use the fieldname in case for exp1 to get the same result value as the field itself, so it'll always be true if vParam is Null "iif([vParam] is Null, [ColumnName], Exp2)".

You can't avoid the automatic query on the opening of a form, so try to avoid requery on opening a form as much as possible. It makes things twice as slow.Rob

I would suggest duplicating the form as a standalone/PopUp, and remove the onLoad macro from the popup form. Your Open PopUp should reference this pop up view rather than the original table view. This is most efficient as it takes seconds to duplicate a form as a pop-up and you won't have to create new parameter queries.

@Ryan, yes you're right. I misunderstood from Cap it concerned an OnLoad-macro from the PopUp-form itself. Never considered an event-macro within a PopUp form the past five years whatsoever. I expect it can be useful in certain cases.