Populating a list box from multiple values in a field

[zionman4]

Hey everyone,
I currently have a list box in a form that populates the ProductID field based on the multi select options.
TableA
RecordID = 100
ProductID = MEH, AGH, PVH
USER = jdoe

I now want to populate and sort a list box on another form based on the values stored on the ProductID field. I have another table that stores the Product info based on ID.
Thanks in advance!

Z

 

[Remou]

I imagine something like:

strSQL="Select ProductID,Descript From tblTable "
& "Where ProductID IN (""" _
& Replace(Me.txtProductID, ", ", """,""") & """)"
Forms!OtherForm.lstList.RowSource=strSQL

 

[zionman4]

Thanks Remou, worked like a charm!!!

 

[zionman4]

Though the above code worked great in selecting only those items that are currently on the field, how can I pull all the data from the Product (Source) table but highlight only those selected in TableA?

Thanks!

Z

 

[Remou]

By highlight, do you mean select? If so, the code you use to fill the field from the multiselect should be a good start.

 

[zionman4]

I guess what I mean is that on the entry form, the list box displays all records and the user selects the items from the list. The code then pastes those selected records on a field. Your code works great displaying those selected records but what if the user wants to edit selected records or add new ones? That is why I need to display all records but "highlight" on the list box the ones already selected.

Here is the code I use to add the records:

' Build string criteria for listbox
For Each varItem In Frm.lstProduct.ItemsSelected
intResolution = intResolution & "," & Frm.lstProduct.ItemData(varItem)
Next varItem
If Len(intResolution) = 0 Then
MsgBox ("At least one Product Name is required!"), vbInformation, "No Product Selected"
'Clean Up!
dbs.Close
Set dbs = Nothing
Exit Function
Else
intResolution = Right(intResolution, Len(intResolution) - 1)
intResolution = "'" & intResolution & "'"
End If

'Update Project table
strSQL = "UPDATE tblProject SET ProductID= " & intResolution & "" & _
"WHERE ProjectID = " & Frm.intProjectID & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

 

[Remou]

It may be better to normalize your tables.

Private Sub Form_Current()
alst = Split(Me.ProductID, ",")
For i = 0 To UBound(alst)
    For j = 0 To Me.lstProduct.ListCount
        If Me.lstProduct.Column(0, j) = alst(i) Then
            Me.lstProduct.Selected(j) = True
        End If
    Next
Next
End Sub

 

[zionman4]

This is exactly what I was looking for. I really appreciate it Remou!!

 

[GreenwoodKevin]

I'm having a problem with code similar to this.

----------------------------------------------

Private Sub cmdFind_Click()

Dim answer As String
Dim rowCount As Integer
Dim counter As Integer

rowCount = ListOrders.ListCount - 1

answer = InputBox("Enter the sales order to search for.", "Search")

For counter = 0 To rowCount
If CStr(ListOrders.Column(2, counter)) = Trim(answer) Then
ListOrders.Selected(counter) = True
ListOrders = ListOrders.Column(0)
MsgBox "Record found. Be sure to click it to 'select' it for editing or printing.", vbInformation, "Record Found"
Exit Sub
End If
Next counter

MsgBox "Unable to locate the record. Please ensure you have typed it correctly." & _
Chr(13) & "You may also need to change your filtering criteria at the bottom.", vbExclamation, "Not Found"

End Sub

--------------------------------------

It successfully highlights a record in the listbox if it finds it, but it doesn't actually "select" it. The row will highlight, but it won't get surrounded by a dotted line. In other words, it's not actually accepted into memory. So, when someone 'finds' a record and the click the [Report] button, the code looks for the selected data, but there is none. If the user physically 'clicks' the row once it's found, everything works properly.

Has anyone ever experienced this? Can anyone help? Thanks so much, in advance, for your help.