How do I get a second data field from a list box?

[dstorms]

Hi there,

I'm developing a database for my company, and for the most part I've been successful, with some complex sub-routines already working fine. But this one has me stumped!

Some background on this database:

It has numerous and tables and queries, and a few tables have identical field names, and I've created a union query, named QryItemList, to create a list containing the unique identifier number in UniqueID, and included the field CategoryName. Then I created a form with a listbox, labeled ListBoxData, and a button that starts this sub-routine when clicked.

The intended result was open the appropriate form, determined by CategoryName, to the record filtered by UniqueID. Unfortunately the listbox only returns the value for UniqueID, and I've been trying to find a way to get the CategoryName data also. The latest attempt was to create a temporary table that I could append the recordset, and use the CategoryName from the temporary table to complete the sub-routine.

When I clicked on the button I get the rather unhelpful "Object required" error message. Can anyone help me with this code?

A successful subroutine would enable the user to open several different forms from one list and open the correct record in one step instead of two. It may not seem like much of a big deal, but when the list is long and varied it can reduce the action of opening and closing of forms by 75% or more.

Here's the code:

Private Sub ItemSelect_Click()
On Error GoTo Err_ItemSelect_Click
 
'1. Set variables for sub-routine
Dim stDocName As String
Dim stLinkCriteria As String
Dim stCategory As String
 
Dim strSQL As String
Dim strTableName As String
Dim strLinkCriteria2 As String
 
'2. Set value of CategoryName according to value of UniqueID
'      QryItemList is a union query of 3 tables with common fields
strLinkCriteria2 = "QryItemList.UniqueID=" & "'" & Me![ListBoxData] & "'"
 
strSQL = "INSERT INTO tblTemp " & _
    "SELECT QryItemList.UniqueID, QryItemList.CategoryName " & _
    "FROM QryItemList " & _
    "WHERE " & strLinkCriteria2 & ";"
 
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
'3. Match CategoryName to correspondaing form name
stCategory = QryItemList.CategoryName
If stCategory = "Category1" Then
        stDocName = "frmCategory1"
    ElseIf stCategory = "Category2" Then
        stDocName = "frmCategory2"
    ElseIf stCategory = "Category3" Then
        stDocName = "frmCategory3"
End If
 
'4. Open form according to CategoryName and record according to UniqueID
stLinkCriteria = "[UniqueID]=" & "'" & Me![ListBoxData] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
'5. Delete temporary table before exiting the sub-routine
db.TableDefs.Delete ("tblTemp")
 
Exit_ItemSelect_Click:
    Exit Sub
 
Err_ItemSelect_Click:
    MsgBox Err.Description
    Resume Exit_ItemSelect_Click
 
End Sub
 

[Rabbit]

I didn't read the entire post but I think you're looking for:

Me.lstBoxName.Columns(Index)

Where index is a number from 0 to number of columns minus 1.

[dstorms]

I didn't read the entire post but I think you're looking for:

Me.lstBoxName.Columns(Index)

Where index is a number from 0 to number of columns minus 1.

Thanks Rabbit!

The revised code is much simpler and works just fine. I did have to use the singular "Comlumn(1) " instead of the plural "Columns(1) " above in order for it to work.

Here's the modified code:

Private Sub ItemSelect_Click()
On Error GoTo Err_ItemSelect_Click
 
Dim stDocName As String
Dim stLinkCriteria As String
Dim stCategory As String
 
stCategory = Me.ListBoxData.Column(1) ' <-- Uses Data in second column 
 
If stCategory = "Category1" Then
        stDocName = "frmCategory1"
    ElseIf stCategory = "Category2" Then
        stDocName = "frmCategory2"
    ElseIf stCategory = "Category3" Then
        stDocName = "frmCategory3"
End If
 
stLinkCriteria = "[UniqueID]=" & "'" & Me![ListBoxData] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_ItemSelect_Click:
    Exit Sub
 
Err_ItemSelect_Click:
    MsgBox Err.Description
    Resume Exit_ItemSelect_Click
 
End Sub
 

Thank you for your help!

[Rabbit]

Not a problem, good luck.