470,630 Members | 1,637 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,630 developers. It's quick & easy.

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

25
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ItemSelect_Click()
  2. On Error GoTo Err_ItemSelect_Click
  3.  
  4. '1. Set variables for sub-routine
  5. Dim stDocName As String
  6. Dim stLinkCriteria As String
  7. Dim stCategory As String
  8.  
  9. Dim strSQL As String
  10. Dim strTableName As String
  11. Dim strLinkCriteria2 As String
  12.  
  13. '2. Set value of CategoryName according to value of UniqueID
  14. '      QryItemList is a union query of 3 tables with common fields
  15. strLinkCriteria2 = "QryItemList.UniqueID=" & "'" & Me![ListBoxData] & "'"
  16.  
  17. strSQL = "INSERT INTO tblTemp " & _
  18.     "SELECT QryItemList.UniqueID, QryItemList.CategoryName " & _
  19.     "FROM QryItemList " & _
  20.     "WHERE " & strLinkCriteria2 & ";"
  21.  
  22. DoCmd.SetWarnings False
  23. DoCmd.RunSQL strSQL
  24. DoCmd.SetWarnings True
  25.  
  26. '3. Match CategoryName to correspondaing form name
  27. stCategory = QryItemList.CategoryName
  28. If stCategory = "Category1" Then
  29.         stDocName = "frmCategory1"
  30.     ElseIf stCategory = "Category2" Then
  31.         stDocName = "frmCategory2"
  32.     ElseIf stCategory = "Category3" Then
  33.         stDocName = "frmCategory3"
  34. End If
  35.  
  36. '4. Open form according to CategoryName and record according to UniqueID
  37. stLinkCriteria = "[UniqueID]=" & "'" & Me![ListBoxData] & "'"
  38. DoCmd.OpenForm stDocName, , , stLinkCriteria
  39.  
  40. '5. Delete temporary table before exiting the sub-routine
  41. db.TableDefs.Delete ("tblTemp")
  42.  
  43. Exit_ItemSelect_Click:
  44.     Exit Sub
  45.  
  46. Err_ItemSelect_Click:
  47.     MsgBox Err.Description
  48.     Resume Exit_ItemSelect_Click
  49.  
  50. End Sub
  51.  
Oct 30 '07 #1
3 1856
Rabbit
12,516 Expert Mod 8TB
I didn't read the entire post but I think you're looking for:
Expand|Select|Wrap|Line Numbers
  1. Me.lstBoxName.Columns(Index)
Where index is a number from 0 to number of columns minus 1.
Oct 30 '07 #2
dstorms
25
I didn't read the entire post but I think you're looking for:
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ItemSelect_Click()
  2. On Error GoTo Err_ItemSelect_Click
  3.  
  4. Dim stDocName As String
  5. Dim stLinkCriteria As String
  6. Dim stCategory As String
  7.  
  8. stCategory = Me.ListBoxData.Column(1) ' <-- Uses Data in second column 
  9.  
  10. If stCategory = "Category1" Then
  11.         stDocName = "frmCategory1"
  12.     ElseIf stCategory = "Category2" Then
  13.         stDocName = "frmCategory2"
  14.     ElseIf stCategory = "Category3" Then
  15.         stDocName = "frmCategory3"
  16. End If
  17.  
  18. stLinkCriteria = "[UniqueID]=" & "'" & Me![ListBoxData] & "'"
  19. DoCmd.OpenForm stDocName, , , stLinkCriteria
  20.  
  21. Exit_ItemSelect_Click:
  22.     Exit Sub
  23.  
  24. Err_ItemSelect_Click:
  25.     MsgBox Err.Description
  26.     Resume Exit_ItemSelect_Click
  27.  
  28. End Sub
  29.  
Thank you for your help!
Oct 31 '07 #3
Rabbit
12,516 Expert Mod 8TB
Not a problem, good luck.
Oct 31 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by johnny | last post: by
30 posts views Thread by Andante.in.Blue | last post: by
4 posts views Thread by Thomas Paul Diffenbach | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.