Solved

List and choose parts from report or form

Posted on 2013-11-22
13
59 Views
Last Modified: 2015-09-30
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
Comment
Question by:gibneyt
  • 8
  • 5
13 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39670210
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
 

Author Comment

by:gibneyt
ID: 39670229
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39670256
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
 

Author Comment

by:gibneyt
ID: 39670333
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
 

Author Comment

by:gibneyt
ID: 39671481
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39671942
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:gibneyt
ID: 39672111
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39673309
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
 

Author Comment

by:gibneyt
ID: 39674762
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39674912
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
 

Assisted Solution

by:gibneyt
gibneyt earned 0 total points
ID: 39706955
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
 

Assisted Solution

by:gibneyt
gibneyt earned 0 total points
ID: 39984092
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
 

Author Closing Comment

by:gibneyt
ID: 41017734
Thank you for all your time and input.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now