Creating an Efficient Item Search Tool for a Massive Database

Greetings Experts!  This question stems from the link below.  I have collaborated with an Expert to develop an Excel Help desk that is being used alongside an Access Database.  In addition, this Access Database is linked to an Oracle database.  

The intent here is to add a search tool that will enable the user to search for an item in a database and pull information relating to the item searched.  The database contains roughly 620,000 items, so all of my attempts have resulted in VERY long search times or endless loops/crashes in Excel.  

I have attached the Excel Help desk spreadsheet and the Access Database involved. Thank you so much for the help with this.  

Previous Question
ADS-Parts-Help-Desk.xlsm
TrackRequests.mdb
Christopher WrightDirector, Service DeliveryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I'll tackle it of course but I need requirements. I need to know at least what you want to search by, and what you want returned.

BTW I don't think posting the TrackRequests database will do anyone any good since it the ItemMaster is a link and I don't think that the linked table will be included in the attachment.
0
Martin LissOlder than dirtCommented:
Also do you want the Search to be another Request Type or do you want it attached to and existing Request Type as a button or do you want it as a button on the Help Desk sheet, or???
0
Martin LissOlder than dirtCommented:
As the ItemMaster currently is set up, the Item table has the ID (which is an autonumber field as the key and that doesn't help as far as searching for anything useful. If we change the key to the field that we search for I believe that that will speed things up considerably. If you can tell me which field we'll be using for most of the searches I'll change the primary key to that. The key can also be made up of mare that one field like Vendor Name + Vendor Part Number.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Christopher WrightDirector, Service DeliveryAuthor Commented:
To answer your question in Thread ID 39512307:
 - The search would be by Part Number.  But is there a possibility to search by other attributes tied to that part.  Such as search by Vendor Name to find all the part numbers belonging to that vendor.  But for now, I want to focus just on searching by the part number.

 Let's include the following attributes with the search return, if possible:

ADS PART NUMBER
INVENTORY ITEM ID
NSN
DESCRIPTION
VENDOR PART NUMBER
MANUFACTURER PART NUMBER
ADS COST
MSRP
COUNTRY OF ORIGIN
BERRY COMPLIANT

To answer your question in Thread ID 39512311:
 - Sorry for not specifying that.  Let's make the search as a button on the Help Desk form.  Since it technically is not a request which will be emailed, it would be beneficial for the user to search an item to find its attributes before they begin their request for anything else.  Your thoughts?

To answer your question for Thread ID 39512331:
- The primary search key would be the part number.  Could we create a search option where we could search by part number and vendor name?
0
Martin LissOlder than dirtCommented:
How will the user benefit from the search as far as the Hrlp Desk is concerned? I ask because right now after entering a vendor name in the NIUF they can see all the part numbers for that vendor, so what does the new search do for them?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
The new search offers the user the ability to search for an item and its attributes.  This would allow the user to confirm certain attributes before they submit a request.  For instance, the user can search if an item exists or not before they submit a NIUF request, or they can search the item and determine if it is marked as Berry Compliant 'Y' or not before submitting a request to have it changed.  And so on.  Currently, any item search is done by opening a spreadsheet with all items and performing a Ctrl+F.  Thank you so much for the help!!
0
Martin LissOlder than dirtCommented:
Thanks for the explanation. I think I have a better understanding now.
0
aikimarkCommented:
The goal will be to eliminate any joins between the Access database and the Oracle database.

1. Do you have the ability to create stored procs in the Oracle database?
2. Do you have the ability to create and populate any tables in the Oracle database?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Although I have write access to the Oracle database, I prefer to maintain a read only position with it.  So, to answer your question, I do not have the ability to do either 1 or 2.  Sorry.  :(
0
aikimarkCommented:
your problem solution might require some PL/SQL stored procs.  You will need the ability to create these and sometimes these use temporary tables.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Okay, No problem.  I will work with my Oracle DBA to make it happen.  If you don't mind, please lay out the guidelines so I can present that to him.  Thanks again for your help!
0
Martin LissOlder than dirtCommented:
aikimark, how would the performance of what you are proposing compare with ADO selecting against a key in an Access database?
0
aikimarkCommented:
@Martin

If the retrieved data requires any table joins, which is likely, then those joins should be done on the Oracle database.  Sometimes, the local (Access) data may be required to select the data.  This may also require some non-trivial amount of data to be transferred a temp table for server-side joining.
0
aikimarkCommented:
@xfitguru

Since I don't know what your database server data looks like, can only say that I'm pleased that you are working with your DBA.  You are not alone on your end of this problem and the DBAs will have the authority, knowledge and skills required to implement any of our suggestions an monitor and tweak the Oracle queries and tables.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Thanks aikimark.  I appreciate that.  I have a close relationship with my Oracle DBA team since they feel my pain in terms of enhancing our user interface between Office and Oracle.  I have been on the witch hunt to identify absurd and/or impractical processes and streamline them to the best of my ability and with A LOT of help from EE (especially Martin Liss!!).  Have a great day sir.
0
Martin LissOlder than dirtCommented:
Here's an update that is a proof of concept only. Some notes and questions:
1.    You should add an Index for Part Number in the Item Master Item table
2.    While the name of the file says it's version 46, internally it's version 45.
3.    When you talk with people at ADS, what do you call the Oracle database?
4.    The search function currently only asks for part number and in my fully fleshed-out version of this I plan on having two comboboxes at the top rather than the one textbox. One combo will list all the vendor names, and when one of them is selected the other combo will list the part numbers, and clicking one of the part numbers will do the search.
5.    In the search results is ADSCost = Vendor Cost (in the Items table)?
6.    In the search results is MSRP = Market Price?
7.    In the search results  do I need to convert the country code to the country name?

To run the search, click the new 'Search' button, enter a part number and click 'Search' on the form.
Is the search performance (by that I mean nits run time) acceptable?
ADS-Parts-Help-Desk-V46.xlsm
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Hello Marty.  I do apologize for the disconnect in the attribute names.  That was sloppy on my part.  I have provided answers to your questions below.  Thank you again for your help man!!

1. You should add an Index for Part Number in the Item Master Item table
- Roger that. Will do
2. While the name of the file says it's version 46, internally it's version 45.
- Noted
3. When you talk with people at ADS, what do you call the Oracle database?
 
- Most people do not even fully understand the concept of a database.  When it is discussed, it is referred to simply as "Oracle," "The System," or "The Database."  Primarily is is "Oracle" though.
4. The search function currently only asks for part number and in my fully fleshed-out version of this I plan on having two comboboxes at the top rather than the one textbox. One combo will list all the vendor names, and when one of them is selected the other combo will list the part numbers, and clicking one of the part numbers will do the search.
 - I absolutely love that idea of the dependent drop-down boxes.  This would knock out two birds with one stone.  They could search for the existence of their part number while also determining all items belonging to a Vendor.  One question; is the user allowed to just search for part number if they do not know the vendor name?
5.    In the search results is ADSCost = Vendor Cost (in the Items table)?
 
- Yes sir, ADS Cost = Vendor Cost on the Items table.
6.    In the search results is MSRP = Market Price?
 
- Yes sir, MSRP = Market Price
7.    In the search results  do I need to convert the country code to the country name?
 - If that is possible, please.  Users would not be as apt to recognize the two letter code versus the full name. Good catch there.  I didn't even think about that.
0
Martin LissOlder than dirtCommented:
4. "is the user allowed to just search for part number if they do not know the vendor name". I'll have to think about how to do that but what if there's more than one vendor with the same part number? I assume then that you'd want the user to be given the ability to choose the appropriate vendor, right? The complexity begins:)

Is the response time OK?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Good news is that our database does not allow duplicate part numbers.  So technically, a part number is unique.  Now, some issues have came up in the past with 0's preceding a part number thus resulting in 0250 becoming 250 in ,csv files.  That is why I wanted to get away from using spreadsheets as our source for data and use Oracle instead.  So, if we pull the part numbers directly from Oracle, there should be no issue with duplicate part numbers. Right?

The response time is CRAZY fast!! How did you get it that fast?  Good stuff man!
0
Martin LissOlder than dirtCommented:
Okay I won't worry about duplicate part numbers. And the Part Number Index allows ADO to go directly to that record which is fast.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Wow.  I am convinced.  It is fast! With the previous version, I was having speed issues when I go into the NIUF and hit the back button to return to the home page.  It appeared as though it was reading the entire list of item numbers every time.  Now it speeds right through it!!
0
Martin LissOlder than dirtCommented:
It appeared as though it was reading the entire list of item numbers every time.
It was, sort of:) In previous versions I was opening and closing the DB connection in several procedures. Now it pretty much stays open once it is opened. We've built this application piecemeal and for quite some time it didn't matter. As the complexity has grown I thought on occasion about making the change but it never really mattered and so I left it as it was. Once we linked to the Oracle database I should have done it, particularly since in another thread you said it was slow, but for whatever reason at that time I didn't think of making the change.
0
Martin LissOlder than dirtCommented:
Here's version 46 which includes the following.

o Added a Part Number Inddx in the Items table (Chris, make sure you've done this)
o Corrected a bug where Ctrl+2 macro was available from the NIUF (Chris have you ever used the Ctrl+1 and 2 shortcuts on the BOM?)
o Made several changes so that the DB connection stays open as much as possible
o Added a search function including a 'Search' button on the HelpDesk sheet
ADS-Parts-Help-Desk-V46.xlsm
0
Martin LissOlder than dirtCommented:
I forgot to mention that this version does what you want. You can either choose a vendor name and then select a part number and click Search, or you can manually enter a part number and then click Search. In the latter case the Vendor Name will be changed to the vendor associated with the manually entered part number.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Hi buddy.  The search tool is looking incredible.  I do have one question.  Can we change the order of the Vendor Dropdown and Part Number Dropdown?  

Also, just want to run something by you. I am having pretty long wait times while searching by the Vendor.  Is there any way to alleviate this or do you think it would be best if we limit the search to only part number and add the Vendor name as one of the search result fields?  The part number search is incredibly fast, so I thought I'd run it by you.

I did not catch this yesterday, but could we add another field to the search results.  The Expired flag?  This would be another attribute they would look for if attempting to have an item expired or not. Thanks again for all of your help buddy!!
0
Martin LissOlder than dirtCommented:
Can we change the order of the Vendor Dropdown and Part Number Dropdown?
Sure.
I am having pretty long wait times while searching by the Vendor.
What do you mean? All the vendors are listed in the 'Vendor Name' combo box when the userform is shown.
could we add another field to the search results.  The Expired flag?
Sure.

In postID ID: 39516717 I had a comment and a question.
o Added a Part Number Inddx in the Items table (Chris, make sure you've done this)
o Corrected a bug where Ctrl+2 macro was available from the NIUF (Chris have you ever used the Ctrl+1 and 2 shortcuts on the BOM?)
0
Martin LissOlder than dirtCommented:
I just made the "change the order of the Vendor Dropdown and Part Number Dropdown" change you requested but it IMO it doesn't have the right "feel". The way I planned this search function was that most of the time to have the user select a Vendor Name which automatically fills the Part Number combo box with all the parts for that vendor. He then selects the part he's interested in and clicks the Search button. Most solutions that involve two related comboboxes are done in a left-to-right manner with the primary combobox on the left. In my design the Vendor Name is the primary and having it on the left seems better to me than the right-to-left that results from the change. Are we using the search in the same way?

I'll do it either way, just let me know.
0
Martin LissOlder than dirtCommented:
Please also see post IDs 39518287 and 39518376.

When we created the TrackRequests database, all it did was what the name implies. It now also is the repository for the data that winds up in Form Codes and the place where official version numbers are stored, so how would you feel about changing its name to something more general like ADS.mdb or HelpDesk.mdb or some other name of your choosing?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Hi friend.  Sorry for the delayed response.  It's been a long day, and it's still going.  To address your questions:

In response to your first Thread ID 39518287:
What do you mean? All the vendors are listed in the 'Vendor Name' combo box when the userform is shown.
- You are correct, all of the vendors are immediately shown when the userform is initialized.  What I was referring to was when a Vendor is selected, it then takes about 45 - 60 seconds for the list of items belonging to that vendor to populate.  My apologies for not specifying that.
- I have added the Part Number Index to the table in the db.
- Yes sir, I have used the Ctrl 1 and 2 shortcuts (which come in very handy by the way.)
 
- Just wanted to note that when the Ctrl + 1 shortcut is used, the spreadsheets jumps from one window to another (working on multiple monitors). It works great with no errors, so it's not a big deal but I thought I would give you a heads up.  
 - Also, I am unable to manually enter a part number for the Kit on the Kit BOM form.  I am referring to the very first row which contains the "header' part number.  This is not a problem when it is automatically populated from the NUIF.  This only occurs when manually attempting to modify that field.  I have provided a screenshot for that.  
Screenshot

In response to Thread 39518376:
- I think you are  right.  I was looking at it backwards with looking at the part number as the primary.  You've made a great point and I think your plan fits the direction that we are going with this.  Well played Sir  :) .  Sorry for being fickle but if you can, I'd like to have it your way again with the Vendor combo box on the left.  Sorry man.  

In response to your most recent Thread 39518716:
- I agree.  It would be best to rename it since it has evolved a great deal since it was first created.  Thank you for catching stuff like that man.  You are the best.  

Again, I know I always say, but I sincerely and truly thank you so much for the help and for teaching me a TON!! You just don't know.  You're turning me into a prodigy at my workplace.  Hahaha  Thanks man!!
0
Martin LissOlder than dirtCommented:
What do you want to call the former TrackRequests.mdb?

Let me do some research on improving the 45 to 60 second time it takes for you to get the data. Note that in my smaller version of the ItemMaster it's seems almost instantaneous.

Also, I am unable to manually enter a part number for the Kit on the Kit BOM form.  I am referring to the very first row which contains the "header' part number.  This is not a problem when it is automatically populated from the NUIF.  This only occurs when manually attempting to modify that field
. It's my understanding that if a kit is generated from the NIUF that you should not be able to change that part number in the BOM. If my assumption is correct then you should not be able to change it.
0
aikimarkCommented:
@Martin

Do you have a feel for how much data can be brought down in some reasonable time period?  Maybe you can populate a recordset with all the item data and then filter it locally when you need a user-selected slice of the data for the combobox/dropdown.
0
Martin LissOlder than dirtCommented:
@aikimark: The production version of xfitguru's database has a table with over 600,000 records. Two of the fields in that table are Vendor Name and Part Number and I'd like to get all the parts numbers for a given vendor as quickly as possible. I assume that that means adding an Index to the table that consists of Vendor Name and Part Number which I did for the VendorPart Index you see here.
IndexAssuming that that was the right thing to do, how do I use that Index in the most efficient way possible. If it wasn't what should I do?
0
Martin LissOlder than dirtCommented:
Also I'm not sure what you mean by "Do you have a feel for how much data can be brought down in some reasonable time period? " but a given vendor may have from 1 to 100 or more part numbers.
0
aikimarkCommented:
@Martin

Are you showing me the index definitions on an (attached) Oracle table or an Access table that has all of the Oracle data?  The indexes need to be on the Oracle table.

I would create the index on the (VendorID , PartNumber) columns.  Searching text columns is much slower than a Long Integer field.  I assume that the VendorName does not related to different VendorID values and that you already have the Vendor data from a prior data request.

The use of an index is determined by the database engine, so this two-column index might not even be used if your query only references the VendorID column in the Where clause.

Also, some queries are more 'sargable' than others when it comes to avoiding table scans.

This is where my earlier comments come into play.  If you are trying to retrieve data from both the local Access table(s) and the Oracle table(s), you might be causing all the data to be brought down to Access instead of only the relevant rows.

I was thinking about caching the index data in a recordset variable that could, possibly, access the data in memory faster than another call to the Oracle database.  I do not know the source of the performance bottleneck from my vantage point.  It might be useful to add some performance metrics to your code.  Log the events to a table or text file.

=================
@xfitguru

Your DBA group should be able to capture some performance metrics on these queries.  Please share with use, the explain data.

Also, please ask them how the table defined.  If you are normally accessing the parts relative to each vendor, then a clustering arrangement might be optimal.
0
Martin LissOlder than dirtCommented:
The database that xfitguru and I are using is an Access database that has a table called Items1 that is a link to another Access database. User1 in production has 600,000+ records and contains Vendor Names (no dupes) and Part Numbers (no dupes) along with other data and the VendorPart Index is in that table. In a userform there is a combo box that contains all the User Names from the Items1 table. That's quick and easy. There's a second combo on the userform that is meant to be filled with all the Part Numbers associated with whatever Vendor Name the user selects in the first combo box. Before I added the VendorPart index I was using a simple rs.Open "Select * from Items1 Where [Vendor Name] = '" & cboVendorName.Text & "'", gConn to retrieve the data and xfitguru is finding that it takes almost a minute to retrieve the data. I thought that an Index like I just created would help but I don't know how to take advantage of it, or even if it will help at all.

In your comments you mentioned VendorID and I assume you meant Vendor Name which as I mentioned is the name of the field in Items1.
0
aikimarkCommented:
when you populate the combobox, have multiple columns.  The visible column will be the vendor name and a non-visible (zero width) column will be the VendorID.  Use the VendorID value in your query.  You will need to use the Columns property of the combobox to get this hidden value for the selected item.

I meant VendorID, which, I assume, is a numeric field, possibly autonumber type.
0
Martin LissOlder than dirtCommented:
That's an interesting idea and I'll try it.

What do you think of the idea of using a stored procedure?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Wow! I am learning a lot from your dialogue.  Two Experts sharing ideas, it's like Clash of the Titans on EE, except there really isn't a clash but merely friendly suggestions.  Both of you guys are way above me in terms of knowledge, but watch out, I'm catching up.  Thank you both so much for the help here!

@Marty
In response to Thread ID: 39519940:
Let's name it "ADS.mdb" or "ADS.accdb"  - I believe we are using Access 2007 now, correct? Either way, they are saved in the *.accdb format on my server. Should I change them?

@aikimark
I am coordinating with my DBA to collect the following info:
Performance data on the local Oracle table being used
- Once I have this, I will share and explain.
Local Oracle Table definition

Again, let me thank you both for showing interest in helping me.  I sincerely appreciate the help and am grateful for the shared knowledge here.  God bless!!
0
aikimarkCommented:
@Martin

I was thinking of Oracle stored procs in my initial comments.  You might have to pass a non-trivial list of values to a stored proc to simulate a table join on the Oracle server with some Access data.
0
aikimarkCommented:
@xfitguru

If multiple people are using this application, each user should have their own copy of the front-end (FE) database, containing the forms, reports, queries, code, etc.  There can be one or more back-end (BE) databases connected to the FE database.  I've seen some performance problems at client sites because of FE database sharing.

This thread is a good example of how EE should work.  You have asked a clear question, posted supporting/example files, correctly zoned the question, and actively participated with the experts.
0
Martin LissOlder than dirtCommented:
You said...
@Marty
In response to Thread ID: 39519940:
Let's name it "ADS.mdb" or "ADS.accdb"  - I believe we are using Access 2007 now, correct? Either way, they are saved in the *.accdb format on my server. Should I change them?
The current code refers to the mdb version of the database because we previously had problems with the accdb format, so yes you should change them and rename it ADS.mdb.

The attached Help Desk version includes the following:
o In the Search userform
    - Added Expired Flag to the returned values
    - Allowed more room for Description, and alphabetized the result fields.
    - Added a second, invisible column to the cboVendorName combobox, populated it from Vendor ID  and used that as the key for retreiving Part Numbers instead of Vendor Name (following aikimark's suggestion). In the SQL  for that I changed it so it returns just the fields it needs rather than doing Select *
    - Loaded the data for efficiently into cboVendorName by using the List property rather than looping through the table values
o Changed the name of TrackRequests.mdb to ADS.mdb

I have two questions.
1) Is the part number retrieval now faster and if so is it acceptable?
2) You never mentioned if there was any slowness with the Vendor Name combobox being filled. If there was is it faster now?

I'm also attaching the TrackRequests.xlsm file because it needed to be changed so that it refers to ADS.mdb rather than Trackrequests.mdb. Even though we changed the name of the database I don't think that the name of this Excel file should be changed because all it does is track the requests.
ADS-Parts-Help-Desk-V47.xlsm
Track-Requests.xlsm
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Sorry for the delay.  I just got a chance to review the files.  The Track Request looks great.  The spreadsheet said version 8 so I went ahead and put that in the DB as well.  I had a couple of issues at first then realized that was the issue.  The Help Desk is amazing man!  It is exponentially faster!!!  Wow.  

The Vendor Name combobox is filled instantly. When I search by a specific part number, the Vendor name is there instantly.  When I open the search userform, the combobox fills instantly.  I have not seen any slowness with that whatsoever.

The part search is way faster.  It is easily acceptable.  I am still running through a few tests but so far, it is GREAT!! Thank you so much Marty!!!
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Uh oh.  I am running into a problem when I go into the NIUF and try to go back to the home screen.  The error message below pops up:

Error Msg
This is the line of code that is highlighted to debug - It is located in
 'Public Function ValidateNIUF() As Boolean'

 rs.Open "Select Count(*) as rsCount from Items1 Where [Part Number] = '" & .Cells(lngRow, C.VendorPN).value & "' and [Vendor Name] = '" & .Cells(lngRow, C.VendorName).value & "'", gConn

Open in new window

0
Martin LissOlder than dirtCommented:
When you were testing, did you go into the Visual Basic Environment at any time? If so that will cause that error and you can restore the connection by typing Ctrl+Shift+R (where R is for Restore connection).
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Roger that.  It worked.  Thanks for the tip.  Sorry for the silly question.  This question is answered as far as the item search is concerned.  I am still working with my DBA to get the performance metrics for the table in Oracle.  Can I go ahead and mark this as complete and post my next question relating to a New process on this form?  This search is phenomenal!  It's really fast for both searching the item and for the vendor search (filling the combobox).  It's great.  Thank you so much for the help again! :)
0
aikimarkCommented:
If you don't have any performance issues, it probably isn't necessary to gather the data I had mentioned/requested.

If you still have any performance issues related to this question we prefer you leave the question open until the problem(s) have been resolved.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Roger that aikimark.  Thanks for the update.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
@MartinLiss

Hello Marty, I have been testing the form all morning and have a couple of questions.  

Question 1:
While conducting a vendor search, if I type in the vendor name rather than manually select it from the list, it does not return any results.  Even if it is identical to what is on the list.  The box auto-populates anyway, so when I start to type a vendor name in the box, it auto-completes the name for me, yet there are no results returned.  The only way is to open the list of values and manually click on that vendor name.  Was this designed to do this or an issue that can be fixed?

Question 2:
My next question is concerning the search on the Kit BOM form.  When I select a vendor name and then click the 'Select Part Number', this takes around 90 seconds to return the list of part numbers for that vendor.  I was curious if we could have it search the same as the userform which is MUCH faster.  I realize this is dealing with a different form, so if you'd like, I can post a new thread relating to that.  Thank you again for all of your help man!!
0
Martin LissOlder than dirtCommented:
1) I'm not at home right now but ire ember from my testing that if, say, I started to type Oakley into the Vendor Name combo that by the time I got to Oak the Oates would be listed. Doesn't that happen for you?

2) I'll look into that when I get back home on Monday.
0
Martin LissOlder than dirtCommented:
I can't edit my post on my iphone but Oates should have been Parts.
0
Martin LissOlder than dirtCommented:
For Q1, could it be that you chose a vendor that doesn't have any parts?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
To answer your first question.  When I type Oakley into the combobox, the items do populate by the time I have finished typing.  

I double checked the vendor and verified that it has parts.
0
Martin LissOlder than dirtCommented:
Here version 48 which includes the following changes.

o In the "SELECT PART NUMBER" search use Vendor ID instead of Vendor Name which is much faster
o Made several changes to the Search, including the following
    - Removed the "No parts found for vendor" message in favor of "- None -" in  cboPartNumber
    - Changed code so that cboPartNumber is not filled until the  the Vendor Name is selected from the list, the entry is clicked or Enter is pressed. Note that former problems with certain Vendor Names seemingly not working were do to the fact that formerly the "Click" event of the combobox was used as a trigger to fill the part number list, but that event only fires when the letters typed are unique in the list. For example as soon as "OA" was typed the click event would fire because "OAKLEY" is the only vendor that starts with "OA", but when typing the letters in "BENCHMADE KNIFE CO", the Click event would not fire until "BENCHMAD" was typed because there's another vendor named "BENCHMARK CRAFTSMEN" and you need to type until the "D" to make the letters unique.
ADS-Parts-Help-Desk-V48.xlsm
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I really like the functionality!  It returns results very fast for both part and vendor searches.  I do have one concern.  I hope it isn't petty but I just want to avoid any confusion.  When I click into the Vendor Name, by default it states, "- Select Vendor Name -" but still allows me to enter text into that field.  See the picture below:
Search Form Sample Scenario

When I do this, I receive the error message below.  While it is not an issue for me, I am concerned about my users.  Can we make the default "- Select Vendor Name -" unwritable?
Error Message
0
Martin LissOlder than dirtCommented:
Add line 3 to 7 and let me know if that works for you.


Private Sub cboVendorName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If cboVendorName.ListIndex = 0 Then
    KeyCode = 0
    Beep
    Exit Sub
End If

'******* 48b Start *******
If KeyCode = vbKeyReturn Then
    SelectVendor
End If
'******* 48b End *********
End Sub

Open in new window

0
aikimarkCommented:
There is a LimitToList property for combobox controls that should be set to True.
0
Martin LissOlder than dirtCommented:
There is a LimitToList property for combobox controls that should be set to True.
I believe that that only applies to comboboxes in Access.
0
aikimarkCommented:
True.  I didn't know if you might be using a compatible combobox control.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
That seemed to do the trick.  Now the user cannot add values to the default '- Select Vendor Name -'.  

I am not sure if this was happening before, but now if the user selects a Vendor name from the drop down and hits the space bar, Delete, or any other button, the value is replace or removed.  Then, when the Search button is clicked or the Enter key is pressed, the same error message populates.  Please forgive for not noticing this before.  I hate not giving you all of what I find all at once.  Sorry about that friend.  

Step 1Step 2Error Message
0
Martin LissOlder than dirtCommented:
No problem. To fix it add two pieces of code.

1) In the SelectVendor sub change

If cboVendorName.Text <> SELECT_VENDOR Then

Open in new window


- to -

If cboVendorName.Text <> SELECT_VENDOR And Trim(cboVendorName.Text) <> "" Then

Open in new window


2)  Add lines 7 to 9 in the following sub.
Private Sub cboVendorName_Change()

    '******* 48b Start *******
    cboPartNumber.Clear
    '******* 48b End *********
    'new
    If Trim(cboVendorName.Text) = "" Then
        cmdSearch.Enabled = False
    End If

End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Here's version 49. I Made a bunch of changes to frmSearch including:

o Getting rid of "- Select Vendor Name -" since it just got in the way
o Made it so that the only time the Search button needs to be clicked is when a part number is manually entered
o Added a ToolTip for the Description so that the complete description can be seen when the description is very long like for part '020413PR -03'
o Added code to handle the pasting in of the part number

I've tested it pretty thoroughly but don't let me stop you from doing more.
ADS-Parts-Help-Desk-V49.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher WrightDirector, Service DeliveryAuthor Commented:
This looks great.  I have tested and have not had any issues as of yet.  It goes much faster now than when we started.  Thanks to aikimark for pointing some things out for you.  I'd like to thank you both for your help and support here.  Truly phenomenal!!
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
This looks great.  I have tested and have not had any issues as of yet.  It goes much faster now than when we started.  Thanks to aikimark for pointing some things out for you.  I'd like to thank you both for your help and support here.  Truly phenomenal!!
0
Martin LissOlder than dirtCommented:
As always you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I have posted another question concerning a brand new "module" I am attempting to add to the Help Desk.  This will involve Purchase Orders and allow the user to submit requests to have Purchase Order attributes updated or have the Purchase Order line cancelled.  I am sure I have not provided the best explanation so please feel free to ask away if you decide to help out with this one.  Again, I really appreciate all of your help and support. I really have learned a lot from you.  That's something I will always keep close.  

New Help Desk Question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.