?
Solved

List and choose parts from report or form

Posted on 2013-11-22
13
Medium Priority
?
103 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 38

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 38

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 38

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
 

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 38

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 38

Accepted Solution

by:
PatHartman earned 2000 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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

770 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