gibneyt
asked on
List and choose parts from report or form
I have an Access DB that will be used to integrate third party BOMs of electrical hardware. This third party used their own part db to build their BOMS. I have integrated their part db into our MRP db.
In the Access db, initially their parts will be matched directly to out parts with exact matches on Manufacturer's Part Number. Next, their parts will be matched to our parts based on the specifications of the specific commodity code per part. This match will list one part from their BOM and all the specification matches from our MRP db, including their previously integrated list of parts. The idea being that we will try to match their part to one or more of our existing (stocked) parts. If we have possible matches the third party will choose one. If the third party doesn't like any of our possible matches we would then purchase the part and list it as a direct MPN match. Parts that have no MPN or specification match are purchased and then become direct MPN matches.
So far I have reports that list the three possible states of any given part; direct MPN match, possible specification match, and unmatched parts based on MPN and specifications. The direct matches will be able to be added straight to my BOM. Once parts are procured for the non matched parts they will added to the BOM too. What I need now is a way to take the list of possible specification matches and click a button or check a box next to one of my parts that they have chosen and have it placed into my BOM. Should I use a form or report? I have a report that lists nicely their part and my parts but how do I set up the choice mechanism?
Once the BOM is complete in the Access routine a .xml file will be exported and that list will build the actual BOM in my MRP system.
TIA,
Tim
In the Access db, initially their parts will be matched directly to out parts with exact matches on Manufacturer's Part Number. Next, their parts will be matched to our parts based on the specifications of the specific commodity code per part. This match will list one part from their BOM and all the specification matches from our MRP db, including their previously integrated list of parts. The idea being that we will try to match their part to one or more of our existing (stocked) parts. If we have possible matches the third party will choose one. If the third party doesn't like any of our possible matches we would then purchase the part and list it as a direct MPN match. Parts that have no MPN or specification match are purchased and then become direct MPN matches.
So far I have reports that list the three possible states of any given part; direct MPN match, possible specification match, and unmatched parts based on MPN and specifications. The direct matches will be able to be added straight to my BOM. Once parts are procured for the non matched parts they will added to the BOM too. What I need now is a way to take the list of possible specification matches and click a button or check a box next to one of my parts that they have chosen and have it placed into my BOM. Should I use a form or report? I have a report that lists nicely their part and my parts but how do I set up the choice mechanism?
Once the BOM is complete in the Access routine a .xml file will be exported and that list will build the actual BOM in my MRP system.
TIA,
Tim
PatHartman
If you are using A2007 or newer, you have event processing available to you. You could add an event to a control on the report that when clicked will run an update query to make the match.
ASKER
OK. But I think I have to build a repository table and the checkbox or button has to have some sort of control associated with it. Would I make table with the fields of the query in it plus the check box or button control? I am using Access 2010.
I'm not sure why you would build another table. I thought you were just trying to say that supplierA's partX is a match for our partY. Without knowing your schema, I can't say exactly how you would have to do this but I'm going to guess that you would find supplierA's partX record and update the empty OurPartNum field with partY.
ASKER
So let me back up a little here. See attached report for clarification. I have 3 other similar reports; one for RESistors, exact matches and no matches.
The report I have lists, per section, one of their parts and all of our parts that match their part specification. I envisioned either an augmented report with radio buttons next to each of our parts so they could choose one part, or a form that looked similar with radio buttons next to each of our parts. Once one of our parts was chosen for each of their parts a control would be clicked that wrote the selected records to a table.
That is the extent of what I know about how to make this work in Access. Unless you are thinking of something different?
rptCAP-Matches.pdf
The report I have lists, per section, one of their parts and all of our parts that match their part specification. I envisioned either an augmented report with radio buttons next to each of our parts so they could choose one part, or a form that looked similar with radio buttons next to each of our parts. Once one of our parts was chosen for each of their parts a control would be clicked that wrote the selected records to a table.
That is the extent of what I know about how to make this work in Access. Unless you are thinking of something different?
rptCAP-Matches.pdf
ASKER
So perhaps I need to restate the question. How do I take the report that is based on a query and add a check box control so I can select one of my part numbers that is listed?
Add code to the click event of a control in the detail section on your report. The code has to "select one of my part numbers that is listed". We don't know how to make this happen.
Open the report in Report View rather than print preview.
Click the control to execute your code.
Open the report in Report View rather than print preview.
Click the control to execute your code.
ASKER
I am able to apply either a check box or radio button to the detail section. In the report view I see the control, one on each line of the possible match section. Trouble is when I click any of the controls, all the controls are selected. How do I only select one from each section? How do I confine the control to that section? The radio button control would be the correct control as I only want to choose one selection from each part section.
Tim
Tim
I thought you wanted to run the code at that point. There would be no need for a separate control. You could add the click event to any existing control which is what I thought you would do. Selecting records for later processing is a different problem.
Unbound controls, since they have no ControlSource can have only a single value at one time. That's why when you click the checkbox, all visible copies also become selected You would need to add a column to a table and add that column to the query if you wanted to use that column to select records for later processing.
Unbound controls, since they have no ControlSource can have only a single value at one time. That's why when you click the checkbox, all visible copies also become selected You would need to add a column to a table and add that column to the query if you wanted to use that column to select records for later processing.
ASKER
Currently I have no controls in my report unless you are saying I can make a control out of one of the fields already in the report.
The report would come up and offer the list of choices. At that point someone would go through the list and select one radio button per choice per part. When a part is chosen it would be written to the appropriate table, these would be in-stock parts. Those parts where a selection is not made would be written to another table, those parts would have to be purchased. That would be the preferred operation.
So since, I don't think, I have any controls in the report based on a query, then I would have to go with a control in the report based on a table? I would have to make the current query create/update a table and the report will be based on the table. I'll have to add the control column to the table. Does that sound like the right way to proceed?
The report would come up and offer the list of choices. At that point someone would go through the list and select one radio button per choice per part. When a part is chosen it would be written to the appropriate table, these would be in-stock parts. Those parts where a selection is not made would be written to another table, those parts would have to be purchased. That would be the preferred operation.
So since, I don't think, I have any controls in the report based on a query, then I would have to go with a control in the report based on a table? I would have to make the current query create/update a table and the report will be based on the table. I'll have to add the control column to the table. Does that sound like the right way to proceed?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for all your time and input.