listbox values and labels: targeting only the value

[travisbrown]

I have a couple listboxes in a form that I'm trying to set up so you can trade values between them. Sort of an "available values" and "current values" setup.

I have it working when just using the numeric ID columns from the tables. What I need to do is display the label column and pass the ID. I'm a bit stuck on this.

Here is the sub that fills the lists:

Private Sub RequeryCategoryLists() 
 
    'On Error GoTo Err_RequeryCategoryLists
 
    ' declare local variables
    Dim strSource As String
  
    ' create source for "Categories Attached" list box

    strSource = "SELECT tblCategory.categoryName FROM tblCategory INNER JOIN tblCategoryKey ON tblCategory.categoryID = tblCategoryKey.categoryID WHERE  productID = " & productID & ";"
   
    ' display categories attached in list box
   lstProductCategories.RowSource = strSource
 
    ' create source for "Available Categories" list box

   strSource = "SELECT tblCategory.categoryName FROM tblCategory WHERE (((tblCategory.categoryID) Not In (SELECT categoryID FROM tblCategoryKey WHERE ProductID = " & productID & ")));"

    ' display languages still available in list box
   lstAvailableCategories.RowSource = strSource
 
 
Exit_RequeryCategoryLists:
 
    Exit Sub 
 
Err_RequeryCategoryLists:
 
   MsgBoxErr.Description
    Resume Exit_RequeryCategoryLists
 
 
End Sub

When I want to add a category, it wants to pass the label as the value. I don't know how to find the ID and pass it rather than the label. Is there a way to have two values in a listbox, or is there a better way to set this up?

The offending line is !categoryID = Me.lstAvailableCategories.Value

Private Sub cmdAddCategory_Click()
 
    'On Error GoToErr_cmdAddLanguage_Click
 
    ' declare local variables
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    ' open database and recordset objects
    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rs = db.OpenRecordset("tblCategoryKey", dbOpenDynaset)
 
    ' get values for new record
    With rs
        .AddNew
        !productID = Me.productID
        !categoryID = Me.lstAvailableCategories.Value
        .Update
    End With
 
    ' refresh data in list boxes to show changes
   RequeryCategoryLists
   lstAvailableCategories.SetFocus
 
Exit_cmdAddCategory_Click:
  
    ' close objects
   rs.Close
   db.Close
 
    Exit Sub
 
Err_cmdAddCategory_Click:
 
   MsgBoxErr.Description
    Resume Exit_cmdAddCategory_Click
 
End Sub