Developing A New Module Concerning Purchase Orders in an Existing Excel Help Desk
This question stems from a long series of questions involving a Custom Help Desk created in Excel. The link is below. The most recent addition to this was an incredible search function which finds and identifies individual item numbers by their respected vendors. In this question, I am attempting to create a similar process, but for identifying purchase orders and its attributes.
I have a database which contains a table with the purchase orders and all attributes for each. This database is also linked to an Oracle database that will update the local table. Each purchase order involves only one vendor. Each purchase order line contains only one specific item. The goal now is to allow the user to search for the purchase order and send requests to have specific attributes updated or have the line cancelled altogether.
Previous Question
Basically, the intent is to copy an existing process that involves items and have it involve purchase orders.
Attributes:
PO Category
- Internal
- External
Cancel PO Line
-Must choose PO Number and identify line number.
This is bare bones in its development phase so I am quite certain there are a lot of gaps in my descriptions of this new module. I am open to suggestions concerning design and functionality. Once again, I sincerely appreciate the help.
PurchaseOrders.mdb
ADS-Parts-Help-Desk.xlsm
I have a database which contains a table with the purchase orders and all attributes for each. This database is also linked to an Oracle database that will update the local table. Each purchase order involves only one vendor. Each purchase order line contains only one specific item. The goal now is to allow the user to search for the purchase order and send requests to have specific attributes updated or have the line cancelled altogether.
Previous Question
Basically, the intent is to copy an existing process that involves items and have it involve purchase orders.
Attributes:
PO Category
- Internal
- External
Cancel PO Line
-Must choose PO Number and identify line number.
This is bare bones in its development phase so I am quite certain there are a lot of gaps in my descriptions of this new module. I am open to suggestions concerning design and functionality. Once again, I sincerely appreciate the help.
PurchaseOrders.mdb
ADS-Parts-Help-Desk.xlsm
Martin Liss
Working on it. I'm sure I'll have questions:)
When you say "... copy an existing process that involves items…" are you talking about 'CREATE NEW INVENTORY ITEMS'?
Or are you talking about a second Search?
ASKER
No sir, I am referring to Update Item Card Values. Since that request allows the user to select multiple attributes related to an item, that process should accommodate what we are developing in this new module.
I think a second search would be beneficial considering there a few hundred thousand PO lines out there. I am sure I am wrong but I assumed that since we have already established the item search, this should be fairly similar. Thanks for jumping on this so quick buddy!
I think a second search would be beneficial considering there a few hundred thousand PO lines out there. I am sure I am wrong but I assumed that since we have already established the item search, this should be fairly similar. Thanks for jumping on this so quick buddy!
If you want a separate search for POs (like frmSearch) them I think that should be a new question but I can add a PO search similar to the BOM Part Number search to the new sheet.
Actually Chris, could you dummy up a new sheet (without code behind it) that will show me what you want the new sheet to look like?
ASKER
Roger that. I was already working on it. I agree with you 100% concerning splitting the search apart as a second question. That can enable us to focus on one stage at a time. Sorry for throwing so much out there at one time. Again, thank you for all of the help so far buddy!!
ASKER
Here is a basic example that I have put together. I simply took the IIN Form and tailored it to handle Purchase Orders. Let me know what you think.
I will go ahead and start putting the question together for the PO search function. Thanks again for all of your help!
PO-Requests.xlsx
I will go ahead and start putting the question together for the PO search function. Thanks again for all of your help!
PO-Requests.xlsx
Okay got it thanks.
Please let me know
1) Which columns should be displayed when each of the checkboxes are selected, and
2) What validations should be performed on each of the fields in the visible columns and where the validation data comes from. If the validation data is from a new range in Form Codes, please tell me what the valid values are, and
3) Who should get the report?
Please let me know
1) Which columns should be displayed when each of the checkboxes are selected, and
2) What validations should be performed on each of the fields in the visible columns and where the validation data comes from. If the validation data is from a new range in Form Codes, please tell me what the valid values are, and
3) Who should get the report?
ASKER
Hi there buddy. To answer your questions, I have slimmed down the original spreadsheet and have attached. After looking at the sheet, I figured I'd take out some of the unnecessary columns:
1) Which columns should be displayed when each of the checkboxes are selected
2) What validations should be performed on each of the fields in the visible columns and where the validation data comes from. If the validation data is from a new range in Form Codes, please tell me what the valid values are, and
3) Who should get the report?
1) Which columns should be displayed when each of the checkboxes are selected
- I have highlighted the columns to match their appropriate checkbox. Hopefully that helps.
2) What validations should be performed on each of the fields in the visible columns and where the validation data comes from. If the validation data is from a new range in Form Codes, please tell me what the valid values are, and
- The Purchase Order should be in the in the Purchase Order Database provided. The PO number must match the Vendor name specified in the DB table. The Purchase Order should be a list of values populated from this database. If the PO cannot be found, then it does not exist, thus disabling the ability to send a request. Once the Purchase Order has been selected by the user, then the remaining cells pertaining to that PO could be populated in that row. (Similar to the Kit BOM sheet) The only values that are free text are the highlighted columns.
- Customer Need By Date = Free Text Field but only in date format.
- Cancel Flag = 'Y' or 'N'
- Category = 'Dropship' or 'Internal'
- Cancel Flag = 'Y' or 'N'
- Category = 'Dropship' or 'Internal'
3) Who should get the report?
- The report/request should be sent to the same recipients as the others.
PO-Requests.xlsx
Okay now I'm confused. You say you've "highlighted the columns to match their appropriate checkbox" but taking the 'Cancel Purchase Order' and 'Cancel Line' checkboxes, but surely other columns besides the Cancel Flag column should be displayed when they are selected. I assume those other fields include at least the Vendor Name and the PO Number, but I'd like you to be specific and tell me all the columns that should be displayed with each checkbox. Talking specifically about those two checkboxes, is there any difference in what I display? In other words would I show the PO Line in the case of 'Cancel Purchase Order', and if so what's the difference from 'Cancel Line'?
The report/request should be sent to the same recipients as the others.In the process for Inventory Items the managers get cc'd. Should anything like that happen for POs?
ASKER
To answer your question regarding the recipients.
- The managers should be cc'd with the PO requests as well.
The columns to be shown are specified below:
I have attached a table which breaks out what fields should be shown with each request. I hope this will suffice. Thanks again Marty.
BreakDown.xlsx
- The managers should be cc'd with the PO requests as well.
The columns to be shown are specified below:
- The Columns Below should be always be shown.
Vendor Name
PO Number
PO Line
Created Date
Item Number
Item Description
Buyer Name
Unit Price
Market Price
Ordered Qty
PO Number
PO Line
Created Date
Item Number
Item Description
Buyer Name
Unit Price
Market Price
Ordered Qty
I have attached a table which breaks out what fields should be shown with each request. I hope this will suffice. Thanks again Marty.
BreakDown.xlsx
Thanks.
I'm well into the new request type but I need some more things before I can go much further.
1) Most importantly, I need a new ItemMasterDB.mdb that has in addition to Item1, the new tables from Oracle that are needed in the request
2) I know that the user can change the Customer Need By Date, Cancel Flag and Category but I need to know which other fields if any the user should be able to change
3) Considering the Customer Need By Date, are there restrictions on the date range? For example can it be a day in the past? Or today?
4) If they can change the Created Date are there restrictions on the date range?
1) Most importantly, I need a new ItemMasterDB.mdb that has in addition to Item1, the new tables from Oracle that are needed in the request
2) I know that the user can change the Customer Need By Date, Cancel Flag and Category but I need to know which other fields if any the user should be able to change
3) Considering the Customer Need By Date, are there restrictions on the date range? For example can it be a day in the past? Or today?
4) If they can change the Created Date are there restrictions on the date range?
ASKER
1) Most importantly, I need a new ItemMasterDB.mdb that has in addition to Item1, the new tables from Oracle that are needed in the request
2) I know that the user can change the Customer Need By Date, Cancel Flag and Category but I need to know which other fields if any the user should be able to change
3) Considering the Customer Need By Date, are there restrictions on the date range? For example can it be a day in the past? Or today?
4) If they can change the Created Date are there restrictions on the date range?
- In the process of converting my linked Oracle tables to local tables. I will then combine and forward the new ItemMaster.mdb your way.
2) I know that the user can change the Customer Need By Date, Cancel Flag and Category but I need to know which other fields if any the user should be able to change
- Vendor Name – Selected from LOV (dependent on PO Number selection)
- PO Number – Selected from LOV (populated from ItemMaster.mdb)
- PO Line – Selected from LOV (dependent on PO Number selection)
- Customer Need By Date – Free text field. (Short Date format)
- Cancel Flag – Selected from LOV (‘Y’ or ‘N’)
- Category – Selected from LOV (‘INTERNAL’ or ‘EXTERNAL’)
- PO Number – Selected from LOV (populated from ItemMaster.mdb)
- PO Line – Selected from LOV (dependent on PO Number selection)
- Customer Need By Date – Free text field. (Short Date format)
- Cancel Flag – Selected from LOV (‘Y’ or ‘N’)
- Category – Selected from LOV (‘INTERNAL’ or ‘EXTERNAL’)
3) Considering the Customer Need By Date, are there restrictions on the date range? For example can it be a day in the past? Or today?
- There are NO restrictions on the Customer Need By Date. In a perfect world this value should be in the future; however, this date gets missed sometimes.
4) If they can change the Created Date are there restrictions on the date range?
- Users are not allowed to changed the created date. This value should be auto-populated from the .mdb once the PO Number has been selected. The other auto-populated fields are listed below:
Auto-populated after PO Number Selected:
- Vendor Name (based on PO Number)
- Created Date (based on PO Number)
Auto-populated after PO Line Selected:
- Item Number (based on PO Line)
- Item Description (based on PO Line)
- Buyer Name (based on PO Line)
- Unit Price (based on PO Line)
- Market Price (based on PO Line)
- Ordered Qty (based on PO Line)
- Vendor Name (based on PO Number)
- Created Date (based on PO Number)
Auto-populated after PO Line Selected:
- Item Number (based on PO Line)
- Item Description (based on PO Line)
- Buyer Name (based on PO Line)
- Unit Price (based on PO Line)
- Market Price (based on PO Line)
- Ordered Qty (based on PO Line)
There are NO restrictions on the Customer Need By Date. In a perfect world this value should be in the future; however, this date gets missed sometimes.Rather than allowing any date which could be anything like 07-07-1776 or 12-01-2999 I could easily restrict it to a range from today to up to 20 years from now (or however many years in the future is reasonable). However unless you say you want to do that then I will just restrict it to any date in dd-mm-yyyy format.
I thought Category was INTERNAL or DROPSHIP not INTERNAL or EXTERNAL as you say here.
Oh and I assumed that the user would select a Vendor name from the database and then give the user a choice of POs to choose from, but above you seem to be saying that they select a PO number from the database. Which do you want?
ASKER
Regarding your date validation:
Regarding the Category:
Regarding the Vendor Name:
- I think your design would work best AND would be best for reporting purposes. This could keep all of the dates in a standard format. Great idea!
Regarding the Category:
- My apologies. The category should be INTERNAL and DROPSHIP. I put EXTERNAL because it means the same thing to me, but to the user, I felt that DROPSHIP would be a better term for them to understand. Sorry about that.
Regarding the Vendor Name:
- That is a great idea. I like your idea. The user selects a Vendor, then chooses that vendors respective PO's. Well played sir!!
Okay, regarding the date range validation, what years should I allow. Since I assumed you'd see the wisdom of my idea:) a went ahead and coded a date range validation. It says that any year greater than 1979 and less that 2051 (1980 to 2050) are valid but that can easily be narrowed or expanded. Let me know the valid range you want.
ASKER
That date range should suffice. I thing 1979-2051 is an acceptable date range. I'm certain that we will not have any PO's being created before 1979 or after 2051. Thanks for that validation idea again!
make the date range validation relative to the current date
(-33 years to +37 years in your example)
Note: DateDiff() is a crude date difference function, but it might suffice for your purposes.
(-33 years to +37 years in your example)
Note: DateDiff() is a crude date difference function, but it might suffice for your purposes.
Thanks but it's all taken care of already.
I've been working with the OraclePOTable and I've realized that I really don't know how you want to handle the POs and there could be a lot of complex programming involved so please think carefully about the following and let me know how you want to proceed. You also may want to read this all the way through once before answering any of the question because they are interrelated. Please let me know if anything below isn't clear.
In the Kit-BOM when the user right-clicks on a component part (other than the kit part) and clicks on "SELECT PART NUMBER" they are shown a form that contains all the part numbers for the vendor and they choose one and all the needed information from that ONE record in Item1 is transferred to the line on the sheet where they made the selection. I thought that I could simply copy and modify that process for POs but the PO situation is much more complex.
With POs I need to deal with multiple records (multiple line items) for each PO and also whether or not the user has selected the 'Cancel Line' checkbox (which after this I'll refer to as "the checkbox").There are also things I need to know about the PO process, so
Question 1: When I generate the report do I just include the lines that the user has changed in some way or do I need to always include all the line items for the PO? (See Question 4)
Question 2: If I don't always send all the lines and the user changes the Cancel Flag for line 1, how will the report recipient know if the user is canceling the whole PO or just line 1? (I guess I could solve that by leaving the line item number blank if the whole PO was being cancelled - would that work?)
Question 3: Do I show the user a form that contains a list like this (which I'll refer to as the "PO list") for the chosen vendor name
PO11111 Description1
PO22222 Another desc
PO33333 blah blah
or do I show the following (which I'll refer to as the "LI list") where all the line items for each of that vendor's POs are shown?
PO11111 Description1 LI1
PO11111 Description1 LI2
PO22222 Another desc LI1
PO22222 Another desc LI2
PO22222 Another desc Li3
PO22222 Another desc Li4
PO33333 blah blah LI1
In the case of the PO list I assume that after the user makes his selection that I would transfer all the line items for that PO to the Update Purchase Order sheet. This will be tricky since the user may have chosen a vendor name on the sheet that has other vendors below it and I'll have to shift the following rows down to fit the line items in. I think I'll also need a process whereby if the checkbox is not checked I'll have to hide all the line items except the first one and unhide them if the checkbox is checked.
Question 4: When the checkbox is checked and I transfer the data to the report I think I'll have to transfer all the line items to the report and if I do, how will the recipient know which ones have changed? If you say no, just transfer the changed rows, that will be very complex to do because the user could change something and then change it back, check the checkbox or uncheck it, delete rows from the sheet, etcetera and I'd have to keep track of the original state.
Question 5: If we go the LI list way I assume you want the user to be able to select several line items - right? If so what happens if the checkbox isn't checked? What if the user wants to select more line items later?
Question 6: In all of this think about the PO Line column and the fact that the user may want to cancel one line item in PO11111 and cancel all of PO22222.
Question 7: I think that we decided that the PO Line column is only needed to be shown when the checkbox is checked but please confirm that it's also needed when the Change Line Category checkbox is checked. Should we always show the PO Line column?
That's enough for now but I'm sure that this discussion will continue:)
In the Kit-BOM when the user right-clicks on a component part (other than the kit part) and clicks on "SELECT PART NUMBER" they are shown a form that contains all the part numbers for the vendor and they choose one and all the needed information from that ONE record in Item1 is transferred to the line on the sheet where they made the selection. I thought that I could simply copy and modify that process for POs but the PO situation is much more complex.
With POs I need to deal with multiple records (multiple line items) for each PO and also whether or not the user has selected the 'Cancel Line' checkbox (which after this I'll refer to as "the checkbox").There are also things I need to know about the PO process, so
Question 1: When I generate the report do I just include the lines that the user has changed in some way or do I need to always include all the line items for the PO? (See Question 4)
Question 2: If I don't always send all the lines and the user changes the Cancel Flag for line 1, how will the report recipient know if the user is canceling the whole PO or just line 1? (I guess I could solve that by leaving the line item number blank if the whole PO was being cancelled - would that work?)
Question 3: Do I show the user a form that contains a list like this (which I'll refer to as the "PO list") for the chosen vendor name
PO11111 Description1
PO22222 Another desc
PO33333 blah blah
or do I show the following (which I'll refer to as the "LI list") where all the line items for each of that vendor's POs are shown?
PO11111 Description1 LI1
PO11111 Description1 LI2
PO22222 Another desc LI1
PO22222 Another desc LI2
PO22222 Another desc Li3
PO22222 Another desc Li4
PO33333 blah blah LI1
In the case of the PO list I assume that after the user makes his selection that I would transfer all the line items for that PO to the Update Purchase Order sheet. This will be tricky since the user may have chosen a vendor name on the sheet that has other vendors below it and I'll have to shift the following rows down to fit the line items in. I think I'll also need a process whereby if the checkbox is not checked I'll have to hide all the line items except the first one and unhide them if the checkbox is checked.
Question 4: When the checkbox is checked and I transfer the data to the report I think I'll have to transfer all the line items to the report and if I do, how will the recipient know which ones have changed? If you say no, just transfer the changed rows, that will be very complex to do because the user could change something and then change it back, check the checkbox or uncheck it, delete rows from the sheet, etcetera and I'd have to keep track of the original state.
Question 5: If we go the LI list way I assume you want the user to be able to select several line items - right? If so what happens if the checkbox isn't checked? What if the user wants to select more line items later?
Question 6: In all of this think about the PO Line column and the fact that the user may want to cancel one line item in PO11111 and cancel all of PO22222.
Question 7: I think that we decided that the PO Line column is only needed to be shown when the checkbox is checked but please confirm that it's also needed when the Change Line Category checkbox is checked. Should we always show the PO Line column?
That's enough for now but I'm sure that this discussion will continue:)
ASKER
Question 1: When I generate the report do I just include the lines that the user has changed in some way or do I need to always include all the line items for the PO? (See Question 4)
Question 2: If I don't always send all the lines and the user changes the Cancel Flag for line 1, how will the report recipient know if the user is canceling the whole PO or just line 1? (I guess I could solve that by leaving the line item number blank if the whole PO was being cancelled - would that work?)
Question 3: Do I show the user a form that contains a list like this (which I'll refer to as the "PO list") for the chosen vendor name
Question 4: When the checkbox is checked and I transfer the data to the report I think I'll have to transfer all the line items to the report and if I do, how will the recipient know which ones have changed? If you say no, just transfer the changed rows, that will be very complex to do because the user could change something and then change it back, check the checkbox or uncheck it, delete rows from the sheet, etcetera and I'd have to keep track of the original state.
Question 5: If we go the LI list way I assume you want the user to be able to select several line items - right? If so what happens if the checkbox isn't checked? What if the user wants to select more line items later?
Question 6: In all of this think about the PO Line column and the fact that the user may want to cancel one line item in PO11111 and cancel all of PO22222.
Question 7: I think that we decided that the PO Line column is only needed to be shown when the checkbox is checked but please confirm that it's also needed when the Change Line Category checkbox is checked. Should we always show the PO Line column?
- If the programming to include only the lines being changed, then let's include all the line items.
- To let the recipient know which lines are being changed, can we simply highlight those rows a differenct color? I don't know if that would be complex or not; however, it does aleviate the need to eliminate line items.
- To let the recipient know which lines are being changed, can we simply highlight those rows a differenct color? I don't know if that would be complex or not; however, it does aleviate the need to eliminate line items.
Question 2: If I don't always send all the lines and the user changes the Cancel Flag for line 1, how will the report recipient know if the user is canceling the whole PO or just line 1? (I guess I could solve that by leaving the line item number blank if the whole PO was being cancelled - would that work?)
- I would rather keep the line item number visible if at all possible, especially since we will be sending all of the lines to the recipient. Would it be possible to simply place a 'Y' in the Cancel Column for each line item number? This way, the recipient will see that all line items are being modified. If the user wants to cancel one line item number but keep the remaining, we could simply have a 'N' in the Cancel column. This would help with Question 6 as well.
- Another possibility is this; if the user selects to cancel the whole PO, could we simply send just the PO number with a note stating that it should be cancelled? This way, no lines need to be sent and the recipient will know that they simply need to cancel the entire PO. The process is on the recipients' end is different when they are cancelling lines versus cancelling an entire PO. To cancel the whole PO, they simply cancel the PO at the header level and all lines within that PO are cancelled along with it.
- Another possibility is this; if the user selects to cancel the whole PO, could we simply send just the PO number with a note stating that it should be cancelled? This way, no lines need to be sent and the recipient will know that they simply need to cancel the entire PO. The process is on the recipients' end is different when they are cancelling lines versus cancelling an entire PO. To cancel the whole PO, they simply cancel the PO at the header level and all lines within that PO are cancelled along with it.
Question 3: Do I show the user a form that contains a list like this (which I'll refer to as the "PO list") for the chosen vendor name
- I prefer the "LI List" format. This would keep the PO's in an orderly fashion and specify the lines that are being modified. (if it is possible to go with my first suggestion in Question 1 pertaining to highlighting only the rows that need to be changed/addressed)
Question 4: When the checkbox is checked and I transfer the data to the report I think I'll have to transfer all the line items to the report and if I do, how will the recipient know which ones have changed? If you say no, just transfer the changed rows, that will be very complex to do because the user could change something and then change it back, check the checkbox or uncheck it, delete rows from the sheet, etcetera and I'd have to keep track of the original state.
- I answered both Question 1 AND 4 together.
- If the programming to include only the lines being changed, then let's include all the line items.
- To let the recipient know which lines are being changed, can we simply highlight those rows a differenct color? I don't know if that would be complex or not; however, it does aleviate the need to eliminate line items.
- If the programming to include only the lines being changed, then let's include all the line items.
- To let the recipient know which lines are being changed, can we simply highlight those rows a differenct color? I don't know if that would be complex or not; however, it does aleviate the need to eliminate line items.
Question 5: If we go the LI list way I assume you want the user to be able to select several line items - right? If so what happens if the checkbox isn't checked? What if the user wants to select more line items later?
- I am not clear what your are asking. Sorry for the confusion. Can you please elaborate a little. My apologies man!
Question 6: In all of this think about the PO Line column and the fact that the user may want to cancel one line item in PO11111 and cancel all of PO22222.
- I think that if we can highlight the rows needing to be addressed, and popualte the Cancel column with a 'Y' for line items needing to be cancelled and 'N' for the rest, then the user would see which rows need to be addressed, and what exactly should be changed. If row one needs to be cancelled in PO11111 but no changed to the other lines and all of PO22222 needs to be cancelled, then PO11111 will have that one line item highlighted to be cancelled. The row would be highlighted and the Cancel Flag set to 'Y' while the other line item rows would NOT be highlighted and the other Cancel Flags set to 'N' - PO22222 will have all line item number rows highlighted and all have Cancel flag set to 'Y'.
Question 7: I think that we decided that the PO Line column is only needed to be shown when the checkbox is checked but please confirm that it's also needed when the Change Line Category checkbox is checked. Should we always show the PO Line column?
- I think the we should always shoe the PO Line column since the complexity of coding otherwise would be much more difficult. If we can go with simply highlighting the rows that need to be updated and have the particular value (CATEGORY, CANCEL FLAG, Etc.) shown to the recipient, then it could work.
Question 1:
Question 2:
Question 3:
Question 5
Is moot if you understand and agree with what I said in question 3 and we can discuss this question more if you don't agree.
Questions 6 and 7I agree with your conclusions.
To let the recipient know which lines are being changed, can we simply highlight those rows a differenct color? I don't know if that would be complex or not; however, it does aleviate the need to eliminate line items.At first I thought "that would be difficult because as I said I'd need to keep track of the original state" but then I had an epiphany. When transfer the rows to the working form I can check the database against what I'm copying and if anything is different I can mark it! Hopefully that won't be too time consuming. As far as marking it goes would you prefer 1) a different row color 2) a different cell color for the cells that have changed, or 3) a new column that indicates that something in the row has changed?
Question 2:
Would it be possible to simply place a 'Y' in the Cancel Column for each line item number?Yes I can do that.
- Another possibility is this;...I much prefer the first option so I'll go with that unless you tell me differently.
Question 3:
- I prefer the "LI List" format. This would keep the PO's in an orderly fashion and specify the lines that are being modified….I'm not sure what "orderly fashion" you mean. In the "PO List" format the user would select the PO and all the line items for that PO would show up in the request sheet and the specification of the ones being modified would result from the user's actions in changing one of the cells.
Question 5
Is moot if you understand and agree with what I said in question 3 and we can discuss this question more if you don't agree.
Questions 6 and 7I agree with your conclusions.
ASKER
Question 1:
Question 3:
Question 5:
Thanks again for all your help friend!!
Let's go with - 1) a different row color
Question 3:
What I meant by orderly fashion is just as you describe. Basically, it meant
all the line items for that PO would show up in the request sheet and the specification of the ones being modified would result from the user's actions in changing one of the cells.
Question 5:
I understand and agree with what you said in Question 3.
Thanks again for all your help friend!!
Here's version 50 (wow!) which is the first cut at the Purchase Order request. Included in this version are
o I created modNotes which includes application documentation including a large section on how to create a new request type.
o Added 'Purchase Order' and 'PO Working Form sheets' and all the code that goes with them
o In previous versions of the workbook, after a cell was made yellow or green and the error was corrected I used to change the fill color to be white which may have been causing the loss of some borders, so I changed it to no fill.
Some things to note when you are testing:
o I change the valid year range for the need by date to be the current year +/- 30
o A Ctrl+3 shortcut was added for the selection of PO Numbers although you can also click a PO Number cell and select "SELECT PO NUMBER"
o I added code to prevent a duplicate PO Number from being added
o I added code to replace a PO even if the PO is in a middle row and the new PO has a different number of rows than the old one
o I assume that the deletion of a line item isn't valid so I added code that catches the deletion
o In addition to changing the color of the changed items in the working form I added a comment that shows what the old value was
o When the user cancels line 1 I added a message that warns the user that all lines will be canceled. Is it needed or should I just go ahead and make the change without asking?
o I don't remember if we agreed to combine the 2 cancel checkboxes. I went ahead and did it but it's easily undone if you want them both put back.
ADS-Parts-Help-Desk-V50.xlsm
o I created modNotes which includes application documentation including a large section on how to create a new request type.
o Added 'Purchase Order' and 'PO Working Form sheets' and all the code that goes with them
o In previous versions of the workbook, after a cell was made yellow or green and the error was corrected I used to change the fill color to be white which may have been causing the loss of some borders, so I changed it to no fill.
Some things to note when you are testing:
o I change the valid year range for the need by date to be the current year +/- 30
o A Ctrl+3 shortcut was added for the selection of PO Numbers although you can also click a PO Number cell and select "SELECT PO NUMBER"
o I added code to prevent a duplicate PO Number from being added
o I added code to replace a PO even if the PO is in a middle row and the new PO has a different number of rows than the old one
o I assume that the deletion of a line item isn't valid so I added code that catches the deletion
o In addition to changing the color of the changed items in the working form I added a comment that shows what the old value was
o When the user cancels line 1 I added a message that warns the user that all lines will be canceled. Is it needed or should I just go ahead and make the change without asking?
o I don't remember if we agreed to combine the 2 cancel checkboxes. I went ahead and did it but it's easily undone if you want them both put back.
ADS-Parts-Help-Desk-V50.xlsm
ASKER
I am testing now. Thanks for your help Marty!!!
I don't know if you've found any problems but the attached corrects a couple I found (I left it as V50). Among several niceties I added is that when the Select PO form shows up you can select a PO by double-clicking it.
BTW, if you haven't already, please read the section in modNotes about adding a new request type and let me know if there's anything there that you don't understand.
ADS-Parts-Help-Desk-V50.xlsm
BTW, if you haven't already, please read the section in modNotes about adding a new request type and let me know if there's anything there that you don't understand.
ADS-Parts-Help-Desk-V50.xlsm
ASKER
I am running into a few errors. I am going to try to explain below.
Error 1:
Error 2:
I have double checked to ensure the OraclePOTable exists and is spelled correctly. I did not have the db open while I attempted to use the Help Desk. Sorry for hitting you with 5 at once. :(
Error 1:
- When I go into the NIUF and click the button to return to the Home screen, I get the error message below:
Error 2:
-When I have an item on the NIUF that is a kit, I get all of the appropriate warning messages; however, after I get the Missing Kits Message Box (which is part of the process) and click yes, I get the error Select Method error message below:
Missing Kit Msg Box
Error 3:
Error 4:
Error 5:
Error Message
Missing Kit Msg Box
Error 3:
While using the search form, I am getting weird results when searching for a specific part number. It only seems to be happening with the Vendor 'Oakley' though. I type in the part number '11-138' (w/o apostrophes) and click search, and it only returns the very first part number for Oakley. I pasted a screen shot below. No what I search pertaining to Oakley, it only returns the results below.
Error 4:
I am not able to replicate this error; however, oftentimes when I attempt to validate/check for errors, I am getting the error message below:
Error 5:
I haven't had much opportunity to get into the new process yet; however, I have had this error message below pretty often when going into the form. Just going in, checking the first check box on the left and validating gives the error message below:
Error Message
I have double checked to ensure the OraclePOTable exists and is spelled correctly. I did not have the db open while I attempted to use the Help Desk. Sorry for hitting you with 5 at once. :(
Error 1: Corrected
Error 2: I assume that after you clicked yes that you neglected to add any kit parts or you had some other error(s) on the BOM.
Error 3: Corrected
Error 4: We've talked about this before and pointed out that it's caused by going into the IDE and that the solution is to type Ctrl+Shift+R. However I've added some code to try to get it to happen less frequently.
Error 5: Do you have a link in ADS.mdb to OraclePOTable? Let me know and I'll post the new version.
Error 2: I assume that after you clicked yes that you neglected to add any kit parts or you had some other error(s) on the BOM.
Error 3: Corrected
Error 4: We've talked about this before and pointed out that it's caused by going into the IDE and that the solution is to type Ctrl+Shift+R. However I've added some code to try to get it to happen less frequently.
Error 5: Do you have a link in ADS.mdb to OraclePOTable? Let me know and I'll post the new version.
ASKER
Wow. Thanks for the quick turnaround! Regarding Error 2, I had item in the BOM after I clicked yes. I corrected the errors in yellow and received the error.
Concerning Error 4, Thank you for adding the extra code for preventing disconnect.
Concerning Error 5, I do have a link from ADS.mdb to OraclePOTable.
Thanks again for your support man!!
Concerning Error 4, Thank you for adding the extra code for preventing disconnect.
Concerning Error 5, I do have a link from ADS.mdb to OraclePOTable.
Thanks again for your support man!!
Error 5: It works perfectly for me so do me a favor. Go into ADS.MDB, delete the link and do File|Get External Data|Link Tables… and make sure you select OraclePOTable. Then try the workbook and let me know what happens.
ASKER
I deleted the link and added the link again. I am not getting the error any longer. It looks good so far. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just wanted to give an update. Currently, my Senior buyer is testing this and collaborating with his team for feedback concerning potential mods. Thanks again for all of your help.
In the mean time, I am going to post the question pertaining to the final module to the Help Desk. This pertains to Returns (Returning of Items by Customers). I have already built the form, I just need validation and code behind it. The link to the new question is below:
Final Helpdesk Module
In the mean time, I am going to post the question pertaining to the final module to the Help Desk. This pertains to Returns (Returning of Items by Customers). I have already built the form, I just need validation and code behind it. The link to the new question is below:
Final Helpdesk Module
Should I assume that there are no problems or changes for this?
Any progress on testing this yet?
ASKER
Yes sir. Actually I have. I was focusing on the Returns Module issues before posting what was found in testing this. How would you like for me to proceed?
If there are problems please list them.
ASKER
Roger that. Will do
Thanks
-M
-M
ASKER
Thank you for all your help buddy! I sincerely appreciate it.
At long last here's the RA module.
In addition to adding the 'Return Authorization' sheet and all the code that goes with it and some minor bug fixes and tweaks I changed your email address within the code and I added a macro named ChangeInsideRep through which you can test as other people.
ADS-Parts-Help-Desk-V52.xlsm
In addition to adding the 'Return Authorization' sheet and all the code that goes with it and some minor bug fixes and tweaks I changed your email address within the code and I added a macro named ChangeInsideRep through which you can test as other people.
ADS-Parts-Help-Desk-V52.xlsm
When you test the module you'll probably find that any change you make to one of the multiple 'Vendor' lines will be slow. The reason for that is that the code needs to get the VENDOR_COST for a particular ITEM from the OrderedItems table every time. I could add some code that would store the cost for an item and only do the lookup if the item changes, but even then the first time will be slow.
The reason it's slow is that there are over a million records and even though ITEM is an index, with that many records it takes a while. I believe it would be faster if ITEM were the Primary Key but as it stands now in many cases the same ITEM occurs more than once and so it can't be the Primary Key. Would it be possible for you to recreate the table so that it contains only unique ITEMs and make that field the Primary Key? BTW besides ITEM all I currently get from that table is VENDOR_COST so those are the only fields that need to be there.
The reason it's slow is that there are over a million records and even though ITEM is an index, with that many records it takes a while. I believe it would be faster if ITEM were the Primary Key but as it stands now in many cases the same ITEM occurs more than once and so it can't be the Primary Key. Would it be possible for you to recreate the table so that it contains only unique ITEMs and make that field the Primary Key? BTW besides ITEM all I currently get from that table is VENDOR_COST so those are the only fields that need to be there.
ASKER
Yes sir, I can do that no problem! I have attached the same DB you provided with the two new tables. The new table is named 'VendorCostForItems'. This will reside in the SalesOrderDB.
NewADSTables.mdb
NewADSTables.mdb
Thanks, using the new table the lookup is almost instantaneous.
ASKER
Perfect. Thanks for all the help man. I'm here at work, 9:30 on a Friday night. Sounds exciting huh? Haha. Hope your weekend goes great.
Here it is 6:48 on a Friday night and I'm working on your project:)
I just made another change that greatly speeds up the code involved in changing the sales order number which previously was pretty slow. Have you had the chance to try the new module?
I just made another change that greatly speeds up the code involved in changing the sales order number which previously was pretty slow. Have you had the chance to try the new module?
ASKER
I'm doing limited testing this weekend. Since I am working from home, I have a tough time maintaining a solid connection via VPN. At this point, I think we both have to own it. It's OUR project now my friend. Haha