Use form filter text in SQL

[ebs17]

However, I have a working setup now

But that doesn't sound simple or efficient when it comes to updating something.

I would replace the hard-coded values with parameters in a statement as shown, the parameters would be taken directly from the ComboBoxes, and a clear query would be ready.

 

[June7]

Is this a multi-user db? Is db split design?

ebs17 - OP Is using ribbon and right-click menu to filter, not actually selecting a value with combobox. That means user could have applied LIKE operator to the field/column built with combobox control. Clarify how your suggestion to take parameter directly from combobox would be applied.

 

[pdanes]

But that doesn't sound simple or efficient when it comes to updating something.

I would replace the hard-coded values with parameters in a statement as shown, the parameters would be taken directly from the ComboBoxes, and a clear query would be ready.

This is a dozen lines of code and runs in around 40 microseconds. What is not simple or efficient about it?

And I can't take the values from the combobox because, once again, I am dealing with what the USER has filtered. The only place that information is available is in the form's filter texts, and they are very complicated to unravel, which was the whole reason I started this thread.

 

[pdanes]

I

Is this a multi-user db? Is db split design?

ebs17 - OP Is using ribbon and right-click menu to filter, not actually selecting a value with combobox. That means user could have applied LIKE operator to the field/column built with combobox control. Clarify how your suggestion to take parameter directly from combobox would be applied.

It is a single-file, single-user configuration. It needs to be portable, because the user takes it with her on field trips.

 

[ebs17]

OP Is using ribbon and right-click menu to filter

I said that if you make things easy at the beginning, you'll pay more later with more effort. I would of course offer my own interface for selecting filters and could therefore evaluate them because I know them.

If you want to use the built-in helpers in Access, you have to know their name and address, i.e. these forms and their controls. This is often difficult, and it is almost certainly not documented. They are just helpers for beginners. They do what they say they do, and you shouldn't expect anything more.
If users want to use such helpers, in my opinion that is a poor reflection on the developer.

Of course, you can offer your own form when you right-click instead of the standard context menu, which you will then know in full.

 

[pdanes]

I said that if you make things easy at the beginning, you'll pay more later with more effort. I would of course offer my own interface for selecting filters and could therefore evaluate them because I know them.

If you want to use the built-in helpers in Access, you have to know their name and address, i.e. these forms and their controls. This is often difficult, and it is almost certainly not documented. They are just helpers for beginners. They do what they say they do, and you shouldn't expect anything more.
If users want to use such helpers, in my opinion that is a poor reflection on the developer.

Of course, you can offer your own form when you right-click instead of the standard context menu, which you will then know in full.

Duplicating things that Access already knows how to do is usually a huge waste of time and effort. It is almost always slower, more trouble-prone and more likely to break when the boyz at MS change something. There are some special cases, of course, but again, I now have a solution to my problem, so I see no point in continuing a discussion of general design philosophies. Thank you for your thoughts on the matter, but I am not going to build a special set of right-click functions to duplicate Access's.