Getting certain data in table for each item in a subform

[orange]

Well I'm not in favor of building on the fly. I like to model the data involved and set up proposed tables, then work with test
data and test scenarios to vet the model. You are extremely familiar with your need and data, but the rest of us are not. So we (I) are trying to sort out the essence of your business and decipher your tables and fields. We don't know the details of what is needed in simple English terms. When you have more than 1 field to represent something and under certain conditions, you end up with some convoluted logic to sort out the issue. Often whether or not a representation (proposed structure) meets the requirement can be tested and evaluated with a model and test scenarios.
The best advice I can offer at the moment is to test your theory/intention with some sample data. That way you can confirm whether it works or not. It appears that you may have a design issue based on your concerns with CycleCount. But look at the whole issue, don't add fields or special code to solve 1 issue at a time. Keep the big picture in mind --someone will have to maintain this at some point.
See my stump the model for some ideas.

??Where exactly do you use frmWOSubformLotNumLookUp??

[Dave14867]

Orange,

That form is not used. I was thinking of using it but haven't yet. I was going to use it along with the frmWorkOrders to provide info on the selected row in the subform so the user could see the details of the Part and Lot used. It probably won't be used.

In the actual file, everything is working great, I just wanted to add more functionality to the Work Order form. I understand what you mean about building on the fly and I agree. The original file has been in use for about a year and does everything it has been asked to do. I guess it was just my error to have the CycleCount field in there rather than just treating the parts added (Found) as just parts being received. In the file that I am actually using the transactions table has almost 20,000 entries, the file I have uploaded here is a dummy file based on the one I am using (my model), and just has a very limited amount of sample data. I will load some more data into that file and try out my theory, if I am correct, it will work fine, if not I will try to ask better questions so as to better outline what I need.

As Always, Thank You. Your input is highly valued

Dave

[orange]

Dave,
When I tested in an earlier version of your database, that form was used with the query qryWOSubformLotNumLookup?? When I try to run that query now I get this screen ???


Again, if you provide some step by step info, I' ll test things, but I'm guessing at some pieces of the puzzle at the moment.

[Dave14867]

Orange,

Your getting that screen because you don't have the frmWorkOrders open and a row in the subform selected. That qry uses the Part Number of the selected row in the subform to look up the info for the query. If you just try to run the query without having the form open and a row in the subform selected, it doesn't know what to look for.

Dave

[Dave14867]

Orange,

This file takes care of all transactions in our manufacturing business. It is not used as the "Official" tool but is considered by many to be extremely helpful and in many cases more accurate than the "Official" tool, and I can add reports and such extremely quickly.
All transactions are recorded in the TranasactionsTable, both incoming and outgoing, incoming (received raw material) is recorded in the UnitsReceived field.
We use Work Orders to build up sub assemblies and assemblies (Final Product) to ship out. Those sub assemblies and assembly quantities all get populated in the cyclecount field (Possibly incorrectly) rather than as UnitsRecieved.
This whole exercise was to add functionality to the file so the user (primarily me) can use a combo box rather than just a text box to enter data for "Lot Numbers". The benefit is that unless a lot number has previously been enter for the sub assembly, it won't be available for the assembly so the assembly can't be finalized. This ensures that "Lot Number" entry mistakes made on paperwork are caught and corrected.

Let me know if that helps in understanding more.

Dave

[orange]

Hi Dave,

Sorry about the last post --bad info on my part??? I just went back to your earlier zip and that form is NOT being used in the query.
I don't know what I was working with, but that's how I remembered and couldn't find that subform on the WorkOrders form?? Oh well, I must have misread or misunderstood??? Any way I'll look at your latest info in more detail.

[mike60smart]

Hi

To see he results from qryWOSubformLotNumberLookup I added a subform and linked them using Forms Reference

It will display the corresponding PartNum when you select a Part Numb

I may be way off the mark in what you require though.

[orange]

Dave,
Check out Mike's approach and see if it satisfies your need.

[Dave14867]

Mike,

That is basically what I was looking for for that subform, I will look into how you did that and decide if I really do want to incorporate that, it lets me know how many of that Lot Number are available.

Orange,

That form was an after thought when I was working on the real issue of the Lot Number Lookup. I am working on filling in more data to assist in exercising the database and entering work orders to see if the changes I made ( populating the UnitsReceived instead of CycleCount when a Work Order is completed) are working as desired.

Thanks

Dave

[orange]

Dave,

Sounds good. You already know this, but I'll overview anyway.
If you have some defined requirements such as
-ability to add new X
-ability to modify X
-ability to identify all components of a FinalProduct
-ability to account for all Parts
.....
and you design and test to accomplish these. Then, when a new requirement is identified and "detailed",
you can add that new thing to the list.

The key is that all items in the list must function. So you test the whole gamut, not just the new thing.
Good luck.

[Dave14867]

Orange,

I am going thru now and adding more data and so far it seems to be working out well with no unexpected consequences but I am going to be doing much more testing before I try to incorporate it into the real database.

Mike,

I appreciate your input also, that's what I intended with that form, I just hadn't tried to put it to use yet, Thanks.

Thanks

Dave