Logical suggestions 1

[ZOR]

Wonder if someone can see a clear way to this maze.

I have a form on which there is a search field for a word or phrase within a certain field (Comments). On entering a word, e.g. "Was", a listbox fills with all records containg the word in their comments field. The user then has the option to save "Was" in a string for later report listfilling (source). The user can then select records from the displayed list of mathcing words, and store their ID's in an array to join in the latter list (report) This all works okay.

My problem is, when the user clears the word/phrase textbox to enter a new word eg "Morning" thats all okay, but if he types in his original word "Was", the list of records appear, however the multiselected list is cleared?

Any ideas of a logical way to fix it?? Thanks

 

[ZOR]

I forgot to add, it's no good omiting them from the list as they might want to go back and unselect them. Thanks

 

[MajP]

Not sure if I fully understand what data you are saving. But if you saved the array (or can get it) of the previously selected IDs you could then pass to this procedure to reselect them.

Public Sub loadSelected(theSelected() As Integer)
  Dim lstBox As Access.ListBox
  Set lstBox = Me.lstOne
  Dim varItem As Variant
  Dim selectedCounter As Integer
  Dim itemCounter As Integer
  For selectedCounter = 1 To UBound(theSelected)
    For itemCounter = 0 To (lstBox.ListCount - 1)
        If lstBox.Column(0, itemCounter) = theSelected(selectedCounter) Then
        MsgBox lstBox.ItemData(itemCounter)
        lstBox.selected(itemCounter) = True
      End If
    Next itemCounter
  Next selectedCounter
End Sub

Basically read each ID in the array and see if it exists in the list. Do that for each item in the array.

 

[ZOR]

Many thanks. I am having problems on the lines:

For selectedCounter = 1 To UBound(theSelected)
ie what is Ubound(theSelected)

and again on
If lstBox.Column(0, itemCounter) = theSelected(selectedCounter) Then

ie the Selected.
I changed the "UBound(theSelected)" to Me.L88.Listcount-1, and it passed through that but stuck on the other line. It looks a good idea to run through the list looking for ID's in the list. Maybe my array of ID's has to go somewhere?

However thanks for the starting point.

 

[MajP]

I passed in an array to the procedure

Public Sub loadSelected(theSelected() As Integer)

"theSelected" is an array of integers representing the ID of a previous selected list.

For example if I want to select the records with IDs of 2,4,and 9

Private Sub Combo2_Click()
  Dim theSelected(1 To 3) As Integer
  theSelected(1) = 2
  theSelected(2) = 4
  theSelected(3) = 9
  Call loadSelected(theSelected)
End Sub

"UBound(theSelected)" returns the upper bound of the array which is the largest available subscript. In this case it returns 3 since my subscripts are 1,2,3

If lstBox.Column(0, itemCounter) = theSelected(selectedCounter)

The above code basically says if the ID from the array equals the value in the first column of the listbox of that row.

I changed the "UBound(theSelected)" to Me.L88.Listcount-1

That does not make sense. In my example my array had 3 values, but my list had about 50 values. So by doing that you would be returning 50 indices, but the array only has 3 values.

 

[ZOR]

The array I am building is of record ID's having been clicked in a list L88

strList2 = ""
For Each Item2 In Me.L88.ItemsSelected
strList2 = strList2 & ",'" & Replace(Me.L88.Column(2), "'", "''") & "'"
Next Item2

If I could get the data back in a loop from strList2 stripped of characters to leave the numeric ID number, combined in another loop looping the Listbox after its filled, I could then reselect records in the displayed list??

 

[ZOR]

Sorry, my last message was sent just as you must have posted. I will look at what you say. Thanks

 

[MajP]

Not sure exactly what your asking, but maybe the split function is what you are looking for. The split function will take a delimited string and turn it into an array. Using my same function I can turn "2,4,9" into an array with values of 2,4,9

Private Sub Combo2_Click()
  Dim theSelected() As Integer
  Dim theString As String
  theString = "2,4,9"
  theSelected = Split(theString, ",")
  Call loadSelected(theSelected)
End Sub

 

[MajP]

Here is an idea. Instead of saving these ideas to an array save them to a table.

So if I first search for "Was" it might match 20 records, and of those I might select 3 with IDs of 2,4,and9. Why not save the selected in a table.

tblSelected
strSearchWord
strSelectedID

The data would look like

was 2
was 4
was 9
morning 5
morning 8
morning 9
morning 33

In this way I could search for many words and select matches. But then I could come back to "was" or any word and reload the values I selected.

 

[ZOR]

Many thanks. Its a good idea, only problem is then having to deal with deleting tables on a networked db.

If only I could find a way to break this array into a loopable format which would release stored record ID's which I could loop with the currently filled listbox, if things matched the its selected, if not its unselected.

It's a holiday today!!

 

[MajP]

Why delete the table. You could add a third field userName in a shared environment

smith was 2
smith was 4
smith was 9
smith morning 5
smith morning 8
smith morning 9
smith morning 33
jones was 11
jones was 4


I think the split function allow you to split your string into an array that you can loop as demonstrated above.

 

[ZOR]

Many thanks for your efforts, worthy of more than 1 star, however I have gone down the easy road (I hope). Instead of putting ID numbers of records selected in a listbox into an array, they are going in a hidden listbox. I can then cycle both listboxes in a loop and select/unselect where needed. Thanks again Regards