Christopher Wright
asked on
Allow Requests to be Returned to Sender if Additional Action Required Involving an Excel Help Desk Project
This is in continuation of the question at the link below:
https://www.experts-exchange.com/questions/28272871/Developing-A-New-Module-Concerning-Customer-Returns-in-an-Existing-Excel-Help-Desk.html
I am taking an existing Help Desk built from scratch using MS Access as my database and MS Excel as the front end UI for the users. The intent is to provide users the capability to submit requests to the appropriate recipient based on table values. The most recent addition is a Module pertaining to requests to submit Customer Returns to the Returns Coordinator. This has been developed effectively; however, now I am trying to develop the functionality to have the Returns Coordinator return the request if it is deemed necessary.
Returns-Process.pdf
https://www.experts-exchange.com/questions/28272871/Developing-A-New-Module-Concerning-Customer-Returns-in-an-Existing-Excel-Help-Desk.html
I am taking an existing Help Desk built from scratch using MS Access as my database and MS Excel as the front end UI for the users. The intent is to provide users the capability to submit requests to the appropriate recipient based on table values. The most recent addition is a Module pertaining to requests to submit Customer Returns to the Returns Coordinator. This has been developed effectively; however, now I am trying to develop the functionality to have the Returns Coordinator return the request if it is deemed necessary.
Returns-Process.pdf
Working on it.
Currently the 'Restocking Fees' cell in the write off section is free text. Should it instead be locked and calculated from the total of the 'Restock Fee' in the upper section?
ASKER
Yes sir. That is correct. It should no longer be a free text field.
Here's version 53 which includes
o A couple of speed improvements
o A number of changes due to requirements change whereby most of the RA writeoff data is now copied from the upper section rather than being free text.
o Corrected a bug where the report might be sent to team leader when there were no write offs
ADS-Parts-Help-Desk-V53.xlsm
o A couple of speed improvements
o A number of changes due to requirements change whereby most of the RA writeoff data is now copied from the upper section rather than being free text.
o Corrected a bug where the report might be sent to team leader when there were no write offs
ADS-Parts-Help-Desk-V53.xlsm
ASKER
I'm testing this now with my RC.
ASKER
Open up your ADS.mdb database. Do you see the SalesRep&Vertical table. If so and it's a link, can you open it?
ASKER
Yes sir. It is a linked table and it opens no problem.
You must be pointing to the wrong ads.mdb.
If you are at work the database it's looking at is the one on your server.
ASKER
That is what I am referencing in terms of my database. I am using the files on the network
I'm 100% sure there is something wrong on your end because it works here.
Is the database being used by you or anyone else? In other words does someone besides the help desk workbook have it open?
Please double check that the database in which you found the SalesRep&Vertical table is the database that's on the server and not some local copy.
Is the database being used by you or anyone else? In other words does someone besides the help desk workbook have it open?
Please double check that the database in which you found the SalesRep&Vertical table is the database that's on the server and not some local copy.
ASKER
I found the issue. For some reason, there are spaces in front of the values of SALES_REP_NAMES. This explains why you had to search with a LIKE operand. Also, you were searching SALES_REP_NAME with ENVIRON("USERNAME") which only produces the first letter of first name and last name (ie: CWRIGHT). I updated it to show APPLICATION.USERNAME to reflect the format in the SALES_REP _NAME column. I have it working with the attached code snippet. I hope you are okay with the slight modifications. Thanks buddy!
.Range(INSIDE_REP) = " " & Application.UserName
'.Range(INSIDE_REP) = UCase(Environ("USERNAME"))
If Environ("USERNAME") = "Martin Liss" Or UCase(Environ("USERNAME")) = "CWRIGHT" Then
.Range(INSIDE_REP) = " " & "HEATH SCHULMAN"
End If
'******* 53f Start *******
' If Vertical was invalid clear the error
If .Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber).Interior.Color = vbYellow Then
RemoveFillColor .Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber)
On Error Resume Next
.Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber).Comment.Delete
On Error GoTo 0
End If
'******* 53f End *********
.Cells(ExplanationHdrRow + 5, RA.CostOfItems_Date).value = Now()
Set rs = New ADODB.Recordset
' For some reason a "Like" search is required
rs.Open "Select Vertical from [SalesRep&Vertical] Where SALES_REP_NAME = '" & .Range(INSIDE_REP).value & "'", gConn
My Environ("USERNAME")) is "Martin Liss" and I believe that what gets shown there is set up when you install the operating system.
I don't have any problem with that change and I'm including it in my code now.
What was the cause of the SalesRep&Vertical table not being found?
I don't have any problem with that change and I'm including it in my code now.
What was the cause of the SalesRep&Vertical table not being found?
ASKER
I'm thinking it is the space " " in front of the SALES_REP_NAME (ie: ' CHRIS WRIGHT') Weird how that happened..?
That is very strange. In that same code try doing it this way. Change line #1 as posted above by you to
I added a space right before the first single quote.
.Range(INSIDE_REP) = Application.UserName
and change line 21 tors.Open "Select Vertical from [SalesRep&Vertical] Where SALES_REP_NAME = ' " & .Range(INSIDE_REP).value & "'", gConn
I added a space right before the first single quote.
To test this you'll need to do one of the following
In the Immediate Window set gbInitialized to False, or
Set a breakpoint at the If gbInitialized Then line and when the code gets there drag the yellow highlight to the With Worksheets(RA_SHEET) line and press F5, or
Close and reopen the workbook.
Can you post the the code from that event please?
ASKER
I am only getting this error if the 'Associated Write Offs' checkbox is not checked.
ASKER
In response to Thread ID 39679333, the event code is below:
' Gray cells on this sheet are RGB(245, 245, 245)
Private Sub Worksheet_Activate()
'******* 52b Start *******
Dim rs As Recordset
On Error GoTo ErrorRoutine
Worksheets(RA_SHEET).Shapes("chkDone").Visible = False
If gbInitialized Then
' Exit Sub
Else
gbInitialized = True
End If
With Worksheets(RA_SHEET)
' This line is needed so that the next line doesn't prompt for the password
.Unprotect " "
'******* 53b Start *******
' .Protect Password:=" ", Userinterfaceonly:=True
.Protect Password:=" ", Userinterfaceonly:=True, DrawingObjects:=False
'******* 53b End *********
.Range(INSIDE_REP) = Application.UserName
'.Range(INSIDE_REP) = UCase(Environ("USERNAME"))
If Environ("USERNAME") = "Martin Liss" Then 'Or UCase(Environ("USERNAME")) = "CWRIGHT" Then
.Range(INSIDE_REP) = " " & "HEATH SCHULMAN"
End If
'******* 53f Start *******
' If Vertical was invalid clear the error
If .Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber).Interior.Color = vbYellow Then
RemoveFillColor .Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber)
On Error Resume Next
.Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber).Comment.Delete
On Error GoTo 0
End If
'******* 53f End *********
.Cells(ExplanationHdrRow + 5, RA.CostOfItems_Date).value = Now()
Set rs = New ADODB.Recordset
' For some reason a "Like" search is required
rs.Open "Select Vertical from [SalesRep&Vertical] Where SALES_REP_NAME = ' " & .Range(INSIDE_REP).value & "'", gConn
If Not rs.EOF Then
.Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber).value = rs!Vertical
Else
'******* 53f Start *******
' MsgBox "Inside Sales Rep " & Environ("USERNAME") & " not found in database", vbOKOnly + vbInformation, "Vertical Value"
' .Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber).value = "(Unknown)"
' This line allows the form to be shown before the message appears
.Range("I6").Select
MsgBox "Inside Sales Rep '" & .Range(INSIDE_REP) & "' not found in database. Please contact support.", vbOKOnly + vbInformation, "Vertical Value"
.Cells(CustomerRow, RA.ItemNumber_Vertical_PartNumber).value = UNKNOWN
'******* 53f End *********
End If
rs.Close
Set rs = Nothing
End With
On Error GoTo 0
Exit Sub
ErrorRoutine:
Select Case Err.Number
Case 3709
' Connection is closed
cnOpen
Resume
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Worksheet_Activate of VBA Document Sheet10"
Application.EnableEvents = True
End Select
Application.Cursor = xlDefault
'******* 52b End *********
End Sub
To correct that error make this simple change.
In the CustomerRow function, change xlValues to xlFormulas.
In the CustomerRow function, change xlValues to xlFormulas.
ASKER
Awesome. That worked perfectly.
I do have a couple of questions.
If I link the SalesOrderHeaders to ADS,mdb, the workbook freezes for about 30 seconds when it is opened. The banner we have which scrolls right to left simply pauses and then moves after 30 seconds or so. If I break the link to the SalesOrderHeaders table, then it doesn't happen.
Another question is concerning the Sales Order Number on the top of the RA form. Is it free text or should it be a dropdown showing only the orders pertaining to the Inside Rep?
Sorry for the issues man. Thanks again for the help and good luck with your Thanksgiving prep time!
I do have a couple of questions.
If I link the SalesOrderHeaders to ADS,mdb, the workbook freezes for about 30 seconds when it is opened. The banner we have which scrolls right to left simply pauses and then moves after 30 seconds or so. If I break the link to the SalesOrderHeaders table, then it doesn't happen.
Another question is concerning the Sales Order Number on the top of the RA form. Is it free text or should it be a dropdown showing only the orders pertaining to the Inside Rep?
Sorry for the issues man. Thanks again for the help and good luck with your Thanksgiving prep time!
If I link the SalesOrderHeaders to ADS,mdb, the workbook freezes for about 30 seconds when it is opened.Do you have these indexes set up in the table?
If you do then I don't believe there's much that can be done. what I'm doing is transferring the data to the Form Codes sheet for easier, quicker, access when it's needed.
concerning the Sales Order Number on the top of the RA form. Is it free text or should it be a dropdown showing only the orders pertaining to the Inside Rep?Well the original spec says "sales Order No. = Select from a drop down list of existing Sales Order Numbers in database table" and I completely missed that so currently it's free text. However if you will describe what I should do using table and field names I'll add that in version 54.
This morning I made cranberry sauce and something called "Strange Chocolate Pie":)
BTW you probably should change your production ADS.mdb so that the tables are imported rather than linked.
ASKER
Strange chocolate pie? Sounds interesting.
The SO number can be found based on the SalesOrders&Vendors Table.
The SO number can be found based on the SalesOrders&Vendors Table.
rs.Open "Select ORDER_NUMBER from [SalesOrders&Vendors] Where INSIDE_SALES_REP = ' " & .Range(INSIDE_REP).value & "'", gConn
It's really good. You can ignore the hand mix and convection oven comments.
Strange-Chocolate-Pie.jpeg
Strange-Chocolate-Pie.jpeg
I'm using Heath Schulman as my alter-ego (Inside Sales Rep name) and when I do that and I get the list of orders associated with that name I get about 4400 orders even after including the "distinct" parameter in the query. The number of them isn't a problem for me but what may be a problem on your end is that I get my "Sales Order number has no Items Numbers" error message for many (most?) of them. The error is generated when I try to "Select ITEM from SalesOrderHeaders Where ORDER_NUMBER ='" & Range(SO_NUMBER).value". The last part of that is the selected Sales Order number. Am I doing something wrong?
ASKER
No, that's not wrong. I suppose we should use another table to select the record from. A table that will show the item number as well as the Order Number. Would that slow it down?
I assume that the user will know which sales order he wants so it's probably not likely he will pick one of the problem one so maybe we don't need to do anything. Some possibilities if we do want to do something are in no particular order:
Change the SalesOrders&Vendors table so that it only contains sales orders that have ITEMs. (BTW why do some not have ITEMs?)
Add a flag of some sort to the SalesOrders&Vendors table that indicates the no-ITEMs situation.
Let me look into a more complicated query (perhaps you already know how to do this) where instead of just doing the query you posted in ID: 39681789, we could do some sort of Join(?) with the SalesOrderHeaders table to restrict the selection to orders with ITEMs.
If we did your suggestion wouldn't that be a very large table?
Please don't create/change any tables until we can discuss it more.
Change the SalesOrders&Vendors table so that it only contains sales orders that have ITEMs. (BTW why do some not have ITEMs?)
Add a flag of some sort to the SalesOrders&Vendors table that indicates the no-ITEMs situation.
Let me look into a more complicated query (perhaps you already know how to do this) where instead of just doing the query you posted in ID: 39681789, we could do some sort of Join(?) with the SalesOrderHeaders table to restrict the selection to orders with ITEMs.
If we did your suggestion wouldn't that be a very large table?
Please don't create/change any tables until we can discuss it more.
D...! I just came back to say I hadn't heard anything from you and I see that I never finished my last post which said I solved the problem we've been discussing by implementing the following Inner Join which gets all the sales orders from the SalesOrders&Vendors table that are associated with the Inside Rep, but only if the sales order is also in the SalesOrderHeaders table.
Where do we go from here? Do you want the new version or can you continue testing with what you have, or perhaps you're done testing.
rs.Open "Select distinct [SalesOrders&Vendors].ORDER_NUMBER from [SalesOrders&Vendors] " _
& "Inner Join SalesOrderHeaders on SalesOrderHeaders.ORDER_NUMBER = [SalesOrders&Vendors].ORDER_NUMBER " _
& "Where [SalesOrders&Vendors].INSIDE_SALES_REP = '" & strInsideRep & "'", gConn
Where do we go from here? Do you want the new version or can you continue testing with what you have, or perhaps you're done testing.
ASKER
Sorry for the "radio silence" friend. A lot of moving and shaking over the holiday weekend that kept me off the radar. I apologize for that. I have addressed you questions below:
Thread ID: 39682423
Thread ID: 39687623
Thread ID: 39682423
I assume that the user will know which sales order he wants so it's probably not likely he will pick one of the problem one so maybe we don't need to do anything. Some possibilities if we do want to do something are in no particular order:
- Agreed, they will already know which Order that has a return against it.
Change the SalesOrders&Vendors table so that it only contains sales orders that have ITEMs. (BTW why do some not have ITEMs?)
- I am not certain why some orders do not have items. One possibility to explain these orders could be that those orders with no items were used for accounting purposes only. I am not completely sure though; however, those orders need to be removed because an order cannot have a return against it if no items exist.
Add a flag of some sort to the SalesOrders&Vendors table that indicates the no-ITEMs situation.
- By removing the orders with no items, we would no longer have this issue.
Let me look into a more complicated query (perhaps you already know how to do this) where instead of just doing the query you posted in ID: 39681789, we could do some sort of Join(?) with the SalesOrderHeaders table to restrict the selection to orders with ITEMs.
- Would this query be necessary if the orders without items are removed?
If we did your suggestion wouldn't that be a very large table?
- Actually yes. The ordered items table already shows items ordered with the related order number. This table contains 1,070,410 rows!!!
Thread ID: 39687623
Where do we go from here? Do you want the new version or can you continue testing with what you have, or perhaps you're done testing.
- I am still testing; however, I would like to use the newest version if at all possible? Thanks for all of your help so far Marty!!!
Thanks for those replies to the sales order problem but I already resolved it using the Inner Join I mentioned in post 39687623.
Here's version 54 which includes:
o Changed what's used as the source of the Inside Reps name from Environ("USERNAME") to Application.UserName to better match what's in the SalesRep&Vendor table. (Your suggestion)
o Corrected the bug you mentioned in post ID 39679317
o Changed the Sales Order cell from free text to a dropdown that shows all the sales orders for the Inside Rep but only if the sales order is also in the SalesOrderHeaders table
o Corrected a bug where write off totals were not being updated when a vendor row was deleted
o Eliminate duplicates from the Vendor and Item Number dropdowns
o Removed a part number lookup that was being done at startup since it was made unnecessary when we changed to copying the writeoff totals from the Vendor section. This has the effect of speeding up the startup.
Note that while in version 53 I forgot to update the workbooks version I did remember to do it this time so you'll have to update your Access FileVersion Help Desk version to 54.
ADS-Parts-Help-Desk-V54.xlsm
Here's version 54 which includes:
o Changed what's used as the source of the Inside Reps name from Environ("USERNAME") to Application.UserName to better match what's in the SalesRep&Vendor table. (Your suggestion)
o Corrected the bug you mentioned in post ID 39679317
o Changed the Sales Order cell from free text to a dropdown that shows all the sales orders for the Inside Rep but only if the sales order is also in the SalesOrderHeaders table
o Corrected a bug where write off totals were not being updated when a vendor row was deleted
o Eliminate duplicates from the Vendor and Item Number dropdowns
o Removed a part number lookup that was being done at startup since it was made unnecessary when we changed to copying the writeoff totals from the Vendor section. This has the effect of speeding up the startup.
Note that while in version 53 I forgot to update the workbooks version I did remember to do it this time so you'll have to update your Access FileVersion Help Desk version to 54.
ADS-Parts-Help-Desk-V54.xlsm
ASKER
I'm testing this version now. So far, everything looks great!
ASKER
That doesn't happen to me so I either need detailed information about what you did prior to emailing and/or a workbook with data that produces the error when the email button is clicked.
ASKER
I have attached the workbook that was giving the error. Also, I overlooked an issue concerning the Vendor Name on the top portion of the RA Form. Currently it is showing the Customer name instead.
Also, would it be a detriment to functionality if we added some sort of progress bar/msg box while the forms are loading.
ADS-Purchasing-Portal-5.4.xlsm
Also, would it be a detriment to functionality if we added some sort of progress bar/msg box while the forms are loading.
ADS-Purchasing-Portal-5.4.xlsm
ASKER
The only reason I asked about the progress bar is because after about 15 seconds of loading the selected form, Excel shows a "(Not Responding)" message at the top of the Windows screen with the Active Workbook.
Another reason might be is that you or someone else may have that file open outside of our workbook.
ASKER
That was the issue. I had the ADS.mdb open, I closed it and it ran without any problem. I'm sorry about that man, I should have known better. :/
I overlooked an issue concerning the Vendor Name on the top portion of the RA Form. Currently it is showing the Customer name instead.For this field I was incorrectly looking at the CUSTOMER field in the SalesOrders&Vendors table. Please verify that I should look at the SUPPLIER field instead.
Also in the worksheet you posted you are using SO# 432895. That number should not appear in the list and the the only reason it does is that the list was not refreshed after I made the change that excludes sales orders that have no items. To refresh the list change temporarily or permanently the Inside rep you are using for testing.
ASKER
Things are looking good here on my end. I am going to go ahead and mark this as solved if you are okay with it. Then, I can add the follow on question concerning the PO - SO Module conversion. Is that okay with you? Thanks
Sure.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Testing this version now. So far, it looks great!!!
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
My apologies for the "abandoned" disposition of this question.