UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA - Select From Listbox Problems, Access 2007    
 
   
jhallcb
post May 18 2017, 05:38 PM
Post#1



Posts: 7
Joined: 2-August 16



Hi all,

Have the below code that is doing everything it should - match results in a query to items in a listbox and select them one by one. Once code finishes all items deselect again? What's confusing me is that the actual line that selects the listbox item works fine on its own and the method is correct. At the moment I am thinking it may be something to do with the loop ending or recordset closing however commenting out the last two lines doesn't help either?

Below code works fine to prove the method and select all:
CODE
Dim r As Long

    For r = 0 To Me.lboSup.ListCount - 1
        Me.lboSup.Selected(r) = True
    Next r



However once the same is modified to fit in the loop, once loop/code finishes, everything deselects again:
CODE
Dim rs As DAO.Recordset
Dim RepName As String
Dim strSearch As String
Dim i As Long


Set rs = CurrentDb.OpenRecordset("qrySupWorkArea")


If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        If IsNull(rs!Supervisor) Then
            rs.MoveNext
        Else
            strSearch = rs!Supervisor
        End If
        strSearch = "*" & strSearch & "*"

        With Me.lboSup
        If .ListCount > 0 Then
            For i = 0 To .ListCount - 1
            If .ItemData(i) Like strSearch Then
                .Selected(i) = True
                'MsgBox "Match: " & .ItemData(i)
            End If
            Next i
        End If
        End With
        
        rs.MoveNext
        Loop
Else
    MsgBox "There are no records in the recordset."
End If

rs.Close
Set rs = Nothing


**Note: Msgbox line that is commented out simply confirms the match has completed and gives time to see the code working on each loop cycle

Any ideas as to how I can rectify this would be lovely as I'm stupped at the moment.
Go to the top of the page
 
GroverParkGeorge
post May 18 2017, 06:43 PM
Post#2


UA Admin
Posts: 29,943
Joined: 20-June 02
From: Newcastle, WA


What is the purpose or goal of this process? Why do it? What is the desired outcome?

Does the list in the listbox change or get requeried at some point? Perhaps at the end of this process?

--------------------
Go to the top of the page
 
BruceM
post May 19 2017, 06:47 AM
Post#3


UtterAccess VIP
Posts: 6,654
Joined: 24-May 10
From: Downeast Maine


I'm not sure what you are trying to do, but perhaps you need to put the strSearch = "*" & strSearch & "*" line directly after strSearch = rs!Supervisor, then put the With Me.lboSup loop directly after that, all within the first If...Then...Else block. After End If do the MoveNext and Loop. If Supervisor is null the code skips to the End If. Whether or not it is null, the MoveNext and Loop take place.

One potential problem is that if Supervisor is Null the string is still formed and you loop through the list box.
Go to the top of the page
 
jhallcb
post May 19 2017, 07:36 AM
Post#4



Posts: 7
Joined: 2-August 16



My thanks to both for your replies...

Grover, basically I have a listbox comprising of an extensive list of supervisors. The selections made will be used as report criteria. Due to the size of the list, options have been requested for select all, random or free select and also for "Direct Reports" (the users subordinates). Whilst it seems a lot of effort for a small improvement, that is my brief. The code posted in the original post is from the "Direct Reports" part that is matching query results to the listbox items.

BruceM, thank you for your suggestion, I will look at the If...Then...Else block if the below idea fails. The nulls are covered by:
CODE
If IsNull(rs!Supervisor) Then
            rs.MoveNext
        Else
            strSearch = rs!Supervisor



After looking at things again today I am starting to think it is the initialisation of the loop - "Do Until rs.EOF = True". I think code is doing exactly as told and I have overlooked... it is doing as it should until it hits the EOF mark and then everything reverts. I can't figure that Do Whilst will be of any more use in this case so I am going to try a work around with a For...Each...Next loop and see if this resolves the issue though I will still need to build in an If statement to handle hitting the EOF mark.
Go to the top of the page
 
BruceM
post May 19 2017, 07:49 AM
Post#5


UtterAccess VIP
Posts: 6,654
Joined: 24-May 10
From: Downeast Maine


If Supervisor is null you move next in the recordset, but then you go ahead and process strSearch by adding wildcards, and loop through the list box. Since strSearch has not been assigned a value, it is "" because it is a string variable. Then it becomes a pair of wildcards, and then you loop through the list box looking for a match for that. It will either select everything or select nothing, I'm not quite sure which.

The first rs.MoveNext does not break you out of the loop. It merely moves to the next item in the recordset before continuing with the code. Then you MoveNext again at the end of the loop. The way it looks to me is that if Supervisor is Null it moves to the next record, then later in the code it moves to the next record again, so in effect you are skipping the record after one where Supervisor is null.
Go to the top of the page
 
GroverParkGeorge
post May 19 2017, 07:59 AM
Post#6


UA Admin
Posts: 29,943
Joined: 20-June 02
From: Newcastle, WA


You know, for trouble shooting purposes, I'd start with just the most basic step in the process, to be sure that works as you think it should, then add additional steps. Check each time you add something to be sure it's performing as you expect. I think Bruce's analysis is right on, but it's hard to know for sure from just looking at the abstracted procedure independently of actual data being processed.

Best of luck,

George

.
This post has been edited by GroverParkGeorge: May 19 2017, 08:01 AM

--------------------
Go to the top of the page
 
RAZMaddaz
post May 19 2017, 08:05 AM
Post#7


UtterAccess VIP
Posts: 9,421
Joined: 23-May 05
From: Bethesda, MD USA


PMFJI!!!!!

Here is an example of a multi-select list box Query. Hold down the Ctrl key to select multiple names in the list.

RAZMaddaz
Attached File(s)
Attached File  MultiSelectQuery.zip ( 15.37K )Number of downloads: 6
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th July 2017 - 01:28 AM