Solved

Modifying a Homemade Help Desk

Posted on 2013-06-18
125
344 Views
Last Modified: 2013-07-08
I am in need of cleaning up some quirks that were found with a homemade help desk created and developed by my good friend and myself.  The link to the question that this spawn from is below.  The quirks are described in Thread ID: 39256689.  Thanks for all of the help!!

Source Question
ADS-Parts-Help-Desk-V34.xlsm
TrackRequests.mdb
0
Comment
Question by:Christopher Wright
  • 67
  • 58
125 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39256873
I'm on it.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39256951
Do you want the same 80/30 length validations for the BOM Component Description and Component Part Number and for the IIN's ADS Part Number?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39257896
Question 2:

In your observations you say
- Whenever a second kit is needed, how do you have the New footer populate?  Is it designed to be automatic? I had one kit which already existed on the form; however, whenever I tried to add a second, I simply added new rows.  The header or footer for the second kit did not auto-populate.  I started clicking around which resulted with the header becoming highlighted and footer appearing.  I restarted the form and tried to replicate on multiple occasions but to no avail.  

- When I was able to successfully create the header and footer for a second kit, I noticed that the header did not contain the all of the information the first had.  It did not auto populate a Qty of 1 and did not give the Extended Cost.  The footer did not calculate all of the rows either.  I tagged this to the Extended Cost issue.
I'm confused because it looks like you're talking about manually adding kits and to add the footer to the second or subsequent kit you do it just like the first kit - by selecting "END OF KIT" from the Component Part Number drop down. When I did that - no muss to fuss - I got this
????I do notice that I have a small problem with the second kit in that the Total Kit Cost s/b $7,104.00 but I'm sure I can easily fix that, but please tell me if you still have a problem adding a second kit.
0
 

Author Comment

by:Christopher Wright
ID: 39257960
I tested the footer and, you were correct, it populated as designed so no worries on that part.  Unfortunately, I am still getting the error message once I have emailed the form.  

Error
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39257986
What about my question in Post ID: 39256951?
0
 

Author Comment

by:Christopher Wright
ID: 39258005
My apologies for the oversight Marty. I DO want the same 80/30 validation for every sheet in this program. This is is the limit that has been set for our Oracle database.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39258129
In your observations you say

I'd also like to add one more column with a drop down for the user to select in order to explain where they obtained their price for that particular item.  This column would also be used for the Update Pricing Selection as well.
I assume that that means that the new column would only appear when 'UPDATE EXPIRE STATUS FOR INVENTORY ITEMS' is selected and when "Update Pricing Selection" is selected, but what is the latter?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39259825
You also say

when the user selects Help and another user happens to be in that file, this causes a "freeze" of some kind.  The sheet locks up and the cursor is in an endless loop.


Have you done this?

1.    Open Windows Explorer (right click on "Start" and then click "Explore") and then navigate to the folder containing your Word document.

2.    Right click on the folder and click "Sharing and Security..." to open the sharing section of the folder's properties.
 
3.    Check the "Share this folder" check box and then click on the "OK" button. The folder is immediately shared, and is accessible by any user on your local network.


Read more: http://www.ehow.com/how_6527080_share-word-document.html#ixzz2Wfw2KixO
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39259858
Also
Now if the user selects more than one, can we have it include all?  Such as 'Update: Berry, Category, Cost, COO'?
 
Are you saying you want to be able to do more than one type of IIN request? While you can physically select more than one Request Type, only the first one selected is acted on, so unless I misunderstand what you're asking, the answer is no.
0
 

Author Comment

by:Christopher Wright
ID: 39260008
Greetings Marty.  Sorry for the delay in responding.  I have provided answers to your questions below:


Thread ID: 39258129
- Sorry, I described it in the wrong way.  Here you go.
   - 'Update Pricing Selection' = 'UPDATE COST FOR ITEMS'

Thread ID: 39259825
- Everyone has permission to access the folder and its files.  What I am referring to is the error message that the Help file is going to be Read Only because someone else is already viewing the file and has it open.  

Thread ID: 39259858
- We already have it designed to allow the user to do more than one type of IIN request.  With the exception of the NIUF and the Kit BOM, when more than one Request Type is selected, the IIN sheet simply unhides the columns associated with each request.  I don't want any changes for that.  I am asking if we can somehow make the .Subject line for the email to be named dynamically based on the Request Types that have been selected.  


I hope I made sense.  I you need me to explain further, I would be more than happy to do so.  Thanks again for all of your help and support here!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39260072
Thread ID: 39259825
I can't test this since it's just me, but look at the file's Properties,. Is it Read-Only? If it's not you'll need to get other EE help to solve this.

Thread ID: 39259858
We already have it designed to allow the user to do more than one type of IIN request.  With the exception of the NIUF and the Kit BOM, when more than one Request Type is selected, the IIN sheet simply unhides the columns associated with each request...I am asking if we can somehow make the .Subject line for the email to be named dynamically based on the Request Types that have been selected
Yes, there is the ability to do more than one type of request, but not at the same time, so there is no "Request Types" only a single "Request Type" and yes, I can (and have in the code I'm working on) change the subject to something like "Update Category For Inventory Items Request".
0
 

Author Comment

by:Christopher Wright
ID: 39260163
Okay, I see what you mean.  We do not have the ability to do these at the same time.  I was mistaken on my end.  It makes sense now. You are already working on the subject line so that was ultimately what I was alluding to anyways, so I think we're on the same page with this one.  Thanks Marty!
0
 

Author Comment

by:Christopher Wright
ID: 39260319
Whenever I attempt to open an older version of the Help Desk file, I am getting a couple of error messages.  At first, I get this error message:

First Pop Up
I click OK and then this error message pops up.

Second Pop Up
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39260447
This is hard for me to test but the file it is looking for is

I:\Chris Wright\Parts Help Desk\ADS Parts Help Desk Vxx.xlsm

where 'xx' is the current version number. I assume because you are having this problem that that's not the name of the file on the server. Is it just this?

I:\Chris Wright\Parts Help Desk\ADS Parts Help Desk.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 39260897
I am using the file location/name
I:\Chris Wright\Parts Help Desk\ADS Parts Help Desk Vxx.xlsm.

During testing, I attempted to open
I:\Chris Wright\Parts Help Desk\ADS Parts Help Desk V33.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39260948
Try to open this file. When it fails, go to the Immediate Window, copy everything you see there, and paste it here.
Test-Open.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 39260969
The file did not fail.  Do you want me to change something to force it to?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39260974
Oh I forgot. Find Public Const FILE_VERSION = 34 and change 34 to anything else. Also clear your Immediate Window before opening it.
0
 

Author Comment

by:Christopher Wright
ID: 39260986
Here you go:

ThisWorkbookName is Test-Open.xlsm
ThisWorkbookPath is C:\Users\cwright\Downloads
strTempFilePath is 
strTempFileName is 
ActiveWorkbook.SaveAs is C:\Users\cwright\AppData\Local\Temp\ADS Parts Help Desk19062013175525.xlsm
Kill Test-Open.xlsm

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39261009
Download this file, clear your Immediate window and try it again please. I took care of changing FILE_VERSION.
Test-Open.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 39261029
Here you go:

ThisWorkbookName is Test-Open.xlsm
ThisWorkbookPath is C:\Users\cwright\Downloads
strTempFilePath is C:\Users\cwright\AppData\Local\Temp\
strTempFileName is ADS Parts Help Desk19062013182152.xlsm
ActiveWorkbook.SaveAs is C:\Users\cwright\AppData\Local\Temp\ADS Parts Help Desk19062013182152.xlsm
Kill C:\Users\cwright\Downloads\Test-Open.xlsm
FileCopy I:\Chris Wright\Parts Help Desk\Test-Open.xlsm

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39261163
When a user opens his out of date version of the Help desk I assume the name of his file will be ADS Parts Help Desk.xlsm so that is what I named this file. I also assume that the file on the server will have the same name.
ADS-Parts-Help-Desk.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 39261241
Roger that. I will ensure that it will be named that on the server and for the user's file.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39261300
So did you test again with the file in post 39261163?
0
 

Author Comment

by:Christopher Wright
ID: 39261498
Once I am able to get back to my pc, I will retest.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39263190
I'm building the Access "Form Codes" source tables and I noticed several things in the Form Codes sheet of the Help Desk:
The data in column 'S' of the Form Codes sheet is apparently not used.
There are 4 rows in that column with values of 102, 103, 202 and 142
We have an unused variable (actually a named range) called Organization_ID that has a value of 103
In the Help Desk code we have a line of code that sets the value of the 'INV ORG' column to a hard-coded value of 103.

So…
Can I ignore or delete column 'S' or do you want to keep it there even though it's not used?
0
 

Author Comment

by:Christopher Wright
ID: 39264080
I retested the file to address Thread ID: 39261498.  I encountered the error message below.  

Permission Denied
Text in Immediate Window
0
 

Author Comment

by:Christopher Wright
ID: 39264086
To address Thread ID: 39263190, please go ahead and delete Column S since it is not used or needed anymore.  Thanks Marty
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39264339
In the Debug.Print output "ThisWorkbook.Path" refers to your 'I' drive which is your server, right? Are you trying to open an old file there?
0
 

Author Comment

by:Christopher Wright
ID: 39264372
Yes sir.  I'm opening an old file in the same folder as the current Help Desk file.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39264405
That's not what I had in mind when I set this up. My vision was that there would be just one Help Desk file on the server which would always be the current version and the "old version updating" would go like this.

1.    I or someone else creates a new version of the Help Desk
2.    You upload that file to the server along with the database that contains the number of the new version
3.    Some time after that a user opens his copy of the Help Desk and when the code finds that the version is out of date, the code that we've been testing will replace his outdated Help Desk with a copy of the one that's on the server.
0
 

Author Comment

by:Christopher Wright
ID: 39264428
That was what I had in mind as well.  What I did was keep that one file current with the changes you made.  Maybe I tested this wrong.  I am going to save the file to my desktop and then attempt to open it.
0
 

Author Comment

by:Christopher Wright
ID: 39264455
I apologize Marty, I was going about testing it all wrong there.  I opened the test file from Thread ID: 39261163 and it worked according to design.  I was the culprit here.  Sorry
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39264458
Okay, that's actually good.
0
 

Author Comment

by:Christopher Wright
ID: 39264487
I dod have one question however.  When the file is reopened, it re-opens the file from the Temp location.  Is there anyway to have it reopen from the original location that the user had the out of date version saved?  Thanks

Temp Name
Immediate Window
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39264499
That's a mistake which I'll correct.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39268295
Okay, a lot of new stuff in this new version.

1. Cut file size in half by deleting blank rows at the end of each sheet.

2. Added field length validations as follows:
NIUF - Add validations for the length of Description, Vendor  Part Number and   Manufacturer Part Number. Also changed the  format of the part number column from General to Text so that long all-numeric part numbers don't get changed to scientific notation.
BOM - Did the same for Component Part Number and Component Description. For the latter there was preiously NO validations  so for example the description could be blank so I made the validations exactly the same as for the NIUF.
IIN -Did the same for ADS Part Number as was done for the NIUF part numbers
3. Validate for orphaned footers and added a tweak for In Oracle so that it updates sooner

4. Corrected a bug where pressing the Home button after completely deleting kits would cause error 91.

5. Corrected a bug where Total Kit Cost would include the kit part cost if the first line(s) in the kit where blank.

6. Remove duplicate part numbers from the IIN. This is done now when the data is transerred to the working form, similar to the way it's done for the NIUF.

7. Added Price_Source (column AU) in Form Codes. Show Price List Value column and new column Price List Source when Expire or Price List requests are selected.

8. Corrected bug where pressing Cancel was ignored when emailing with attachments.

9. Changed the email Subject for IIN reports to reflect the specific request.

10. Added a welcome message for first-timers (everyone will be considered a first-timer once)

11. Added code to use Access as the source for most of the tables on the Form Codes sheet. This included:
- Added 14 tables in our Access database to hold the data for the Form Codes sheet
- Added a 'Form Codes Ledgend' sheet which is not used by the code but rather is there to just document the relationship between the Form Codes "tables" and the Access tables.
- Updated 2 or 3 recently added Named Ranges so the their RefersTo string is dynamic like most of the rest of the other Named Ranges rather than being a static range.
- Made a copy of the original Form Codes sheet called 'Form Codes (2)' as a backup for a while in case errors are found in the updated Form Codes sheet.
12. Temporarily (at least) just tell the user to download the new file when they open an old file

It's important to note that because of change 11 that from this point forward all changes to the Form Codes tables should be made in the Access database. You can use the new Form Codes Legend sheet to help you figure out which Access table to update.

As I said above there's a lot of new code in this version and I have to apologize because while I did some testing it's not what I would call fully tested - I just don't have the time.
ADS-Parts-Help-Desk-V35.xlsm
TrackRequests.mdb
0
 

Author Comment

by:Christopher Wright
ID: 39268443
So far everything is working properly.  I won't be able to test fully until I am back into the office and can sit with my user and watch her go through everything.  As far as aesthetics, the icons on the IIN form are showing up outside of the form boundaries when I select four or more requests on the Homepage and click OK.  See screenshots below:

Selected Requests
Selected Requests 2
Resulting Icon position:

Far Right Icon Position
Now the weird part is when I select a certain combination of requests and click OK, the Icons are positioned accurately.  It's weird.  See screenshots below:

Selected Requests (No Issues)
Icons in Correct Position With Multi-Selected Combination
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39268465
As I mentioned previously, no matter how many you select only the first one in the list gets acted on, but what is probably happening is that regardless of that, the icons are probably getting positioned on every click resulting in showing the sheet for the first one but positioning the icons for the last one. I'll look into it. Also IMO the forms controls supplied with Excel are pretty crude as compared with the ActiveX equivalent ones and I couldn't find a way to limit the selection to just one request. I'll give that another look too.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39268517
I figured out how to restrict the selection to just one Request Type and as I expected the icons now behave.
0
 

Author Comment

by:Christopher Wright
ID: 39268723
I agree that Activex is the better way to go but coding them kicks my but as well.  They're quirky!  Thanks again my friend!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39268729
It's the other way around. We are using only form controls which IMO are crude, but ActiveX controls more robust and easier, but maybe that's because I have 20 or more years experience using them:)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39268743
Good news. I believe I also have the version control problem fixed. I developed the code in a test workbook and now I have to copy and adapt the code to our project.
0
 

Author Comment

by:Christopher Wright
ID: 39271053
That's great news.  Should I hold off on testing the latest version this afternoon?  Or should I move forward with that and test?  Thanks so much man!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39271568
Okay here's 36. The changes include

o Restrict selection to just one request type
o Worked out how to do version control.
o I also added some Application.Statusbar messages when starting up the app and when emailing since especially in the case of emailing it can take a few seconds to do the processing and the message lets the user know that something's going on.
A message
Please update your Access database to reflect version 36.
ADS-Parts-Help-Desk-V36.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 39271791
Hey my friend, I have a question.  You state that you have:  
Restrict selection to just one request type

This is taking away from functionality that we developed a while back.  I don't know if you recall that we purposely enabled the user to have the ability to select multiple requests with the exception of the NIUF and the Kit BOM forms.  The IIN requests were on the same sheet so you simply hid/un-hid the appropriate columns. The link to the question which established this is below.  Does the form not have the ability to have this anymore?  I'm just curious. Thanks my friend.

Multiple Requests Question
0
 

Author Comment

by:Christopher Wright
ID: 39271892
Also, my users with Excel 2007 are seeing this message box when they open the program.  Is this intended?  Just wanted to give you a heads up buddy.  Thanks

Message Box
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39271940
This is taking away from functionality that we developed a while back.  I don't know if you recall that we purposely enabled the user to have the ability to select multiple requests with the exception of the NIUF and the Kit BOM forms.  The IIN requests were on the same sheet so you simply hid/un-hid the appropriate columns. The link to the question which established this is below.  Does the form not have the ability to have this anymore?  I'm just curious. Thanks my friend.

I'm don't understand what you are saying because in post ID 39260072 I said
Yes, there is the ability to do more than one type of request, but not at the same time, so there is no "Request Types" only a single "Request Type"...

and in your response in post ID 39260163 you said
Okay, I see what you mean.  We do not have the ability to do these at the same time.  I was mistaken on my end.  It makes sense now....
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39271953
my users with Excel 2007 are seeing this message box when they open the program
Do they just see it just once, each time they open the program?
0
 

Author Comment

by:Christopher Wright
ID: 39272526
Hello Friend.  Pertaining to Thread ID: 39271940:

I guess I did not grasp the concept as well as I thought.  At first I thought that there would be multiple requests available but when you stated:
Yes, there is the ability to do more than one type of request, but not at the same time, so there is no "Request Types" only a single "Request Type"...
I was under the impression that the user could still select more than one request and click Ok and when you stated that, it meant that all of the requests would be treated as though they were only one request when it was submitted.  For example, the user selects more than one request and when they submit and email, only one spreadsheet is attached to the email with all of the information needed for each request.  Forgive me for the confusion, I really did not mean to get lost with that one.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39272554
I'm sorry for the confusion but at least for now it's one email for each request. Since it's been that way for a long time I'm surprised we didn't notice that we weren't on the same page:(
0
 

Author Comment

by:Christopher Wright
ID: 39272591
It's no worries.  I am to blame for that one.  I remember the user having the ability to update the spreadsheet with only the columns that were needed being visible.  I just assumed that it sent as one email.  

Even if it sends multiple emails, that will be fine, just as long as the user can still update the IIN spreadsheet like it was before.  For example, if they selected multiple requests, then each column needed would be shown.  Is that still possible?
0
 

Author Comment

by:Christopher Wright
ID: 39272604
Sorry I was updating the other post to answer your question but you beat me to the punch so I have to add a new comment.  So, for Thread ID: 39271953, the users were clicking yes and it was getting stuck with the message bar stating "Initializing number 2 of 13 tables (15.4%)."  I looked into it and found that they are having to click the yes button to initialize each table one by one.  After they have clicked yes to have all of the tables initialized the sheet works as designed.  Once they save and close, the same message shows when they re-open.  Then, it still requires each table to be initialized one by one.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39272895
D__m. I was getting that error myself but one small change fixed it me. I'll take another stab at tonight but if it's not too annoying to you and your testers, please continue testing and let me know what other errors you find.


As for IIN I believe it does what you want but that's something you can verify for yourself.
0
 

Author Comment

by:Christopher Wright
ID: 39273186
Roger that. I'll continue with our testing.  Thanks Marty!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39273282
my users with Excel 2007 are seeing this message box when they open the program
Do you get this message?
0
 

Author Comment

by:Christopher Wright
ID: 39273359
No sir, I do not.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39273367
Do they have the same permissions on the server that you do?
0
 

Author Comment

by:Christopher Wright
ID: 39273375
Yes sir, for this particular server they have the same.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39273377
I understand that they use Excel 2007, what about you?
0
 

Author Comment

by:Christopher Wright
ID: 39273393
I am using 2010 as well several other users on my team and quite a bit more throughout the company.  It's limited to 2007 and 2010 though.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39273414
You probably can't do this tonight but tomorrow could you try this for me please?

In the 'Functions' module you'll find a procedure called 'AddOnetable'. In that procedure, someplace before the With ActiveSheet.ListObjects.Add(SourceType:=0... line add this new line.

Application.DisplayAlerts = False

and put Application.DisplayAlerts = True just before the End Sub.

Give that modified wb to one of your 2007 users and tell me if they still get the error message.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Martin Liss
ID: 39273419
BTW do you have the ability to add Excel 2007 on your PC? I only have 2003 and 2010 so it would help with testing for your 2007 users.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39273776
I did some research and according to this thread which is exactly our problem, my suggestion in Post ID 39273414 will work:)
0
 

Author Comment

by:Christopher Wright
ID: 39274467
Success!! It works.  It took approximately 45 seconds to initialize all 13 tables and no message boxes were displayed.  Thanks Marty!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39274751
You're welcome but on my machine where I'm running Excel 2010 in Windows XP (emulated on a MAC) it only takes about 5 seconds so I'm disappointed at the results.
0
 

Author Comment

by:Christopher Wright
ID: 39275433
I am running 2010 on mine as well.  It only takes around 5 seconds for me as well.  I am curious as to why such a significant increase in loading time?  That's weird.

I am going to look into adding a message box to accompany the message bar to alert the user as to the load/initialize disposition.  That shouldn't be too hard.

My concern now is the single request functionality.  Is there any way to have the form allow the multiselect of requests involving items on the IIN form again?

Thanks again my friend!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39275460
I am going to look into adding a message box to accompany the message bar to alert the user as to the load/initialize disposition
It's already doing that with the number and % of tables being loaded.

I'm looking into a way to have it only do the update once for each user.

Is there any way to have the form allow the multiselect of requests involving items on the IIN form again?
Yes but I'd rather not do it now.
0
 

Author Comment

by:Christopher Wright
ID: 39275535
My apologies Marty, I was referring to something similar to Excel 2010 below:

Excel 2010 Loading
However, there would really be no need if you enable it to only update once for each user.  Forgive me for jumping the gun there.  That would be a great enhancement if it did only update once.  Thank you so much!!

I have absolutely no problem at all with holding off with allowing multi-requests.  Thank you again for everything.  I really do appreciate it so much!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39275562
Are you saying that you want an "in your face" type window to tell the user what's going on with the loading rather than just what I have now which shows the progress in the Status Bar?

Even if I figure out how to do the loading just once, I can fairly easily provide such a window if that's what you want.
0
 

Author Comment

by:Christopher Wright
ID: 39275589
Yes sir. I want that type because the users would not pay attention to the message bar or possibly would not see it at all; so if they had a long load time, they'd get impatient and close out the program and send a help email or pick up the phone and call.  Trust me, I have some very needy users, as I am sure you have had to deal with the same thing in your experience.  Thanks again man!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39275658
Okay, will do.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39276638
Okay I have a version that only updates the tables once. Let me know when you find any new problems and/or when you feel that you aren't likely to find any more, or you find a problem serious enough that you don't want to test any more.

Also you've never mentioned seeing the Welcome msgbox. I assume it appeared once and if you want to see it again go into the database and in the UsersOfHelpDesk table delete the row where your username appears.
0
 

Author Comment

by:Christopher Wright
ID: 39278861
My apologies.  I did see the msgbox which welcomes the users and prompts them to reference the Help file.  I modified the database to see this Welcome msgbox, this is what I saw:

Welcome
However, when I clicked 'Open Help Later,' the window below popped up:

DataLink
I tried to exit out but it would not close until I hit the Esc button.  Upon doing this, I received the error msg below:

Error
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39278878
All that button does is close the form so I'm guessing that whatever you did when you say you "modified the database to see this Welcome msgbox" is the culprit. What was it that you did?
0
 

Author Comment

by:Christopher Wright
ID: 39278903
I deleted my name from the 'UsersofHelpDesk' Table.

Table
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39279014
That's was the correct thing to do. Did you leave the database open when you were done? If so that might have caused it otherwise I'm mystified since I do it all the time in my testing.
0
 

Author Comment

by:Christopher Wright
ID: 39279171
No sir.  I made sure to have it closed before I opened the Help Desk
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39279249
Please try it again. If it fails go into the Workbook_Open() event and put a breakpoint on the line below the ' See if this user is in the database comment and press F5. When the code gets there press the F8 key once to execute the line and then again. At that point if the next line that's going to execute isn't frmWelcome.Show then drag the little execution arrow to that line. In either case keep pressing F8 until the error occurs or the sub ends. Let me know which line it errors at if it does.
0
 

Author Comment

by:Christopher Wright
ID: 39279358
I figured it out.  I changed the Help address to match where it is located on my server instead of yours.  I haven't had the issue since.

Changed:
oWord.documents.Open "C:\Safari Downloads\NIUF\Documents\help.docx"

Open in new window

To:
oWord.documents.Open "I:\Chris Wright\Parts Help Desk\Help.doc"

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39279378
Okay thanks. In your original post about the problem you said

However, when I clicked 'Open Help Later,'

and I assume now that you meant to say

However, when I clicked 'Open Help Now,'
0
 

Author Comment

by:Christopher Wright
ID: 39279424
Yes sir.  My apologies about that.  :(
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39282664
What do you think of this for a way to do IIN requests? In our discussion about the misunderstanding we had about multiple IIN requests I said that after you said that this release was error free that I would put back the ability for the user to multi-select multiple request types and have them all show up in one report. I can still do that but what would you think of the idea of there being only 3 request types: 'Create Kits and Kit BOMs', 'Create New Inventory Items' and 'Update Inventory Items', and if the latter was selected the user would see a sheet like this
Straw man
where they would check one or more of the checkboxes indicating the type(s) of updating they wanted to do?  In the example above they checked Expire and Berry so all the fields necessary for those two types of request are shown and all would be one one emailed report. Unchecking the Berry checkbox after it was checked would make the Berry Compliant column disappear.

I ca do either the multi-select method we talked about or this checkbox method with about the same effort on my part, but I think the latter is better for the user since it's easier to get to, and once there it's easier to change their mind and add or delete update types on one screen.
0
 

Author Comment

by:Christopher Wright
ID: 39283082
Wow!  Legendary idea!  I like the checkbox A LOT! I do have one question then, could we re-name the request as 'Update Item Card Values' and have the options (Berry Flag, Expired Flag, Country of Origin, etc.) in the text box as with the current requests?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39283092
Sure.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39283132
Just to make sure, is this what you want?

Just a heading change
0
 

Author Comment

by:Christopher Wright
ID: 39285305
Yes sir.  That is exactly what I had in mind.  Sorry for the late reply.
0
 

Author Comment

by:Christopher Wright
ID: 39291392
During testing today, I provided a fresh copy downloaded from Thread ID: 39271568 to my user.  We started with the NIUF and began to create items.  We hit the email button and we received the msgbox below:

KIT BOM
When 'Yes' is clicked, the following Run-Time error occurs:

Run-Time Error
When 'No' is clicked, it continues to send the email with no issues.

I went back and double-checked the file to identify the data on the Kit BOM form.  I found that there was no data whatsoever on the Kit BOM form but it still populated the Kit BOM msgbox.  

Just a heads up, if the 'Cancel' button is clicked, the email is still sent instead of stopping the process
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39291441
Those should be easy to fix. See what else you can find and I'll post an update when you're done testing.
0
 

Author Comment

by:Christopher Wright
ID: 39291451
Roger that.  Working on that now.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39291511
To fix the incorrect "Pending Kits" message, in procedure BOM_LastRow change

BOM_LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

to

BOM_LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39291540
Since there is normally no "cancel" button to click when doing emails without attachments, and also since pressing the Cancel button with attachments correctly stops the process, I'm assuming that your "Just a heads up, if the 'Cancel' button is clicked…" error was just an offshoot of the first error. If it's not then let me know.
0
 

Author Comment

by:Christopher Wright
ID: 39299958
The code snippet in Thread ID 39291511 worked.
0
 

Author Comment

by:Christopher Wright
ID: 39299963
NIUF
So far so good.  Except when the send an email with attachment button is clicked.  When you click the 'Cancel' button, it states 'Cancel Selected' and the deletes all of the data on the form.


IIN Form
 - On the 'Update Cost for Items' Request, Column I 'Price List Value' is showing.  In this request, this is not a valid field.  I know the checkboxes may eliminate this altogether but I wanted to mention it.

 - Also on the 'Update Cost for Items' Request, the item I asked to expire with a Y, had errors generate when I did not have a price noted. This requirement should be only when you are asking to expire N.

- When the send an email with attachment button is clicked.  When you click the 'Cancel' button, it states 'Cancel Selected', however, when you hit the regular email with no attachment button, it still prompts the user to add an attachment.

 - When the 'Minus' button is clicked, nothing happens at all.  Normally the msgbox 'No Errors to display' shows, but it does not.  And after the button has been clicked, any other buttons create the error message below.
Run Time Error
Kit BOM Form
- I am still working on this one myself.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39300663
QUESTION 1
I'm confused when you say
On the 'Update Cost for Items' Request, Column I 'Price List Value' is showing.  In this request, this is not a valid field.

because in a previous comment you said
I'd also like to add one more column with a drop down for the user to select in order to explain where they obtained their price for that particular item.  This column would also [that means to me that it's used in two places] be used for the Update Pricing Selection as well.

Following that I asked this question but you never answered.
I assume that that means that the new column would only appear when 'UPDATE EXPIRE STATUS FOR INVENTORY ITEMS' is selected and when "Update Pricing Selection" is selected, but what is the latter?

and not hearing from you I want ahead and made that assumption, and in my update notes for release 35 I said
7. Added Price_Source (column AU) in Form Codes. Show Price List Value column and new column Price List Source when Expire or Price List requests are selected.
So please clarify for me which request types should show the Price List Value.

QUESTION(s) 2
Also on the 'Update Cost for Items' Request, the item I asked to expire with a Y, had errors generate when I did not have a price noted. This requirement should be only when you are asking to expire N.
Don't you mean "Update Expire Status For Inventory Items"? And when you talk about "price" not being required do you mean "Price List value" or do you mean "Price List Source", or both?
0
 

Author Comment

by:Christopher Wright
ID: 39300992
QUESTION 1
I'm confused when you say
On the 'Update Cost for Items' Request, Column I 'Price List Value' is showing.  In this request, this is not a valid field.


because in a previous comment you said
I'd also like to add one more column with a drop down for the user to select in order to explain where they obtained their price for that particular item.  This column would also [that means to me that it's used in two places] be used for the Update Pricing Selection as well.

Following that I asked this question but you never answered.
I assume that that means that the new column would only appear when 'UPDATE EXPIRE STATUS FOR INVENTORY ITEMS' is selected and when "Update Pricing Selection" is selected, but what is the latter?

and not hearing from you I want ahead and made that assumption, and in my update notes for release 35 I said
7. Added Price_Source (column AU) in Form Codes. Show Price List Value column and new column Price List Source when Expire or Price List requests are selected.

So please clarify for me which request types should show the Price List Value.

 - My apologies for the confusion Marty.  When I requested the quoted question, I meant to have the additonal drop down column ("Price List Source") in the screen shot below added to the IIN sheet. This column would be visible for every request that involved the IIN sheet.  The particular column I am referring to is the column named "Price List Value" which is only needed for the request named, "Update Price List Value for Inventory Items."  When the user selects the request named, "Update Cost for Items," the column named "Price List Value" does not need to be visible.  Basically, the column named "Price List Source" needed to be added to all of the requests involving the IIN form which is essentially all of the requests excepth the NIUF and the Kit BOM.
 
 - I sincerely apologize for not seeing your question.  My intent was to have the new column appear for all of the requests that involved the IIN form.



QUESTION(s) 2
Also on the 'Update Cost for Items' Request, the item I asked to expire with a Y, had errors generate when I did not have a price noted. This requirement should be only when you are asking to expire N.

Don't you mean "Update Expire Status For Inventory Items"? And when you talk about "price" not being required do you mean "Price List value" or do you mean "Price List Source", or both?

 - Yes sir, you are correct, I meant to say 'Update Expire Status for Inventory Items' when I was referring to the expire with a 'Y' or 'N'.

 - Also, I apologize for not specifying.  When I was referring to "price" I mean "Price List Value."  The "Price List Value" column is only required to be seen during the "Update Price List Value for Inventory Itmes."  As I stated above, the "Price List Source" is the new column that was to be shown in all requests involving the IIN form.

Please let me know if I did not make sense.  I overlooked your question and I hate that.  My apologies there as well.  Thank you for the help and support again!!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39300994
I'm running out the door but I think that clears things up.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39301130
I think there's still a problem.
"Price List Value"... is only needed for ... "Update Price List Value for Inventory Items."
I meant to say 'Update Expire Status for Inventory Items' when I was referring to the expire with a 'Y' or 'N'
.
So when the "Update Expire Status For Inventory Items" is shown, there is only "Price List Source" so what does the Y/N Expire choice have to do with price list value???
0
 

Author Comment

by:Christopher Wright
ID: 39301719
My apologies Marty.  In order for an item to be UNexpired (Expired Flag set to 'N'), the user will need to provide some sort of valid price or that request cannot be approved.  Therefore, we wanted to have that column added. I should have explained that, so please forgive me there.

About the Expire function:
Since we are a re-seller, we are only as good as the price of the items we buy from OUR suppliers, so if a supplier's item exists in our database and is not included on the most up to date yearly price sheet from them, then that item is expired.  Now if one of our sales team reaches out to that vendor directly and obtains pricing for pricing for that particular part number, then we will unexpire that part number in order to be used moving forward.  I would say that 90% of the time, Expire 'N' will be requested.

About the 'Price List Value' function.  
Our database has limitations on high high a sales rep can quote/sell particular items to a customer.  This is to avoid breach of contract for agreements we have made with some of our suppliers.  However, we have 'price-lists' that can be chosen which provided higher/lower thresholds since particular price lists can only be used with certain contracts. Therefore, if an item's has a need to be changed, the user can request for that item' price list value to be modified and by how much.

I hope I make sense.  Thank for all of your help my friend!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39302176
So is this what you want?

All IIN requests have 'Price List Source'
'Price List Source' value is always required
'Update Expire Status For Inventory Items' request has 'Price List Value' but is only a required field in 'Expire' is 'N'
'Update Cost For Items' has 'Price List Value' and it is a required field
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39302560
I deleted a comment I made here but I just want to point out the question in post 39302176 so you don't miss it.
0
 

Author Comment

by:Christopher Wright
ID: 39302780
Hello again my friend.  I have addressed your questions below.  Again, thank you so much for the help!!

This is what we need:
All IIN requests have 'Price List Source'
Yes sir, all IIN requests should have the 'Price List Source'

'Price List Source' value is always required
While the 'Price List Source' is always visible, it will not be required for all IIN requests.  For instance, if a user selects to have an item expired, then technically there would be no need to have a price list source.  This is the only request that I can think of that would NOT require the 'Price List Source' to be specified.  To solve this, we could simply add another value to the drop down list that states 'Not Applicable'.  Would that work?

'Update Expire Status For Inventory Items' request has 'Price List Value' but is only a required field IF 'Expire' is 'N'.
Correct. As I stated above, there would essentially be no need to provide a 'Price List Source' if the user requests to have the item expired.

'Update Cost For Items' has 'Price List Value' and it is a required field
Incorrect.  'Update Cost for Items' has 'Price List SOURCE' and should be required.  Currently, the 'Update Cost for Items' has the other column 'Price List VALUE' showing.  This column should NOT be shown during this request. The screenshot below shows the column I am referring to.
Incorrect Column
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39302817
'Price List Source' value is always required
While the 'Price List Source' is always visible, it will not be required for all IIN requests.  For instance, if a user selects to have an item expired, then technically there would be no need to have a price list source.  This is the only request that I can think of that would NOT require the 'Price List Source' to be specified.  To solve this, we could simply add another value to the drop down list that states 'Not Applicable'.  Would that work?
I like that idea and I will add code so that when the user sets Expire to "Y" the Price List source will be automatically set to "N/A" and I will it may not be needed I'll validate that it is "N/A". Otherwise if Expire is "N" I'll validate to make sure it's not "N/A"

'Update Cost For Items' has 'Price List Value' and it is a required field
Incorrect.  'Update Cost for Items' has 'Price List SOURCE' and should be required.  Currently, the 'Update Cost for Items' has the other column 'Price List VALUE' showing.  This column should NOT be shown during this request. The screenshot below shows the column I am referring to.
Got it.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39302951
I thought I had it but still questions:(

With the above changes I now have Price List Value in 3 places: Update Expire Status For Inventory Items, Update Cost For Items and Update Price List Value For Inventory Items. Correct?

In the one place where the Expire column appears (Update Expire Status For Inventory Items) I know that now when Expire is "Y" that Price List Value Value s/b "N/A" but what about Price List Source? Should it also be "N/A"?

This brings up a point for the future and that is that whenever you need a new field added (or possibly even if one is deleted) a succinct list of requirements would be a help. Something like the following

Display name for new field: e.g. Price List Value
Request types where it appears: e.g. Update Expire Status For Inventory Items, Update Cost For Items and Update Price List Value For Inventory Items.
Validation requirements for the new field: When Expire is "Y" this field s/b "N/A", otherwise it should be one of the other valid price list values.

And as a matter of fact if you have time it would help me be 100% sure I have this right if you did the above for Price List Value and Price List Name.
0
 

Author Comment

by:Christopher Wright
ID: 39303095
Roger that. I have provided the info below:

PRICE LIST VALUE

Display Name For New Field:

'Price List Value'
Request types Where It Appears:
Update Price List Value for Inventory Items
Validation Requirements For The New Field:
Value must be completed with the request. Please note: 'PRICE LIST VALUE' is only required for ONE request - 'Update Price List Value for Inventory Items'


PRICE LIST SOURCE

Display Name For New Field:
'Price List Source'
Request types Where It Appears:
Update Expire Status for Inventory Items; Update Berry Status for Inventory Items; Update Category for Inventory Items; Updated Cost for Items; Update Country of Origin for Inventory Items; Update Minimum Selling Price for Inventory Items; Update Price List Value for Inventory Items
Validation Requirements For The New Field:
When Expire is "Y" this field should contain the value "N/A".  When Expire is "N" this field cannot contain "N/A"
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39303109
Thanks.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39303283
Version 38

o Corrected problem where user would be told that there were kits when there were none.
o Removed coloring from detail rows
o Corrected problem where after cancelling an email with attachments, a regular email would ask the user to select attachments.
o A fix I made in version 33 to correct a "block not set" error didn't go nearly far enough. I only fixed the code for the specific instance of the error whereas similar code was in several other places and they all should have been fixed. I fixed them all for this release and I'm pretty sure you'll never see that error again.
o Part of a previous fix was to show a message when the '-' button was clicked and there were no errors to show. I'm not sure that it ever worked, but it does now.
o Changed code so that Price List Source is visible for every request that involves the IIN sheet, changed code so that Price List Value only shows up in Update Price List Value for Inventory Items, and added validations so that Price List Source can only be "N/A" when Expire is "Y"
o Added "N/A" to Price Source table values. (Almost made the mistake of adding it directly to Form Codes rather than by adding it in Access and incrementing the Access file version.
ADS-Parts-Help-Desk-V38.xlsm
TrackRequests.mdb
0
 

Author Comment

by:Christopher Wright
ID: 39303371
I really like the user interface now.  The "in your face" msgbox during load is GREAT.  During testing, right off the bat, I encountered the error message below when attempting to submit the 'Update Expire Status for Inventory Items' request.  I was able to submit successfully with other requests using the IIN Form, the error only occurred during this specific request.

Mail VBA Error
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39303382
I have no idea why that happens. It doesn't happen with me so it must have something to do with how your email is set up. Does it always happen or is it a one-off thing?
Works for me.
0
 

Author Comment

by:Christopher Wright
ID: 39304418
This is a one-off thing.  I've never had this happen before.  Like I said, it only happens with one request.
0
 

Author Comment

by:Christopher Wright
ID: 39304423
This error may have occurred because I had to comment out the script which sends the email versus displaying.

  .Display changed to .Send
0
 

Author Comment

by:Christopher Wright
ID: 39304429
Expire N - N/AErrorAhhh, I think I know what is causing this.  If Outlook is closed and the submit button is clicked when the program is closed, this error populates.

Also, if I have N/A in the Price List Source field and 'N' in the Expired Field, the cell is highlighted as designed.  This works as it should, however, when the 'Minus' button is clicked, it still says that there are no errors.

Please let me know if I need to explain further.  Thanks again!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39304518
I will look into those two problems but Outlook being closed doesn't seem to me to be the reason because it's usually closed when submit is clicked and my code opens it when needed. Did you possibly close Outlook during the Submit process?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39304552
Your email error apparently has to do with your email profile. Google "Cannot create e-mail message because a data file to send and receive messages cannot be found. Check your settings" and you'll find a couple of hits that suggest what you can do to fix it.

Also please let me know when you are satisfied that there aren't any more errors. At that point I can do one of two things. I will be away from the 11th to the 14th and if you are satisfied in the next couple of days I can probably get the checkbox change done before that and I can upload it or I can wait 'till I get back. Let me know.
0
 

Author Comment

by:Christopher Wright
ID: 39307298
Roger that - I testing version 38 now.  I will go ahead and post the new question since I feel that we are close to ironing out the bugs.  Hopefully, we will know this today.  

Also, I did run into an error when attempting to create a Kit and BOM.  I created the kit as normal and had the error msg below pop up when I tried to identify the 'END OF KIT' in order to start building another kit.  Thanks man!!

End of Kit Error Msg
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39307678
The first thing to try is this. Go to Visual Basic and then select the Debug|Compile NewItemUpload menu item. I believe you will find one error. I think that I found one after I released version 38 and I corrected it but don't remember where it was. I think it was a misspelled word. Let me know if you find it and are able to correct it and having done so if you still have the Kit problem.

If you do still have the Kit problem I'll need more detail on how the error occurred.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39307730
Doh! I went back to version 38 and found the error and sure enough it involves adding the end of kit row.

In the procedure GetKitPartLine you'll find this line


    lngKitRow = Cells.Find("*", SearchOrder:=xlByRows, lookinn:=xlValues, SearchDirection:=xlPrevious).Row

Open in new window


change "lookinn" to "LookIn"

 
   lngKitRow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

Open in new window

0
 

Author Comment

by:Christopher Wright
ID: 39308004
Also, my users are getting a login msgbox asking them to login with credentials.  This hasn't happened before.  Is this intentional?  Thanks!

Login Msg
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39308195
No it's not intentional. Can you tell where it's happening? Where more than one person using the database at the same time (that should be OK) or did you have the database open at that time?
0
 

Author Comment

by:Christopher Wright
ID: 39308208
I may have had the database open at the time.  I did not think that would cause an issue.  I will have them test to ascertain that.  I had three users with this issue.  I'll let you know.  Thanks Marty!!!
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39308966
I may have had the database open at the time
Any results one way or the other?
0
 

Author Comment

by:Christopher Wright
ID: 39309192
Sorry Marty.  No errors so far.  I think we may be all set here.  If I encounter anything, I will keep you posted.  I am going to move on to the next question/phase of this one.  Thanks
0
 

Author Closing Comment

by:Christopher Wright
ID: 39309193
Marty really came through for me here.  Thanks
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39309194
As always I'm glad I was able to help.
0
 

Author Comment

by:Christopher Wright
ID: 39309198
A new question has spawned from this one.  Link is below:

New Question
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

17 Experts available now in Live!

Get 1:1 Help Now