473,224 Members | 1,485 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 2051
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

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

Similar topics

9
by: lawrence | last post by:
Is there an easy way to sort a 2 dimensional array alphabetically by the second field in each row? Also, when I use sort() on a two dimensional array, it seems to work a lot like...
0
by: johnny | last post by:
hi all, I have an old script which has a dynamically populated form where the user chooses the items from as many list/menu as the categories existing in the database and then on a confirm page...
5
by: Carlos Ribeiro | last post by:
Hello all, I'm posting this to the list with the intention to form a group of people interested in this type of solution. I'm not going to spam the list with it, unless for occasional and...
30
by: Andante.in.Blue | last post by:
I just browsed through some of my Access links when I came across the Ten Commandments of Access (http://www.mvps.org/access/tencommandments.htm). Some of the points I heartily agree with (and...
2
by: Big Time | last post by:
Hi, I have a question regarding using drop down lists and how to get them to limit the list of a subsequent field. I have a database which has the names of colleges. Each college has it's own...
4
by: Thomas Paul Diffenbach | last post by:
Can anyone point me to an open source library of /statically allocated/ data structures? I'm writing some code that would benefit from trees, preferably self balancing, but on an embedded system...
13
by: nyt | last post by:
I have a problem of number and text field. I got the database file(mdb) that contains many combo boxes used and its list values are created by "value list" For eg field Field name= 'furniture'...
1
by: kickergirl | last post by:
I have a database developed in MS Access 2000 that allows people to enter training histories for staff. The staff information is stored in tblStaff, and the training histories are stored in...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.