Solved

Allow Requests to be Returned to Sender if Additional Action Required Involving an Excel Help Desk Project

Posted on 2013-11-21
49
219 Views
Last Modified: 2014-01-29
This is in continuation of the question at the link below:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28272871.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
0
Comment
Question by:Christopher Wright
  • 25
  • 22
49 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39666000
Working on it.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39666871
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?
0
 

Author Comment

by:Christopher Wright
ID: 39666957
Yes sir.  That is correct.  It should no longer be a free text field.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39668023
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
0
 

Author Comment

by:Christopher Wright
ID: 39668850
I'm testing this now with my RC.
0
 

Author Comment

by:Christopher Wright
ID: 39678225
I am getting the following error message.  I have this in my db?
Error Msg
Also, I am getting the following error when I open the RA form.  I have tried multiple Sales Rep names other than 'Heath Shulman' as well.  
Sales Rep Error
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39678237
Open up your ADS.mdb database. Do you see the SalesRep&Vertical table. If so and it's a link, can you open it?
0
 

Author Comment

by:Christopher Wright
ID: 39678249
Yes sir.  It is a linked table and it opens no problem.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39678291
You must be pointing to the wrong ads.mdb.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39678345
If you are at work the database it's looking at is the one on your server.
0
 

Author Comment

by:Christopher Wright
ID: 39678377
That is what I am referencing in terms of my database. I am using the files on the network
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39678424
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.
0
 

Author Comment

by:Christopher Wright
ID: 39678892
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

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39678933
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?
0
 

Author Comment

by:Christopher Wright
ID: 39678945
I'm thinking it is the space " " in front of the SALES_REP_NAME (ie: ' CHRIS WRIGHT') Weird how that happened..?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39679024
That is very strange. In that same code try doing it this way. Change line #1 as posted above by you to      
 .Range(INSIDE_REP) = Application.UserName

Open in new window

and change line 21 to
rs.Open "Select Vertical from [SalesRep&Vertical] Where SALES_REP_NAME = ' " & .Range(INSIDE_REP).value & "'", gConn

Open in new window


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

Author Comment

by:Christopher Wright
ID: 39679317
I received this error from those changes:
Error Msg
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39679333
Can you post the the code from that event please?
0
 

Author Comment

by:Christopher Wright
ID: 39679341
I am only getting this error if the 'Associated Write Offs' checkbox is not checked.
0
 

Author Comment

by:Christopher Wright
ID: 39679432
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

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39681466
To correct that error make this simple change.

In the CustomerRow function, change xlValues to xlFormulas.
0
 

Author Comment

by:Christopher Wright
ID: 39681540
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!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39681701
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?
IndexesIf 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":)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 39681725
BTW you probably should change your production ADS.mdb so that the tables are imported rather than linked.
0
 

Author Comment

by:Christopher Wright
ID: 39681789
Strange chocolate pie?  Sounds interesting.

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

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39681843
It's really good. You can ignore the hand mix and convection oven comments.
Strange-Chocolate-Pie.jpeg
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39682222
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?
0
 

Author Comment

by:Christopher Wright
ID: 39682380
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
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:

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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39687623
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.
        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

Open in new window


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

Author Comment

by:Christopher Wright
ID: 39689836
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
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!!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39691032
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
0
 

Author Comment

by:Christopher Wright
ID: 39692399
I'm testing this version now.  So far, everything looks great!
0
 

Author Comment

by:Christopher Wright
ID: 39698306
I ran into an issue when we tried to start emailing the RA form.  I have provided the error message below:

Error Msg
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39698810
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.
0
 

Author Comment

by:Christopher Wright
ID: 39701562
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
0
 

Author Comment

by:Christopher Wright
ID: 39701568
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39701704
The wb you attached works fine for me so I think that both the problem you reported in post 39698306 and the Not Responding problem are probably being caused by your ADS.mdb not being up to date. In other words the ads.mdb on your server may not have all the required tables as shown here.
ADS.MDB
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39701713
Another reason might be is that you or someone else may have that file open outside of our workbook.
0
 

Author Comment

by:Christopher Wright
ID: 39701718
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.  :/
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39702068
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.
0
 

Author Comment

by:Christopher Wright
ID: 39735290
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
0
 
LVL 45

Expert Comment

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

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39736468
Here is version 55. It contains just two changes.

Corrected the Vendor Name problem you pointed out in post ID 39701562
Added error handling and line numbers in Workbook_OPen to help trap any future errors
ADS-Parts-Help-Desk-V55.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 39740482
Testing this version now. So far, it looks great!!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39806047
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 

Author Comment

by:Christopher Wright
ID: 39818710
My apologies for the "abandoned" disposition of this question.
0

Featured Post

How your wiki can always stay up-to-date

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

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now