The row sources are queries that bring in values which are regularly updated, so I wouldn't be able to leverage storing those values on the frontends, nor using views because the combos need all the values.
Actually, I can add one more FYI to the issue of combo boxes.
Do NOT use a pass-through query to drive a bound combo box.
But, why?
This advice seems counter intuitive, since after all, a PT query is perhaps the fastest performing way to pull data from SQL server, right?
Well, there is one "dark" downside, and issue one has to be aware in the case of a PT query.
Answer:
Access from the client side cannot filter such a query!!!
So, EVEN if one where to build (modify) the PT query client side to restrict the rows for that combo box?
Still bad!!!
And the reason is as follows:
When the form loads, or you navagate to another record?
Access is displaying the combo box, and OFTEN of course we have a hidden id, and are thus displaying a 2nd column (description or whatever).
And, that means Access has to look up, and take that ONE row out of the combo box data source.
But, as I pointed out, Access can't filter a PT query down to one record, and in fact Access cannot filter the PT query in ANY WAY!!!
What this means?
Well, when you navigate to the next record, or even load the form?
Access has to hit the data source, and "scan it" for the one row that is currently selected. And since the data source can't be filtered?
Then the whole data source will be scanned.
I had a few forms - combo box based on PT query. And when I moved to next reocrd, there was a significant delay.
I traced the issue down to that combo box + PT query.
Changing the combo box source to a view? (which the client side can then filter correctly)?
The big delay went away.
So, yes, use a PT query as you need to, but if ANY client side code, ANY where clause is to be against that PT query?
Then do not use or do this!!!
use a view.
And if the query is a single table, then of course no view is required nor will it help, and you are free to base the combo box on that SQL that points to the linked table.
However, using a PT query for that combo box, and it is a bound combo box?
Nope, Access can't filter the results (well, it can, but that's AFTER all rows are pulled for the filter....).
R
Albert
