Please Help - Userform For Item Additions **Revisited**

[CHRISEWRIGHT20]

Please help anyone. I am trying to have this file work so users can email new items that need to be imported to our database. Can this be fixed where the 'NSN' field does not have to be populated? If so, can it stay in the same row? I thought I had it where the NSN did not have to be completed for a particular item but any items after that did have a value were automatically placed in the first empty cell. Also, if an error occurs, can this be made where the userform does not close but allows the modifications to be made? I have provide a file for reference. Thanks and God bless.

[jaslake]

Hi Chris

What are the "Rules"? The code needs to be written to follow the "Rules". Spell them out, perhaps I can accommodate.

[CHRISEWRIGHT20]

My apologies for being so vague. I tested the “Rules” process for the SINGLE item upload and it appeared to work perfect. The process for the MULTIPLE item upload is where the issue lies. The Rules pertain to the destination columns on the ‘IDC Working Form’ tab. Each CELL per ROW per COLUMN is to be completed by the user form (fmMultiple). Initially, I was told that every field for an item had to have some value before it could be sent via email to the appropriate department. After I tested the form, I was told that every item would NOT have a corresponding NSN value, therefore, that field would have to be left NULL. I attempted to modify the VBA to allow an item to be populated from the user form to the ‘IDC Working Form’ tab with every value completed except the NSN. I found that if I had multiple rows with items that did and did not have an NSN, that the user form populated the first blank cell in the related column on the ‘IDC Working Form’ tab. I need the NSN to stay on the same row as the PART NUMBER it corresponds with.

I hope I have explained it properly. Everything else works on the form except the NSN issue and the fact that if a user hits the EMAIL REQUEST command button and there is an error, it closes the form completely resulting in lost work. So, while fixing the NSN issue, I was also attempting to allow a user to correct any errors that may arise instead of having to start from scratch. So, it’s only two issues but I am certain it may take a lot of work. Let me know. Again, thank you for the help. God bless.

[jaslake]

Hi Chris

What about this (from a PM relating to you're previous Thread)

When it comes to tweaking "our" form, haha, the single request is leaving out the Berry Compliance value and the Template ID value. I am thinking this has to do with the list index? Also, I am trying to combine the "Add Item" and "Email" commandbuttons

Has it been handled?

[CHRISEWRIGHT20]

Yes sir. I tinkered with it until it worked. I'm thinking I did it right. I tested it several times with no errors. Thanks for inquiring about that John. I should have kept you up to speed. My apologies. Thanks for all the help sir.

[jaslake]

Hi Chris

Alright, we've confirmed (via PM) that ALL fields except NSN are required. Based on the current Code, to populate IDC Working Form properly, ALL fields are required to be filled with SOMETHING.

To accommodate this I can think of several approaches that won't take major (as in TOTAL) rewrite. Just thinking this through as I'm typing I'll probably combine two approaches to satisfy current Code needs.

ALL fields are required to be filled with SOMETHING

Thinking out loud, if NSN is left empty the Code will populate the TextBox on the UserForm with 0000000000000. Will this approach exercise your Users?

Upon posting to IDC Working Form and before Emailing, the NSN Field will be cleared (made Blank) if equal to 0000000000000.

Let me know your thoughts RE: this approach.

[jaslake]

Hi Chris

The code in the attached addresses this issue

I need the NSN to stay on the same row as the PART NUMBER it corresponds with.

If the User leaves the NSN Field blank as the User Tabs through the MultiForm, the issue is addressed by filling the NSN Field with "0000000000000". So, you'll need to educate them to not get exercised when they see this happen. If instead of using the Tab key to move through the MultiForm the User elects to Mouse Click around this Field they'll still get an error message about a missing NSN Number.

These "0000000000000" DO NOT carry over to the IDC Working Form.

This issue is also addressed

a user hits the EMAIL REQUEST command button and there is an error, it closes the form completely resulting in lost work

Check the Code, see if you can figure out how this is fixed.

Let me know of issues.

[CHRISEWRIGHT20]

Hi John. I have tested this form and it works great. I did change the NSN to allow NULL values instead of the "0000000000000" just in case anyone did have an issue. The team lead for that department was somewhat skeptical last week so I didn't want to encounter anymore hiccups with her. This has been a long and arduous task. I can just imagine how "over" it you are too. Thank you so much for taking this challenge on with me. God bless.

I looked into the method you used for the "StartWatching" Sub you added. It works great and is really neat. Thank you again John for going way above and beyond for me here. I hope you are blessed beyond measure. I am going to test some more to verify there's nothing out there I haven't proven yet but I', almost certain you hit the nail on the head here. Gosh, they don't make them like you much anymore. You are truly a saint John. Thank you so much again my friend.

[jaslake]

Hi Chris

You're welcome. Now, you can educate me. How did you do this...show me the code

Please Login or Register  to view this content.

PS: just the Code Snippet...not the whole thing.

[CHRISEWRIGHT20]

I never thought I'd have to show you something. I changed the below:

Please Login or Register  to view this content.

I then changed the below:

Please Login or Register  to view this content.

[jaslake]

Hi Chris

Please wrap all posted Code in Code Tags...Forum Rule.

I'm concerned this'll come back to bite you in the butt

Please Login or Register  to view this content.

See if it works....if no issues then Fly...if issues, let me know.

[CHRISEWRIGHT20]

My apologies for not posting in Code Tags. I'm still new at the forum environment. Forgive my ignorance. What are the potential issues you think? I simply "jerry rigged" it to get it to show what I needed but if there is a potential for errors, I will revert to the original. I know I say it all the time but thank you again my friend. Blessings

[jaslake]

Hi Chris

By allowing blank spaces in the NSN Field it's no longer strictly numeric. Perhaps only 1 time in a hundred or a thousand or a million it may cause an issue. Rest assured, it will cause an issue.

[CHRISEWRIGHT20]

Oh, well I always seem to get caught by Murphy's law, therefore I will change it back. Another question unfortunatley though...I need to add a value to the Berry list of options. I had "Y", "N", "P", and of course a NULL cell but I need to add "NA". I attempted this and when testing, the Single user form came back as blank. For the multiple, I see you have hard coded the same array for the KIT list of values as for the Berry. Is this an easy fix? Thanks brother.

[jaslake]

Hi Chris

I think I know what you want here

I need to add a value to the Berry list of options. I had "Y", "N", "P", and of course a NULL cell but I need to add "NA".

I've taken a bit different approach here

I always seem to get caught by Murphy's law, therefore I will change it back

When the NSN Number is "0000000000000" the Text Font is changed to White so the User never sees it. Let me know of issues.

[CHRISEWRIGHT20]

Simply genius! It works! You never cease to amaze me my friend. Wow. Thanks for helping me along the way. Not only did you catch the fish for me, you taught me how to catch it moving forward. You are truly an overall good person and have become a great friend. Thank you so much John. God bless you and I hope your holidays are filled with warmth, cheer, and joy. Thank you!

[jaslake]

Hi Chris
My pleasure...Holiday wishes to you also.