Userform Selecting listbox items erroneously

Heres todays daft issue...

Im testing a system which is Excel (2013) with DAO to Access tables, theres 50+ users in different locations around the country. All users are on standardised machines that should all be built to the same spec, same add-ins etc so it shouldnt be an issue with the Excel version/build.

The app is a single Excel template file so each user is running their own copy.

Within it is a form that opens for users to select 1 or more failure reasons (this is displayed whenever the user selects FAIL from one of the 8 question listboxes on another form) , this "Failure Reason" form contains a straight forward ListBox which populates from an array (previously pulled in from the Access database), theres also code in there to automatically select the relevant options if the question has already been answered.

For all but two of these users this works fine, no problems at all, it gives a list of non selected items when appropriate and selected items correctly when needed.


Then there are the other 2 users, who coincidentally sit in the same location as me (and I dont have any problems)

For them this works fine for 7 of the 8 questions that use this form however when they select FAIL on Question 3, the form pops up with options 1 and 3 already selected. This happens on each and every case they do.

I've sat with each of them and stepped through the code from the point of them clicking the FAIL option, nothing different happens in their code to anyone elses HOWEVER when we step through, it doesnt happen, its only when its carried out in normal running.

Im at a loss and to be honest cant even word a google search to correctly portray my issue! (and probably havent worded this well either!)

Heres the Initialize code

Private Sub UserForm_Initialize()
Dim answercount As Integer
Dim howmanyitems As Integer
Dim thisitem As String
Dim vFails As Variant
Dim a As Integer, b As Integer, possanswers As Integer
Dim tmpSQL As String
LbxFailReason.Clear
possanswers = 0
For a = 1 To UBound(varFailRsn, 1)
If (varFailRsn(a, 1) = strQstn) Then
    LbxFailReason.AddItem varFailRsn(a, 2)
    possanswers = possanswers + 1
End If
Next a
LbxFailReason.AddItem "Other"
If (Mid(AllQuestionsComplete, Val(strQstn), 1) = "F") Then   'this is the check to see if the question has already been answered.
tmpSQL = "SELECT Fail_Reason FROM tblFails WHERE ((tblFails.CaseID = " & currCaseID & ") AND (tblFails.Question = " & strQstn & "));"
Call TableAccess(tmpSQL, vFails)
        If (Empty_Array(vFails)) Then
        'do nowt
        Else
       For answercount = 0 To possanswers
        thisitem = LbxFailReason.Column(0, answercount)
        For b = 0 To UBound(vFails, 2)
            If (vFails(0, b) = thisitem) Then
                LbxFailReason.Selected(answercount) = True
            End If
        Next b
        Next answercount
        End If
End If
Mid(AllQuestionsComplete, Val(strQstn), 1) = "F"

End Sub

And I know someone will ask for the workbook to be uploaded but unfortunately its totally dependent on the database and linking to Outlook for user info so would need numerous changes just to get it to open for anyone outside the organisation.

Ive now been told a user in another location gets options 1 and 6 selected on Question 4.

Ive asked all users to go back in and do 6 cases, checking for this but so far it appears we've got
51 users with no problems
2 where Q3 shows options 1 and 3
1 where Q4 shows options 1 and 6


Forgot to mention, in all the above, if these users open a case that has already been done then they get the correct answers selected eg if they open a case where Q3 should have option 4 selected then thats exactly what they get, not options 1,3 and 4 as you may expect.

That was my 1st thought but Ive since returned the Db to a blank state, got these people to go in and same thing happens on every case.

Originally Posted by Kyle123

I'd still print out the SQL and run it against the database.

Ill give it a go but like I say I had an empty tblFails table at the point the user tested this on the 2nd run which I checked and was still empty just before they clicked the Fail, I then checked it after the form was displayed and still empty so its n ot even as if its inserting the answers somehow beforehand.

Originally Posted by Kyle123

Secondly, you have what appear to be a number of public variables, what's the scope of these?

They're Global variables ie Defined in Module 1 and available to a number of forms. The two main variables used in the code above are currCaseID which stores the ID number of the case you're dealing with and strQstn which is the number of the question. Ive Watched these variables many times during development and both act exactly as expected.

Originally Posted by Kyle123

Thirdly, how are you initializing the userform? Are you creating a new instance for each question?

New instance each time, at the end of the forms use it is UNLOADed ie

Unload frmFails

and then opened with

frmFails.Show

Originally Posted by Kyle123

If it's not point one or two, we're gonna need to see more code, it doesn't matter if the code doesn't run, we just need to see what you're doing

I'll write another post with the code immediately before the Fail form is opened.

This is the code thats ran immediately before opening the form in question.

When a user clicks FAIL in one of the 8 questions, the Question Number and Answer theyve provided is passed to the following Sub eg

Call Record_Answer("3","Fail")

Private Sub Record_Answer(TheQuestion, TheAnswer)
Dim SQLOut(15) As Variant
    'Delete any existing answers
    SQLOut(1) = "DELETE tblAnswers.CaseID, tblAnswers.Q_No FROM tblAnswers WHERE (((tblAnswers.[CaseID])=" & currCaseID & ") AND ((tblAnswers.[Q_No])= '" & TheQuestion & "'));"
    'add any new ones
    SQLOut(2) = "INSERT INTO tblAnswers ( CaseID, Q_No, Answer, Delay, Other ) SELECT " & currCaseID & ", '" & TheQuestion & "', '" & TheAnswer & "', " & TheDelay & ", '" & strOtherReason & "';"
    SQLOut(3) = "DELETE CaseID FROM tblFails WHERE ((tblFails.CaseID = " & currCaseID & ") AND (tblFails.Question = " & TheQuestion & "));"
    Call InsertArrayload(SQLOut, 4)
'if Its a Fail then set some indicators and open the Failures form
If (TheAnswer = "Fail") Then
        strOtherReason = ""
        TheDelay = 0
        intFailCall = 1
        strQstn = TheQuestion
        frmFail.Show
Else
'otherwise everything is cool and we can just update the Answers indicator to show this as Pass or NA
    Mid(AllQuestionsComplete, TheQuestion, 1) = Left(TheAnswer, 1)
End If

End Sub

basically, it
1. Deletes any records in the tblAnswers table (which contains a number of fields including the "Pass", "Fail","NA" status of the question)
2. Deletes any records in tblFails table for that case and question.
Inserts a new record in tblAnswers with the relevant answer, the answers for tblFails will then be INSERTed when the Save button is clicked on the form

If its a FAIL then it resets a couple of indicators that may be used later on and opens the frmFail form (the one I have a problem with)


I know what you're saying about Global Variables and would agree however I inherited this system at the point of going into user testing and although Im willing to do it, would involve a lot of rewriting to ensure its passing the variables here there and everywhere when required.

I'll give the new Instance a go but surely, if it was something like that (or the Global variables) then it would affect all users? Thats the bit thats got me most confused how its only 3 users and alwys the same questions in each users case.

It'll be something they're doing weird in their process flow that you didn't consider, we have these problems all the time at work!

That delete syntax is really weird, is it working?

You're also treating the question number as string in one table and number in another - is that right?

Unfortunately yes, its right, the designer of the database appears to have just randomly decided a fields type on the toss of a coin or something.

Id like to change it but am wary that there'll be numerous knock on effects, its something I'll look at over time though.

Whats your thoughts on the DELETE statement? I know normally I would just write it as

DELETE * FROM .......

but what they have seems to work fine (Im guessing theyve created the Queries in Access by just dropping the fields they knew were needed in and then making it a DELETE query), it definitely appears to work ok though.

Im confident its not a busines process thing though, we've got a couple of hundred test cases in the system, I can sit at one machine and select a random one while the affected users sits at another PC doing exactly the same steps on another case totally picked at random yet we always end up with different outcomes on that Question.

UPDATE:

This appears to be a machine specific issue, if I log onto the PC of an affected user then it happens to me and if they log onto mine, all is fine.

Ive added in code to deselect all items at the very end of the Form Initialize but still it happens. Its literally as if the selection happens outside of the form actions ie it completes Initializing and then selects the items once the form has finished displaying.

Which makes no sense at all!

Nope, Ive compared the references on both machines and the following are used:

Visual Basic For Applications
Microsoft Excel 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Windows Common Controls-2 6.0 (SP6)

Im not sure it is being used, Ive disabled it and cant find any functionality that isnt working as a result.

The problem is still there though.

I think Ive found the problem now just need to find the solution.

When the frmFail form opens up it is selecting the item directly under the mouse cursor. The reason it wasnt happening for me at all on my main PC is that I was opening the Workbook and moving it to the 2nd screen, when this form opens it does so automatically on the lefthand screen so was never popping up over the calling form.

Those that were mainly hitting problems are the ones using a single small screen

its definitely NOT though, down to users clicking inadvertently etc.

In the following images the red dot shows where (on the form below) I clicked, as you can see it always corresponds to the 2nd option that the system is incorrectly selecting, if I use keyboard to select the Fail, then no problems, if I move the app to 2nd monitor then the frmFail form opens on Monitor1 and all is fine. Its only when theres a Fail option directly under where the mouse WAS CLICKED not where the mouse is when the form opens though, you can click Fail, throw the mouse out of the way and it still selects the item where the cursor had been.

Q3.jpg

Q4.jpg

Q4_movedup.jpg

everyone, the only reason it wasnt before was the likes of the form opening on a different screen to the calling form or even clicking to the far right of the Fail row.

For me, I was opening the VBA editor and using that on screen 1 with the App on screen 2 but the form was opening on screen1 so it never affected me, now if I move the app to screen 1 it happens as per above. Ive tried dozens of these tests as well not just the ones above, Ive moved the calling form left, right, up down before clicking and each and every time it selects any item that happens to be where the mouse was as well as item 1 (which is making it even stranger!).

The only mouse click handler is this one which is in an unrelated form and there are no API calls at all in the Project.

Private Sub cmbNote_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    
    If Button = 2 Then
        Set g_txtActiveTextbox = cmbNote
        txtSpellForm = "cmbNote"
        BuildTextboxMenu x, Y
    End If

End Sub

This is everything in frmFail

Dim strFailReason As String

Private Sub UserForm_Initialize()
Dim answercount As Integer
Dim howmanyitems As Integer
Dim thisitem As String
Dim vFails As Variant
Dim a As Integer, b As Integer, possanswers As Integer
Dim tmpSQL As String
LbxFailReason.Clear
possanswers = 0
For a = 1 To UBound(varFailRsn, 1)
If (varFailRsn(a, 1) = strQstn) Then
    LbxFailReason.AddItem varFailRsn(a, 2)
    possanswers = possanswers + 1
End If
Next a
LbxFailReason.AddItem "Other"
If (Mid(AllQuestionsComplete, Val(strQstn), 1) = "F") Then
tmpSQL = "SELECT Fail_Reason FROM tblFails WHERE ((tblFails.CaseID = " & currCaseID & ") AND (tblFails.Question = " & strQstn & "));"
Call TableAccess(tmpSQL, vFails)
        If (Empty_Array(vFails)) Then
        'do nowt
        Else
       For answercount = 0 To possanswers
        thisitem = LbxFailReason.Column(0, answercount)
        For b = 0 To UBound(vFails, 2)
            If (vFails(0, b) = thisitem) Then
                LbxFailReason.Selected(answercount) = True
            End If
        Next b
        Next answercount
        End If
End If
Me.btnFailResonSave.SetFocus
Mid(AllQuestionsComplete, Val(strQstn), 1) = "F"

End Sub



Private Sub btnFailResonSave_Click()
Dim boolOther As Boolean
Dim failreasons(20) As Variant
Dim tempcount As Integer
    intNOTREFRESH = 1
        intQuestion = Val(strQstn)
    
    If LbxFailReason.ListIndex = -1 Then
  
        MsgBox ("No Failure Reason Selected")
    Else
        For tempcount = 0 To countFails
        If (varAllFails(tempcount, 0) = intQuestion) Then
            varAllFails(tempcount, 0) = 0
            varAllFails(tempcount, 1) = ""
        End If
        Next tempcount
        'tempcount = tempcount + 1
         'initialise and reset the variables and array for the question
        intFails = 2

        For x = 0 To 50
        strQFail(intQuestion, x) = ""
        Next x
        failreasons(1) = "DELETE CaseID FROM tblFails WHERE ((tblFails.CaseID = " & currCaseID & ") AND (tblFails.Question = " & intQuestion & "));"
        
        'cycle through the Failure reasons adding the selected items to the questions array.
        For i = 0 To Me.LbxFailReason.ListCount - 1
            If Me.LbxFailReason.Selected(i) Then
                varAllFails(tempcount, 0) = intQuestion
                varAllFails(tempcount, 1) = Me.LbxFailReason.List(i)
                tempcount = tempcount + 1
                failreasons(intFails) = "INSERT INTO tblFails ( CaseID, Question, Fail_Reason ) SELECT " & currCaseID & ", " & intQuestion & ", '" & Me.LbxFailReason.List(i) & "';"

                If (Me.LbxFailReason.List(i) = "Other") Then
                    boolOther = True
                End If
                intFails = intFails + 1
             End If
    ' save the info
        Call InsertArrayload(failreasons, intFails)
        frmFail.Hide
        If (boolOther) Then
            frmOther.Show
        Else
        'do nowt
        End If
        NoteQNo = strQstn
        frmNewNote.Show
        Unload frmFail
    End If
End Sub

Ive tried removing the Sub btnFailResonSave_Click and still the same so its not some rogue code in there. If its to do with code then its going to be in either the Initialize routine or the calling Record_Answer sub (but I really cant see how, that does some actions then call the Form and Exits the Sub.

This is really getting on my wick now like!

Ive created a simple workbook with just that functionality and yes, it still happens.

This one runs much faster than the main one, theres upto a second or so delay in the other workbook but still same result.

good to see you used the right variation of Howay

So Ive reduced all of the code previously detailed to the following:

Where the code below opens the form, it previously called the Record_Answer sub which did a number of database updates etc before opening the form

Private Sub lbQ4_Click()
frmFail.Show
End Sub

The code to populate the frmFail Listbox with the relevant options for the question and set variables etc has been reduced to

Private Sub UserForm_Initialize()
For a = 0 To 10
LbxFailReason.AddItem a
Next a
End Sub

So now you click and it opens the form with options 0 to 10 listed, nothing else is done, thats all the code from the point of clicking Question 4 through to opening the form. Issue is still there! Form opens and items of 0 and 5 are selected, ie the 1st in the list and the item directly under the mouse. I cant reduce this any further down.

Id also point out that now Im at home and am on a totally different setup to work so its unlikely to be something PC/operating system related.

FINALLY! MY EUREKA MOMENT!

And frankly Im disgusted in myself (and you to be honest Kyle) that neither of us even thought to look into this...........

Modal window, I was displaying a non-modal window on top of a modal one, still not sure why it causes that issue but thats fixed it............

however.....theres a new (well old) issue, I remember now why I changed its Modal Property, for 3 of the 8 questions the window pops up as it should but then disappears behind the main form. I tried everything to fix this previously and changing the Modal property was the only one that worked.

Many Many thanks though Kyle for helping me work through that main problem, now if you can just suss out the latest one!