Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

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
0
gibneyt
Asked:
gibneyt
  • 8
  • 5
3 Solutions
 
PatHartmanCommented:
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.
0
 
gibneytAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gibneytAuthor Commented:
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
0
 
gibneytAuthor Commented:
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?
0
 
PatHartmanCommented:
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.
0
 
gibneytAuthor Commented:
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
0
 
PatHartmanCommented:
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.
0
 
gibneytAuthor Commented:
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?
0
 
PatHartmanCommented:
If your report shows anything, then you have controls.  Controls are like windows into your data.  Bound controls show/update the data for a specific field.  None of the report's controls as yet has any event code.  That is what we are talking about adding.

Let's start from the beginning.
You have a table of parts you want to do something with.  Currently these parts are outside of your application and you want to use this process to specify which table each of these parts should be moved to.  Let's call the tables Source, InStock, and Buy

Here is one possible solution.
Create a form to run the process.  Add one button to open the report and a second to run the update.
Add a destination column to the source table.
Add an option group to the report with the options InStock (1) and Buy (2)
In the Click event of the option group, run an update query that updates the value of destination with either a 1 or a 2.
When the user is happy with his selections, he presses the update button on the form.
This update button has to run three queries.
Qry1 - select all the Destination options with a value of 1 and append them to the InStock table.
Qry2 - select all the Destination options with a value of 2 and append them to the Buy table.
Qry3 - delete all the Source rows where Destination is not null.

I chose this method because it gives the user the flexibility to stop in the middle and come back to it and see where he is.  Nothing permanent happens until the user actually runs the update to move the records to a new table and delete them from the temp table.

I'm assuming that this is a multi-user database.  That makes processes like this that use temp tables problematic.  If you put the temp table into a shared BE, each row needs to be associated with an individual user because you don't want them interfering with each other's data if they happen to be doing this process at the same time and all your queries need to include the userID so they only impact Joe's data or Mary's data.
0
 
gibneytAuthor Commented:
PatHartman,

I'll have to take your efforts into consideration.  My db is not complete yet and I still have lots to do.

I will have to postpone this question until I can try to incorporate your suggestions.

Tim.
0
 
gibneytAuthor Commented:
Pat Hartman,

This process development has been farmed out to a professional.  Thank you for your time.

Admins,

Please close or delete the question.

Regards,
Tim
0
 
gibneytAuthor Commented:
Thank you for all your time and input.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now