There is a hidden productID textbox that is linked to the lot number. When you exit the date textbox it reads the value and compares that to the different product types. This in turn toggles the visibility of the appropriate subform and updates the product type textbox.The same function is linked to the prev./next buttons.
I suppose I will have to rewrite the query in such a way that it reads the lot number and the date on the form and runs the query based off those values. I figured I could avoid pain and suffering in the future by having a better table design. I can do what I am told in Access, but I am a little green when it comes to practical design.
I think I am going to develop a work around for now as I am facing a pending deadline, 'good enough for now' will suffice but I am still interested in a better way to design my tables, and I don't like doing things in a haphazard fashion.
EDIT: Third time is a charm I hope:
Here is the new table setup. The first time I did this, tblDailyBatch was an associative table until I realized that it was set up as a many-to-many relationship when I wanted nested one-to-many relationship. I just now realize why I had used the many-to-many relationship in the first place... I needed a concatenated key. If you see any glaring problems with this solution please let me know.
BTW: Thanks for giving me a place to air out my thoughts!!!