Combine Multiple Requests in Excel Help Desk Program (if needed)

I'm working on it.

You may just want to use the Help Desk file you already have since I attached the incorrect file.  Please use file attached below:
ADS-Parts-Help-Desk-V38.xlsm

Help Desk Version 39

o. Corrected bug where if there was an error in columns I or J of  the IIN sheet, hitting the "-" icon would not detect the error
o Change the IIN process so that there is just one request type and the former individual IIN requests are now invoked via the clicking of checkboxes which allows those previously separate requests to be done at the same time.
o Added validations to compare MSP, Cost and Price List Value

Database Version 3
Changes to RequestTypes
ADS-Parts-Help-Desk-V39.xlsm
TrackRequests.mdb

Before I started testing the new workbook in Thread ID 39310508, a couple of my users ran into the error below.  Just a heads up, this is a screenshot from just one of my users, the link in the error message varies depending on username.

For instance if I were to have the error (which I do not) the error would read:

In Workbook_Open (near the bottom), change line 3 to

        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PRODUCTION_PATH & "\TrackRequests.mdb"

        ' Reopen the connection
        Set cn = New Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.ThisWorkbook.Path & "\TrackRequests.mdb"
        Set rs = New ADODB.Recordset
        rs.Open "Select * from UsersOfHelpDesk Where UserName = '" & Environ("USERNAME") & "'", cn, adOpenDynamic, adLockOptimistic
        rs.Fields("TablesVersion") = intAccessTablesVsn
        rs.Update
        rs.Close
        Set rs = Nothing
    End If
    Set cn = Nothing
    '******* 37b End *********

    '******* 35k End *********
    '******* 36c Start *******
    Application.StatusBar = False
    '******* 36c End *********
 
End Sub

Select allOpen in new window

Pertaining to the new version.  I really like the concept!!  It makes the form much more dynamic.  I do have one question.  Is it possible to have the checkboxes remain stationary and be LEFT justified?  Or, another thought is to move the checkboxes to the Home page for selecting.  Something like a dependent combo boxes that will only show the Attributes if the UPDATE ITEM CARD VALUES request is highlighted/selected?  This way, when the IIN form opens, it will automatically show just the needed columns?  This is merely a thought, don't even think twice if this will cause an excessive amount of work.  Either way works great for me.

Besides being a lot of work, moving the sub-choices to the home page would eliminate the flexibility of being able to just check and uncheck the boxes on the same page. In other words in your scenario if you chose "expire" you would be sent to the IIN sheet but if you then decided you wanted to do, say, "Berry" at the same time you'd have to go back to the Home page and reselect both.

I can definitely left-justify the checkboxes. I only right-justified them because the buttons on all the sheets are right-justified and I thought it would be more consistent that way. So here are the options

1.    Keep the buttons right-justified on all sheets and left justify the checkboxes on the IIN sheet, or
2.    keep the buttons right-justified on the BOM and NIUF and left justify both the icons and the checkboxes on the IIN sheet, or
3.    Left justify the buttons on all sheets and left-justify the checkboxes on the IIN sheet.

Your choice but #1 is the least work.

If you will allow me to shorten the captions as shown, or with a few additional words or abbreviations that you might suggest, I can fit them on one one.

In response to Thread ID: 39311672, I did not even consider the loss of functionality with my concept.  GREAT CATCH!!! Thanks Marty.  To address the options you presented, I would go with the first option; however, after seeing your suggestion in Thread ID: 39312269, I prefer that look best.  I wanted to make sure I addressed both threads but IMO, Thread ID 39312269 looks best.  Thank you!

While testing the function of the 'auto-update' to a newer version, I ran into this error:



Also, I am not sure if these are related, but my users are still encountering the error below:


Then, they are taken to this sheet....

Did you do what I suggested in post 39311325?

Yes sir.  I did.  It seems to be only 2 of my users.  I am going to sit down with them individually when they get in tomorrow morning.  No one else is having this issue.  I am almost certain it is a localized issue. I will keep you posted.  Thanks

My apologies.  I thought I did what you recommended, however I must have saved over it.  I made sure I did that and I still get the 'Permission Denied' msg.

Is it still just two people having this problem? And could you please post the Workbook_Open code for one of them?

I am having the problem as well, but instead of getting the login error, I am getting the permission denied.  I have attached my Open Workbook as well.

Private Sub Workbook_Open()

    Dim sht As Worksheet
    Dim intIndex As Integer
    Dim x As Long
    '******* 30f Start *******
    Dim strTempFilePath As String
    Dim strTempFileName As String
    '******* 36b Start *******
'    Dim strThisFileName As String
'    Dim strFilePath As String
    Dim strNameAndPath As String
    '******* 37b Start *******
    Dim intCurrTablesVsn As Integer
    Dim intAccessTablesVsn As Integer
    '******* 37b End *********
    
    Const FILE_NAME = "ADS Parts Help Desk"
    '******* 36b End *********
    '******* 30f End *********
    
    '******* 36c Start *******
    Application.StatusBar = "Welcome!"
    '******* 36c End *********
    
    Dim cn As ADODB.Connection, rs As Recordset
    Set cn = New Connection
    Select Case Environ("USERNAME")
        Case "Martin Liss", "Martin Lissx"
            cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.ThisWorkbook.Path & "\TrackRequests.mdb"
        Case Else
            '******* 33a Start *******
            If Not FileExists(PRODUCTION_PATH & "\TrackRequests.mdb") Then
                MsgBox "Tracking database not found, perhaps because you are not connected to the production server. " _
                     & "Processing can not continue unless the connection is restored.", vbOKOnly + vbCritical, "Database not found"
                ActiveWorkbook.Close True
                Exit Sub
            End If
            '******* 33a End *********
            cn.Open PRODUCTION_CN
    End Select

    Set rs = New ADODB.Recordset
    rs.Open "Select * from FileVersions Where File = 'Help Desk'", cn

    If rs!Version <> FILE_VERSION Then
        '******* 36b Start *******
'        If vbOK = MsgBox("Your version of the ADS Help desk is out of date. Click OK to replace it.", _
'             vbCritical + vbOKCancel, "Workbook Out of Date") Then
'             ' Save the name and path to this file
'            strThisFileName = ThisWorkbook.Name
'            strFilePath = ThisWorkbook.Path
        If vbOK = MsgBox("Your version of the " & FILE_NAME & " is out of date. Click OK to replace it.", _
             vbCritical + vbOKCancel, "Workbook Out of Date") Then
             ' Save the name and path to this file
            strNameAndPath = ThisWorkbook.FullName
        '******* 36b End *********
            ' Get the path to the user's Temp directory
            strTempFilePath = Environ$("temp") & "\"
            ' Create a unique temporary file name
            '******* 36b Start *******
'            strTempFileName = "ADS Parts Help Desk" & Format(Now(), "ddmmyyyyhhmmss") & ".xlsm"
            strTempFileName = FILE_NAME & Format(Now(), "ddmmyyyyhhmmss") & ".xlsm"
            '******* 36b End *********
            ' Save this WB as the temp name (it becomes the temp file as this point)
            ActiveWorkbook.SaveAs strTempFilePath & strTempFileName
            '  Delete the out of date file on the PC
            '******* 36b Start *******
'            Kill strThisFileName
            Kill strNameAndPath
            '******* 36b End *********
            ' Copy the new file from the server
            MsgBox "This workbook will now close. Please wait while we replace it with the new workbook.", vbInformation + vbOKOnly, "Download In Progress"
            Select Case Environ("USERNAME")
                Case "Martin Liss", "Martin Lissx"
                    '******* 36b Start *******
'                    FileCopy "C:\Temp" & "\" & strThisFileName, strThisFileName
                     FileCopy "C:\safari downloads\niuf\production\" & FILE_NAME & ".xlsm", strNameAndPath
                    '******* 36b End *********
                Case Else
                    '******* 36b Start *******
'                    FileCopy PRODUCTION_PATH & "\" & strThisFileName, strThisFileName
                    FileCopy PRODUCTION_PATH & "\" & FILE_NAME & ".xlsm", strNameAndPath
                    '******* 36b End *********
            End Select
            '  Open what is now the new file that just replaced the out of date file
            '******* 36b Start *******
            'Workbooks.Open ThisWorkbook.FullName
            Workbooks.Open strNameAndPath
            '******* 36b End *********
        End If
        rs.Close
        '******* 36b Start *******
'        ActiveWorkbook.Close True
        If strTempFileName <> "" Then
            Workbooks(strTempFileName).Close True
        Else
            ActiveWorkbook.Close True
        End If
        '******* 36b End *********
        '******* 33b Start *******
'        Exit Function
        Exit Sub
'        '******* 33b End *********
    End If
    '******* 37b Start *******
    Set rs = New ADODB.Recordset
    rs.Open "Select * from FileVersions Where File = 'Tables'", cn
    intAccessTablesVsn = rs.Fields("Version")
    '******* 37b End *********
    
    rs.Close

    ProtectWorkbook UNPROTEKT
    
    On Error Resume Next
    Sheets(HELPDESK_SHEET).Shapes("lblCredits").Left = 700
    Sheets(HELPDESK_SHEET).Shapes("lblCredits").TextFrame.Characters.Text = Range("Credits")
    On Error GoTo 0
    
    ' So that the user doesn't see even a glimpse of any sheet
    ' except the this one at startup
    Worksheets(HELPDESK_SHEET).Activate
    Worksheets(HELPDESK_SHEET).Visible = xlSheetVisible

    Application.ScreenUpdating = False

    On Error Resume Next
    Application.ErrorCheckingOptions.NumberAsText = False

    Worksheets(NIUF_SHEET).Shapes("picShowErrorsNIUF").Visible = msoTrue
    Worksheets(NIUF_SHEET).Shapes("picShowAllNIUF").Visible = msoFalse
    Worksheets(BOM_SHEET).Shapes("picShowErrorsBOM").Visible = msoTrue
    Worksheets(BOM_SHEET).Shapes("picShowAllBOM").Visible = msoFalse

    PositionIcons
    
    For Each sht In Worksheets
        If sht.Name <> HELPDESK_SHEET Then
            sht.Visible = xlSheetHidden
        Else
            sht.Visible = xlSheetVisible
        End If
    Next
    
    '******* 29c Start *******
    '******* 30c Start *******
'    With Worksheets(NIUF_SHEET)
'        .EnableAutoFilter = True
'        .Protect Contents:=True, UserInterfaceOnly:=True
'        .EnableSelection = 1 - xlUnlockedCells
'        .ScrollArea = "A1:L5000"
'    End With
'    With Worksheets(BOM_SHEET)
'        .EnableAutoFilter = True
'        .Protect Contents:=True, UserInterfaceOnly:=True
'        .EnableSelection = 1 - xlUnlockedCells
'        .ScrollArea = "A1:I250"
'    End With
'    With Worksheets(IIN_SHEET)
'        .EnableAutoFilter = True
'        .Protect Contents:=True, UserInterfaceOnly:=True
'        .EnableSelection = 1 - xlUnlockedCells
'        .ScrollArea = "A1:L500"
'    End With
'    Application.Windows(ThisWorkbook.Name).DisplayVerticalScrollBar = True
    '******* 30c End *********
    '******* 29c End *********
    
    ProtectWorkbook PROTEKT
    Application.ScreenUpdating = True

    For x = 1 To 700
        '******* 29z Start *******
'        Sleep 2
        Sleep 0.5
        '******* 29z End *********
        Sheets(HELPDESK_SHEET).Shapes("lblCredits").Left = Sheets(HELPDESK_SHEET).Shapes("lblCredits").Left - 1
        DoEvents
    Next
    
    '******* 35j Start *******
    On Error GoTo 0
    Set rs = Nothing
    Set rs = New ADODB.Recordset

    ' See if this user is in the database
    rs.Open "Select * from UsersOfHelpDesk Where UserName = '" & Environ("USERNAME") & "'", cn, adOpenDynamic, adLockOptimistic
    If rs.EOF Then
        ' He's not so show the welcome message and add him
        frmWelcome.Show
        '******* 37b Start *******
        ' Give the form a chance to close
        DoEvents
        '******* 37b End *********
        rs.AddNew
        rs.Fields("UserName") = Environ("USERNAME")
        rs.Update
        '******* 37b Start *******
        intCurrTablesVsn = -1
    Else
        If Not IsNull(rs.Fields("TablesVersion")) Then
            intCurrTablesVsn = rs.Fields("TablesVersion")
        Else
            intCurrTablesVsn = -1
        End If
        '******* 37b End *********
    End If
    rs.Close
    Set rs = Nothing
    '******* 37b Start *******
'    Set cn = Nothing
    '******* 37b End *********

    '******* 35j End *********
    
    '******* 35k Start *******
    '******* 37b Start *******
'    UpdateTables
    ' uncomment to load the tables and see the progress bar no matter what
'    intCurrTablesVsn = 999
    If intCurrTablesVsn <> intAccessTablesVsn Then
        ProgressDialogue.Configure "Updating tables please wait...", "", 1, 13
        ProgressDialogue.Show

        ' Needs to be closed so it doesn't interfere UpdateOneTable
        Set cn = Nothing
        UpdateTables
        
        ProgressDialogue.Hide
        
        ' Reopen the connection
        Set cn = New Connection
         cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PRODUCTION_PATH & "\TrackRequests.mdb"
        Set rs = New ADODB.Recordset
        rs.Open "Select * from UsersOfHelpDesk Where UserName = '" & Environ("USERNAME") & "'", cn, adOpenDynamic, adLockOptimistic
        rs.Fields("TablesVersion") = intAccessTablesVsn
        rs.Update
        rs.Close
        Set rs = Nothing
    End If
    Set cn = Nothing
    '******* 37b End *********

    '******* 35k End *********
    '******* 36c Start *******
    Application.StatusBar = False
    '******* 36c End *********
 
End Sub

Select allOpen in new window

Okay, so I think there are two different issues going on.  I made the mistake of rolling them all into one in Thread ID: 39312378 but I think that was the wrong approach.  One issue is the auto-update for older versions being updated to new.  For this issue, I posted Thread ID's: 39314590,  39314049.  


The second issue is the login msgbox which is populated when my users open the file.  The msgbox states 'please enter microsoft office access database engine ole db initialization information'.  This was covered in Thread ID: 39312378 in the 2nd and 3rd screen shots I provided. Those are posted below as well.

I'm confused. In any case I think that there are only two ways that we can solve these two problems. One would be for me to somehow be able to connect to your server. I know that that is probably not going to happen so the other way is for you to debug this with whatever help I can give you.

To start that process unlock the code in your local copy workbook and put a breakpoint at the first line in Workbook_Open. Then press f5 to run that sub. The code will stop at the breakpoint and from there press F8 repeatedly to see where the code goes and which line is causing the problem. If we can find and fix the problem you can do the same breakpoint-F5-F8 process on the users' machines.

My apologies for the confusion Marty.  I did as you suggested and stepped through the Workbook_Open procedure.  One thing I found is that while stepping through, I am show the MsgBox that alerts me about the Workbook closing during the update.  I do not get this normally.


After this msgbox, I continued on.  Once I reached the line (line 12 in the code snippet) below, I attempted to step through and got the error msg that the file could not be found.  

Select Case Environ("USERNAME")
                Case "Martin Liss", "Martin Lissx"
                    '******* 36b Start *******
'                    FileCopy "C:\Temp" & "\" & strThisFileName, strThisFileName
                     FileCopy "C:\safari downloads\niuf\production\" & FILE_NAME & ".xlsm", strNameAndPath
                    '******* 36b End *********
                Case Else
                    '******* 36b Start *******
'                    FileCopy PRODUCTION_PATH & "\" & strThisFileName, strThisFileName
'                   The line below is where I encountered the error.  Once it was highlighted
'                   and I pressed F8, it gave me the error.
                    FileCopy PRODUCTION_PATH & "\" & FILE_NAME & ".xlsm", strNameAndPath
                    '******* 36b End *********
            End Select

Select allOpen in new window

One thing I found is that while stepping through, I am show the MsgBox that alerts me about the Workbook closing during the update.  I do not get this normally

That can only happen if your workbook version is out of synch with the Access file version. Make them the same and try it again.

I purposely had them out of synch in order to test the 'auto-update' functionality. Whenever I make them the same, I do not have the error.

To add to the second issue I mentioned in Thread ID 39314753, all of my users are getting this error for some reason.  It prompts each user with the msgbox below:

When they click 'OK', then this screen pops up:

Okay, I stepped through the code on my users machine.  The login msgbox populates after this line of script:

 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & strAccessPath & ";Mode=1" _
        , _
        ";Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB" _
        , _
        ":Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OL" _
        , _
        "EDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale" _
        , _
        " on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OL" _
        , "EDB:Bypass UserInfo Validation=True"), Destination:=Range(strDestCell)). _
        QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array(strTableName)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_TrackRequests"
        .Refresh BackgroundQuery:=False
    End With

Select allOpen in new window

I hit okay and then this error shows:

Chris, let's not test the purposely out of synch workbook now. Also when I said "make them the same", what I should have said was "make sure you are using the latest version". If that means re-downloading version 39 and Access database version 3 and then making that one-line fix that I posted in post 39311325 then that's what you should do. Once we get all other problems resolved you and I can test the purposely out of synch problem.

Roger that.  I actually did re-download both before I tested.  After your latest post, I re-downloaded again to start from fresh. I made the one-line fix suggested in post 39311325. And I had no problems when I opened.  However, my users are still getting the same error.  I've had them select the 'Open Help Now' option AND then tried the 'Open Help Later' option.  They are getting the same results from both selected options.

My users have both Excel 2007 and 2010 and both version have the same issue.  As for me, I am not getting any errors when I test on my machine(s). I have tested Excel 2007 and Excel 2010 - both 64 bit and 32 bit.  All work fine for me. This error is weird.  You think it may be something to do with the username, since your nor I get an error when opening the file?

Physically go to one of there computers and do what I described in post ID: 39314795. Which line causes that msgbox to appear?

Also once again make sure that you don't have the server Access database open.

To address Thread ID: 39315273: I did go to my users machine and stepped through as suggested.  I identified the line of code that caused the error in Thread ID: 39314938.  I re-posted the code snippet below:

 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & strAccessPath & ";Mode=1" _
        , _
        ";Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB" _
        , _
        ":Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OL" _
        , _
        "EDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale" _
        , _
        " on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OL" _
        , "EDB:Bypass UserInfo Validation=True"), Destination:=Range(strDestCell)). _
        QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array(strTableName)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_TrackRequests"
        .Refresh BackgroundQuery:=False
    End With

Select allOpen in new window

To address Thread ID: 39315274: Nobody goes into the Access database except for myself.  I did attempt to open the worksheet when I had the database open and received the error msg below.  

Okay I think we can ignore the "Error if Access DB Open" at least for now. As for the other error that you talked about in post ID: 39315841. Do you get that error when you open your local copy of the wb on your PC?

Hi Marty.  To answer your question in Thread ID: 39315878, I DO NOT get that error when I open the local copy of the wb on my pc.

So far, so good with testing.  I'll keep you posted

I'm back. What news?

So far so good.  I have one user that reported an error over the weekend but failed to provide any explanation or screen shots.  Once I close the loop with him, we should be all set.  Thanks again Marty!

One thing I did want to ask:  Is it possible to modify the email subject lines to include the Vendor(s)' Name involved in the request being submitted?  This was a request from several of my users that tested.  Thanks again!

What if there's more than one vendor?

That was the thought I had as well.  I met with my users and explained this to them this morning.  It would really not beneficial in the sense they were thinking.  Sorry about that.  

The reason they requested this is due to other requests that they want added to the Help Desk, several of which involve Sales Orders.  There could be multiple vendors per sales order; however, I proposed that instead of worrying about identifying the Vendor in the subject line, just keep the Sales Order as the subject.  The Help Desk could potentially read from the Database table the vendors that are on the sale order and email accordingly.  Of course, the new requests are yet to be developed, but I feel that solution was the best approach.

I am now trying to an access database to my Oracle database to enable this information to be as up to data as possible.  Even on the Database we are using now, if I can somehow enable this, the data would always be up to the minute and I could also include a list of all the items that currently exist to prohibit users from trying to re-create them.

I just did a quick check and apparently connecting to an Oracle database from VBA is not much different than connecting to Access. The problem for me at least is that I don't have any idea how I'd go about getting/creating one to test with.

BTW can we declare this current question complete?

Support was very prompt and speedy.  MartinLiss went above and beyond to far exceed my expectations.  He has taught me a lot and I have made a life-long friend!

I have not been able to connect with VBA yet either.  I have the access required, I am still trying to manipulate the connection string or find out if I am missing something to enable a connection.

Good news!  I have successfully created a link to the database in Access!

Great. When you have it the way you want it I will need a copy of your database to make whatever changes are necessary for the next question.

Roger that.  I can provide a copy of the static Access db.  This will at least show you the data contained, table names, etc.

I can provide a copy of the static Access db

Is this a different database than TrackRequests.mdb? If so then I don't think I need it.

Sorry, I was unable to get the new question posted today. It was an unbearable day at work. I should have everything pulled together first thing in the AM. Please forgive me for not coming through today. Thanks again buddy!

Here are the two databases that are linked
TrackRequests.accdb
ItemMasterDB.accdb