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

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?

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

I'm testing this now with my RC.

I am getting the following error message.  I have this in my db?

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.  

Open up your ADS.mdb database. Do you see the SalesRep&Vertical table. If so and it's a link, can you open it?

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.

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.

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

Select allOpen in new window

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

 .Range(INSIDE_REP) = Application.UserName

Select allOpen in new window

and change line 21 to

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

Select allOpen in new window

I added a space right before the first single quote.

I received this error from those changes:

Can you post the the code from that event please?

I am only getting this error if the 'Associated Write Offs' checkbox is not checked.

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

Select allOpen in new window

To correct that error make this simple change.

In the CustomerRow function, change xlValues to xlFormulas.

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!

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.

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

Select allOpen in new window

It's really good. You can ignore the hand mix and convection oven comments.
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?

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.

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

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

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:

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?

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.

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

I'm testing this version now.  So far, everything looks great!

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

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.

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

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.

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.

Another reason might be is that you or someone else may have that file open outside of our workbook.

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.

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.

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.

My apologies for the "abandoned" disposition of this question.