I have many reports which are all highly similar. Each one displays numerous individual values. Each one of these values is processed through a function that tests to see if the value is greater than a preset limit. If so the function returns the initial value, if not then a special symbol is displayed. But now my users want the aforementioned preset limit to be dynamic, i.e. they want to type it in when they open a report. How do I query the user for a single value when the report is opened and then use that value as a variable on my report.
To describe things in more detail. Say I have twenty text boxes on my form, each one has as its source a field from the underlying query. But these text boxes are invisible and the user doesn't actually see them. Next to each of the invisible boxes is another text box whose data source is a public function defined in a code module. One of the three inputs to the function is the name of the invisible text box right next to it. (The reason I did things this way was because it made it easier to change the data sources used from the underlying query.) Another one of the inputs to the public function (I call it Format_Data) is the value limit I mentioned earlier. Currently I just have a literal number entered here for every instance of the function. (I wasn't sure if every instance would use the same value limit or not). Anyway, I now need to replace those literal numbers with an actual variable, of course. And I need to assign a value to that variable somehow when the report is opened. I tried adding another invisible text box bound to a data source that didn't exist so that the report would ask the user to input a value for it at startup, but that didn't work. The textbox seemed to act as if it wasn't storing the given value until after the other textboxes had tried to load/compute their data.
If this doesn't make sense I will gladly reexplain it again, and try to organize my thoughts better. But basically I just need some sort of entry box, asking for a single value, to open when a user tries to open a report. Then the entry box should pass the user entered value on to the report somehow.
p.s. I am trying to avoid making a form for each separate report!
To describe things in more detail. Say I have twenty text boxes on my form, each one has as its source a field from the underlying query. But these text boxes are invisible and the user doesn't actually see them. Next to each of the invisible boxes is another text box whose data source is a public function defined in a code module. One of the three inputs to the function is the name of the invisible text box right next to it. (The reason I did things this way was because it made it easier to change the data sources used from the underlying query.) Another one of the inputs to the public function (I call it Format_Data) is the value limit I mentioned earlier. Currently I just have a literal number entered here for every instance of the function. (I wasn't sure if every instance would use the same value limit or not). Anyway, I now need to replace those literal numbers with an actual variable, of course. And I need to assign a value to that variable somehow when the report is opened. I tried adding another invisible text box bound to a data source that didn't exist so that the report would ask the user to input a value for it at startup, but that didn't work. The textbox seemed to act as if it wasn't storing the given value until after the other textboxes had tried to load/compute their data.
If this doesn't make sense I will gladly reexplain it again, and try to organize my thoughts better. But basically I just need some sort of entry box, asking for a single value, to open when a user tries to open a report. Then the entry box should pass the user entered value on to the report somehow.
p.s. I am trying to avoid making a form for each separate report!
