Getting certain data in table for each item in a subform

[Dave14867]

Hello all,

I will try to explain what I would like to do, hopefully it makes sense. I have 2 database that I created for work that to a large extent duplicate data but one has more functionality and I would like to incorporate the functionality of the other so I could use just 1. In the one I would like to stop using I have a table for each "Assembly", this allows me to have a combobox that is tied to that table in any other Higher Level Assembly that uses another Assembly. This ensures that the upper level assembly cannot use a lower level assembly that hasn't been entered yet.

In the database that has more functionality, I would like to incorporate this ability. All transactions are in 1 table. There is another table that I call a relationship table that has relationships built for all assemblies and the parts that go into them. When a Work Order is completed it gets entered and a query looks at the relationship table and populates a subform on the Work Order Form (and appends the data to the Transaction Table). The user then has to populate a field in the subform with the "Lot Number" for each part that was used.

I would like to have the same functionality as in the other database in that I would like to have it so the user can only enter a "Lot Number" for any given part in the subform that has already been entered as a "Work Order", if not, it won't accept the entry.

I hope this makes sense, If not let me know and I will try to clarify. Unfortunately the file is too large to post.

Thanks in advance
Dave

[Micron]

something about one of the table sets doesn't sound right. If an assembly consists of related parts, that seems to be a parent/child relationship. If an assembly consists of sub assemblies which in turn consist of parts, then you have the same sort of relationship but it goes one level deeper. In the simpler case, primary assembly records should be in one table, parts in another, and you have a linking table (many to many) that relates one assembly to many parts. This also provides for assemblies with options, although it can start to get rather deep in analysis depending on the business at hand because in that case, the assembly number could appear more than once in the many side due to variations on its build. However, IMO this should be handled by variations in the assembly record such as an appendix to the assembly number, so that you maintain a single assembly number to a group of parts.
Not sure if any of that will help. You probably could post a stripped down version of your db, removing whatever it takes to get it down to size when you zip it.

[Dave14867]

Micron,

It is a parent to child relationship, and it can consist of sub assemblies, and in some cases goes to other levels as you describe. This is handled in the relationship table, thats where I build the parent to child relationships. Everything works great and I get exactly the desired results. I just want to add this new feature to it as I explained.

I am trying to create a stripped down version now to be able to post to make it a little clearer what I am asking, but it seems as though you have a good grasp on what is happening now. I don't know if I can get it done today or not ( I am working on it in between other things at work) If I can get this extra functionality into the database, I can quit using the other one. The ability to ensure that a Lot Number has been entered has saved us numerous times. Essentially it allows us to verify the Lot Number was recorded correctly by the operators.

Dave

[Dave14867]

Micron,

I had gotten further than I thought. I think this will illustrate what I am trying to do. If you open frmWorkOrders, the first record is for a table and it shows correctly the parts and qty's needed. What I want is that in the Lot Number field in the subform, I want to create a combobox and for each part, only have the Lot Numbers available for that given Part Number.

Does that make sense?


Generic MRP -LotLookup.zip

Dave

[Dave14867]

I have added the Combo box to the subform but it doesn't work as desired, I get no results in it. The query it is based on though does seem to give the desired results.

Generic MRP -LotLookup-2.zip

Dave

[Dave14867]

OK. I think I know what is wrong but I don't know how to resolve it. In the attached file if you open the frmWorkOrders, the first record is for an Assembly that has 5 parts that are used in making it. PN 11-111 is also a made part that was made on Work Order "Legs Test". Query "qryWOSubformLotNumLookup" is the query I wrote to get the Lot Num's that are for the select row part number and it seems to be giving the expected results.

"Lot Num 2" field is a combo box that I would like to be populated with the results of the query. The name of the combo box is "CmbLotNumSelect", I believe I need to use "Me.CmbLotNumSelect=" probably in the OnClick event but I don't know how to reference the Query results.

Can someone point me in the right direction.

Thanks

Dave

Generic MRP -LotLookup-3.zip

[orange]

You may get some insight from this thread and this where a part hierarchy is discussed. It is the discussion that may be useful to you. It is critical to get your database structure designed and tested to ensure it meets your needs.

You are working without a relationship diagram?? No identified referential integrity?

Good luck with your project.

[Dave14867]

Orange,

I took a look at the thread you suggested and I think I am all set with that part of it. My file works great right now, I just want to add more functionality to it. I am using a generic file that simulates my actual file. What I am trying to do is based on the selected part in the subform, use a query to find out what lot numbers are associated with that part in the transactions table. The query is giving me the expected results, I just don't know how to have them show up in the combo box.

I believe it would be "Me.CmbLotNumSelect= ?" but I don't know how to retrieve the results of the query.

Thanks

Dave

[orange]

It seems that not all parts have an associated LotNum??
Can you give a specific -line by line description - to show exactly what you are having trouble with and what you expect/need as a result?
It's form frmWorkOrders, but what do I highlight to see exactly what your issue is?

[Dave14867]

Orange,

You are correct, no parts have lot numbers at this time. In the 1st record of frmWorkOrders, in the subform the 3rd row, PN 11-111 Table Leg.

In Field Lot Num 2, it's a combo box called "CmbLotNumSelect". I want to have that populated with the results of "qryWOSubformLotNumberLookup". The query seems to be giving me the desired results.

Not all parts have a lot number at this time. I just populated a couple of items while trying to get this to work.

Dave

[orange]

?? What should be in LotNum combo???

[Dave14867]

Orange,

"Legs Test". That is the Lot Number (Work Order Number) for the Lot I created for those parts. If you run "qryWOSubformLotNumberLookup" with that row select, that is the result you will get and it is correct.

Dave

[Dave14867]

Orange,

I believe I have it figured out. I use the Rowsource property to create the query and it seems to work.

Does this make sense to you? You are one of the people here I rely on for good input.

Thanks

Dave

[orange]

Hi Dave,
I've been out all day. Just saw your post.
Yes comboboxes have a rowsource.
You have another subform that I couldn't see where or how you were using it. Then had to leave.
Adjust your database and repost, so we can see it.
Glad you have it working.

[Dave14867]

Orange,

Here is my latest attempt. It is working for what I was wanting but it hi-lited an issue, or a possible issue. I would like this to work for items made in house, which it does, and also for purchased items (which it doesn't). It currently doesn't work for purchased items because when they are received the qty goes into a different field in the transactions table. So what I would need is a query that looks at 2 different fields, Either CycleCountCorrection OR UnitsRecieved, I'm not sure how to do that in the query builder. All of this is being done in the attached file for now which is very sparse on data. I don't want to try it on the acual file at this time because I don't want to adversely affect the table in that file.

I am thinking about creating an Update Query in the real file that would update any records with qty's in the CycleCountCorrection column and add them to the UnitsRecieved column, then change my original qry to look at the UnitsReceived column instead of the CycleCountCorrection column. That would also require then going back and changing the CycleCountCorrection values to "0" because of calculations that are done in various places for Qty Values. In Hindsight, maybe the cycle count field isn't such a good thing to have, I could always put a comment in the entry as to why units received was adjusted without a PO. What do you think?

Generic MRP -LotLookup-4.zip

Dave