Use Query to set Multi Select List Box Selections? 3

[DBLoser]

(Access 2003)
There's lots of information on how to use a multi select list box's selection to filter something. I'm thinking of a backwards approach. Is it possible to use a query to set the selections in a multi select list box? I would like to do this to allow a user to see the "default" selections in the multi select list.

Here's the application. An engineer chooses a material specification and based on it certain welding procedures will apply. It would be really nice to have the multi select list box pre-select the default procedures. Other procedures may apply but at the discretion of the Engineer.

ty in advance

 

[DBLoser]

Maybe a multi select list box is a bad way to go. What about using a subform? I can filter a subform based on a value on the main form but how can you update records in the subform. For example, the subform shows the entire domain but toggles a checkbox only on the rows based on a value in a text box from the main form.

 

[TheAceMan1]

How are ya DBLoser . . .

You need a seperate table containing all welding procedures including a checkbox to identify defaults. This table would be linked to the primarykey of your Material Spec table. In this way you could easily change/add/remove procedures and/or defaults as necessary.

The above would also make it easy to query defaults. Using this query as the source for a recordset you simply parse each returned default procedure from the query against the listing of the combobox and set the [blue]Selected[/blue] property of that combo index when a match is found.

You may also want to consider the following method:
Moving Items Between two ListBoxes

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[seaport]

Hi, DBloser,

Whether you use listbox or subform, you need to write the similar vb code to pre-select.

Also, if you use a subform, that form has to be bound to a temp local table, which can be avoided if you use listbox.

 

[dhookom]

You can use code like the following:

Private Sub cmdLoadProducts_Click()
    Dim lbo As ListBox
    Dim itm As Variant
    Dim intI As Integer
    Dim strSelectedProducts As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT prodTagID from tblSelectedProducts")
    With rs
        Do While Not .EOF
            strSelectedProducts = strSelectedProducts & "~" & .Fields("prodTagID")
            .MoveNext
        Loop
        Close
    End With
    Set rs = Nothing
    strSelectedProducts = strSelectedProducts & "~"
    Set lbo = Me.lboProducts
    For intI = 0 To lbo.ListCount - 1
        If InStr(1, strSelectedProducts, "~" & lbo.ItemData(intI) & "~") > 0 Then
            lbo.Selected(intI) = True
        End If
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Assuming you have a query that has a field to identify a default, include that field in the rowsource for the listbox. You can hide that field in the listbox by setting the width to 0. Then simply read the items in the listbox and hilite the ones with a default. This example uses a yes no field in the second column (index of 1 because columns are zero indexed)

Private Sub Form_Load()
  hiliteDefaults Me.lstOne
End Sub

Public Sub hiliteDefaults(lst As Access.ListBox)
  Dim varItm As Variant
  Dim i As Integer
  For i = 0 To (lst.ListCount - 1)
    If lst.Column(1, i) Then
      lst.Selected(i) = True
    End If
  Next i
End Sub

 

[DBLoser]

Thank you for the posts. I will have time to try these today. The defaults will be determined by a string search. For example, we have procedures named like (and this would be the multiselect list box):

1 WPS1-0808M03
2 WPS1-0808M03R
3 WPS1-0808T01
4 WPS1-10H10H
5 WPS1-10H10HM01A

A text box would have either 8 or 10 in it. If it contained 8 then the default selection would be all procedures with 8 in them:

WPS1-0808M03
WPS1-0808M03R
WPS1-0808T01

 

[dhookom]

It would really help if you had:
- suggested the string matching earlier
- provided the name of the text box
- provided the name of the list box

Rather than attempting to string match, I much prefer entering relationships between values into tables. However, try something like:

Private Sub ATextBox_AfterUpdate()
  If Not IsNull(Me.ATextBox) Then
    hiliteDefaults Me.lstOne
  End If
End Sub

Public Sub hiliteDefaults(lst As Access.ListBox)
  Dim varItm As Variant
  Dim i As Integer
  For i = 0 To (lst.ListCount - 1)
    If Instr(lst.Column(1, i),Me.ATextBox) > 0 Then
      lst.Selected(i) = True
    End If
  Next i
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[DBLoser]

I am sorry I left out the details. I agree, I don't like the string searches either and there's lots of extra characters within the numbers. I'll provide details:

Form name is OtherSelect

The mtl spec combobox is called cboSpec1

The WPS multiselect box is called lstWPS

I think, as suggested by TheAceMan1, having each spec's defaults be a subset is the best way to go. But I really like the search methods posted above. I wish I was a real programmer and knew this stuff.

 

[MajP]

Probably could add to Duane's idea to select and unselect based on changes in the textbox

Change:
If Instr(lst.Column(1, i),Me.ATextBox) > 0 Then
lst.Selected(i) = True
End I
To:
lst.Selected(i) = Instr(lst.Column(1, i),Me.ATextBox) > 0