Hello again, Colleagues All,
Another thorny little problem which will no doubt look absurdly simple after you bright sparks out there have had a look at it.
I have a form which contains a number of combo boxes, all of which pull data out of the same table in accordance with different criteria. I attach a PDF file with a few screen grabs which I hope will illustrate the problem adequately. I have added a combo box by copying another and then changing the query to pull out the data with different criteria.
The first two pages show the properties of the old (function date) combo box and the new (booking date) combo box respectively. The third page shows the underlying queries which populate the two combo boxes.
The fourth page is just a picture of the form for context, while the 5th and 6th pages show what happens when you click on each of the two combo boxes. The original one collects all of the required data. The new one collects nothing ! If I copy the SQL from the new combo box into a Query, it works perfectly on execution. This suggests that there must be a property value somehow which is affecting the result, but I just can't see it.
I know that tearing ones hair out is the cheapest buzz-cut on offer, but this is ridiculous. Can anyone help, pretty please with sugar on it ?
Well, guys, I got on with life by deleting the combo box and building a new one from scratch. It worked perfectly. I still don't know why the original did not work - after all, I have done the same thing a thousand times before. Still, there might be a PhD thesis in there somewhere for somebody. The only conclusion I can draw is - duplicate at your own risk !
Just a quick update which may be of interest. In my previous post, I intimated that I had solved the problem, but the query fell over yet again.
I now have a fix of sorts. though I would not call it a solution, because I cannot say with confidence why it works (though I can speculate).
What I did was to create a new combo box and edit each property to bring it into line with the other sibling combo boxes. The main difference with previous attempts was that I copied and pasted the rowsource SQL from the FunctionDate-based combo box and edited it in the actual property line. In other words, I did not use the wizard.
Bearing in mind that the same SQL has always worked in an MS Query, I theorise that the wizard, which checks text as it is entered and modifies it to suit its own rules (square brackets around field names and so forth) is being a bit too clever somewhere. The Jet engine is possibly a bit more simplistic and so long as the SQL makes sense and is not ambiguous, it will shrug its shoulders and process it. So far as entering the SQL directly in the rowsource property line, no fancy checking is going on and no behind the scenes assertions are being made, so it will be OK if it is acceptable to the Jet engine.
I hope this bit of info will be found to be useful somewhere.