Absolutely bizarre query issue

[MWhitfield]

So I'm relatively new to Access; I'd never used it before about two weeks ago. Nevertheless, I've spent a lot of time with it in those two weeks and I think I've got a pretty good hold on things. Every problem I've encountered in building my database so far (and there have been a lot, obviously), I've managed to solve through a combination of Google and help docs, but this latest one is so perplexing I'm turning to you guys.



So, I have an inventory database for a chemical laboratory. There are only three tables: Chemical Index (info for each type of chemical in the inventory), Transactions (log of all transactions -- receiving, check in, return, discard, etc), and Inventory (the actual list of items, with storage location, current status, lot number, etc.)

I have a form called Browse Inventory, which basically just contains three listboxes: InventoryList, which is just what it sounds like, and ManufacturerList and ProductList, which are set up as "filter panes" like you'd see in a media player, filtering the contents of InventoryList based on the manufacturer and product name selected in each (ManufacturerList also filters ProductList).

Now, getting that whole filter pane setup going was quite a trick to start with, but I managed to get it perfect. Today, I was trying to cut down on the number of objects by incorporating some of the queries involved in that setup and just embedding the SQL in the controls themselves. What I have now is this:

ManufacturerList row source:

SELECT [Chemical Index].Manufacturer, Count([Chemical Index].[Chemical Name / Encapsulant ID]) 
AS [CountOfChemical Name / Encapsulant ID] 
FROM [Chemical Index] 
GROUP BY [Chemical Index].Manufacturer 
UNION Select Null as AllChoice , "(All)" as Bogus From [Chemical Index];

ProductList row source:

SELECT [Chemical Index].[Chemical Name / Encapsulant ID], Count(Inventory.[Name/Encapsulant ID]) 
AS [CountOfName/Encapsulant ID] 
FROM [Chemical Index] 
LEFT JOIN Inventory ON [Chemical Index].ID = Inventory.[Name/Encapsulant ID] 
GROUP BY [Chemical Index].[Chemical Name / Encapsulant ID], [Chemical Index].Manufacturer
HAVING (((IIf(IsNull([Forms]![Browse Inventory]![ManufacturerList]),[Manufacturer] Is Not Null,[Manufacturer]=[Forms]![Browse Inventory]![ManufacturerList]))<>False)) 
UNION Select Null as AllChoice , "(All)" as Bogus From [Chemical Index];

InventoryList row source:

SELECT Inventory.[Inventory Number], [Chemical Index].Manufacturer, [Chemical Index].[Chemical Name / Encapsulant ID], Inventory.[Lot Number], Inventory.[Current Status], Inventory.[Expiration Date], Inventory.[Storage Location]
FROM [Chemical Index] 
RIGHT JOIN Inventory ON [Chemical Index].ID = Inventory.[Name/Encapsulant ID]
WHERE (((IIf(IsNull([Forms]![Browse Inventory]![ManufacturerList]),[Manufacturer] Is Not Null,[Manufacturer]=[Forms]![Browse Inventory]![ManufacturerList]))<>False) 
AND ((IIf(IsNull([Forms]![Browse Inventory]![ProductList]),[Chemical Name / Encapsulant ID] Is Not Null,[Chemical Name / Encapsulant ID]=[Forms]![Browse Inventory]![ProductList]))<>False));

The SQL is funky, I know, but it works (or worked, I guess) perfectly. If you don't feel like trying to parse through those criteria (don't blame you), I basically just set up fields that evaluate to True if no record is selected in the filter boxes, or if there is no selection in the filter boxes, and False if the user HAS made a selection that is not equal to the relevant field. Then a criteria of not false on that field. The first two listboxes also requery the "lower-level" listbox(es) OnUpdate.

So that was a ton of exposition. Now, here's the problem: when I load up the database, and attempt to load up Browse Inventory, I receive 3 parameter prompts: two for [Forms]![Browse Inventory]![ManufacturerList], and one for [Forms]![Browse Inventory]![ProductList]. ProductList and InventoryList then come up blank when the form loads. Now yes, both of those expressions will evaluate to Null since I just pulled up the form, but the SQL should be able to handle that (and can, it worked fine as external queries). It also continues to prompt me any time I select something on ManufacturerList, when it requeries the other two boxes.

And here's where it gets really weird. If I change the form's record source -- to anything -- the form will suddenly work perfectly. I can even close out the form and re-open it and it works fine. BUT, if I exit out of the whole database and come back in, with the new record source saved, it goes right back to the original problem. What makes this so strange is that there are NO bound controls on the form; shouldn't the form's record source have no effect at all?

Now yes, I could go back to having the queries as external objects and have the listboxes just reference those. But I like this way better if I can get it working. And besides, at this point I'm just so befuddled that I want to solve this one on principle. It'll just bug the hell out of me if I don't.

[Access_Blaster]

I have a form called Browse Inventory, which basically just contains three listboxes:

If I change the form's record source -- to anything

I pulled the two quotes out because they seem on the face to contradict each other. Care to explain what you mean by the forms record source? Because if you have three list boxes each with their on "Row Source" What is the record source of the form for?

What makes this so strange is that there are NO bound controls on the form;

Regards,

Richard

[MWhitfield]

That's exactly the point. The record source isn't FOR anything -- there's no logical reason it should have any effect on anything at all. I'm just talking about the "Record Source" property of the form -- but nothing references it.

[Access_Blaster]

You could try importing all the objects into a New database and leave all the default options set. Have you looked at the form's events, like On Open, On Current and so on? I know from experience I am my own worst enemy. I fiddle with a piece of code until it does something really unexpected...haha.