Modifying a Homemade Help Desk

I'm on it.

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?

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.

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.  

What about my question in Post ID: 39256951?

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.

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?

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

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.

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!

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".

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!

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:


I click OK and then this error message pops up.

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

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

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

The file did not fail.  Do you want me to change something to force it to?

Oh I forgot. Find Public Const FILE_VERSION = 34 and change 34 to anything else. Also clear your Immediate Window before opening it.

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

Select allOpen in new window

Download this file, clear your Immediate window and try it again please. I took care of changing FILE_VERSION.
Test-Open.xlsm

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

Select allOpen in new window

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

Roger that. I will ensure that it will be named that on the server and for the user's file.

So did you test again with the file in post 39261163?

Once I am able to get back to my pc, I will retest.

I'm building the Access "Form Codes" source tables and I noticed several things in the Form Codes sheet of the Help Desk:

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

I retested the file to address Thread ID: 39261498.  I encountered the error message below.  

To address Thread ID: 39263190, please go ahead and delete Column S since it is not used or needed anymore.  Thanks Marty

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?

Yes sir.  I'm opening an old file in the same folder as the current Help Desk file.

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.

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.

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

Okay, that's actually good.

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

That's a mistake which I'll correct.

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:
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:
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

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:



Resulting 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:

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.

I figured out how to restrict the selection to just one Request Type and as I expected the icons now behave.

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!!

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:)

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.

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!!

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.

Please update your Access database to reflect version 36.
ADS-Parts-Help-Desk-V36.xlsm

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

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

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....

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?

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.

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:(

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?

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.

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.

Roger that. I'll continue with our testing.  Thanks Marty!!

my users with Excel 2007 are seeing this message box when they open the program

Do you get this message?

No sir, I do not.

Do they have the same permissions on the server that you do?

Yes sir, for this particular server they have the same.

I understand that they use Excel 2007, what about you?

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.

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.

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.

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

Success!! It works.  It took approximately 45 seconds to initialize all 13 tables and no message boxes were displayed.  Thanks Marty!!

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.

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!!

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.

My apologies Marty, I was referring to something similar to Excel 2010 below:


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!!

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.

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!

Okay, will do.

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.

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:


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


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:

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?

I deleted my name from the 'UsersofHelpDesk' Table.

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.

No sir.  I made sure to have it closed before I opened the Help Desk

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.

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"

Select allOpen in new window

To:

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

Select allOpen in new window

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,'

Yes sir.  My apologies about that.  :(

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

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.

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?

Sure.

Just to make sure, is this what you want?

Yes sir.  That is exactly what I had in mind.  Sorry for the late reply.

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:


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


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

Those should be easy to fix. See what else you can find and I'll post an update when you're done testing.

Roger that.  Working on that now.

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

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.

The code snippet in Thread ID 39291511 worked.

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.

Kit BOM Form
- I am still working on this one myself.

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?

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!!!

I'm running out the door but I think that clears things up.

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???

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!

So is this what you want?

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.

Hello again my friend.  I have addressed your questions below.  Again, thank you so much for the help!!

This is what we need:

'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.

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.

Roger that. I have provided the info below:

PRICE LIST VALUE

PRICE LIST SOURCE

Thanks.

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

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.

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?

This is a one-off thing.  I've never had this happen before.  Like I said, it only happens with one request.

This error may have occurred because I had to comment out the script which sends the email versus displaying.

  .Display changed to .Send

Ahhh, 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!

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?

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.

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!!

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.

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

Select allOpen in new window

change "lookinn" to "LookIn"

 

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

Select allOpen in new window

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

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?

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!!!

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

Marty really came through for me here.  Thanks

As always I'm glad I was able to help.

A new question has spawned from this one.  Link is below:

New Question