By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Selecting Data from List Box

P: 31
In some previous posts I was inquiring about record set order, thanks everyone for the help it fixed the problem. With that step out of the way I am having an problem with the next step.

So I search the DB for John Doe, and I get three results and have them listed in a Listbox:
Expand|Select|Wrap|Line Numbers
  1. ID            First Name           Last Name
  2. 4             John                    Doe
  3. 23           John                    Doe
  4. 58           John                    Doe
Now I want to click an entry and have that John Doe's record show up.

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstSearchResults_Click()
  2.     Dim dbs As DAO.Database
  3.     Dim qdf As DAO.QueryDef
  4.     Dim ci As String
  5.     Dim ctl As Control
  6.  
  7.     Set dbs = CurrentDb
  8.  
  9.     Set ctl = lstSearchResults
  10.     'ci = ctl.ItemsSelected.Item(1)
  11.  
  12.     lstSearchResults.AddItem (ci)
  13.  
  14.     'Dim strSQL As String
  15.     'strSQL = "Select * FROM Request WHERE Customer_Order_Number = " & lstSearchResults.ItemsSelected.Item(1) 'lstSearchResults.ItemData(1)
  16.     'Set qdf = dbs.CreateQueryDef("GetRequest", strSQL)
  17.  
  18.     DoCmd.OpenForm ("Current_Customer_Data")
  19. End Sub
My code has a lot of comments because I have been playing around. It "worked" when I used lstSearchResults.ItemData.Item(1). But this only retrieved the first item from the list. I have, as you can see from my code tried to use lstSearchResults.ItemSelected but the rest is still gray for me.

Anybody follow and can help? Please let me know.

Thank you.
Mar 9 '07 #1
Share this Question
Share on Google+
19 Replies


Rabbit
Expert Mod 10K+
P: 12,347
Assuming that ID is the first column in your listbox.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Current_Customer_Data",,,"ID = " & Me.lstSearchResults.Column(0)
Mar 9 '07 #2

P: 31
Cool.

Is Me a reserved word or should I replace that with my Form name? Because when I put my form name in it, it does not work, but when I leave Me in it kinda works.

It still opens the record to the first John Doe on the list, even if I click, highlight, and select the third one?
Mar 9 '07 #3

Rabbit
Expert Mod 10K+
P: 12,347
Cool.

Is Me a reserved word or should I replace that with my Form name? Because when I put my form name in it, it does not work, but when I leave Me in it kinda works.

It still opens the record to the first John Doe on the list, even if I click, highlight, and select the third one?
Yes Me is a reserved word pointing to the form that called the event.

It should open correctly if the Where criteria is set up correctly. Check the field names and control names and position in the listbox to make sure everything is referencing correctly.
Mar 9 '07 #4

NeoPa
Expert Mod 15k+
P: 31,345
It still opens the record to the first John Doe on the list, even if I click, highlight, and select the third one?
Post in the Row Source for your ListBox and we can check it over for you.
If you include the exact (pasted) code for opening the form too we'll check that as well.
Mar 10 '07 #5

P: 31
Expand|Select|Wrap|Line Numbers
  1.     If ((FirstName <> "") And (LastName <> "")) Then
  2.          Set rs = dbs.OpenRecordset("Select * FROM Caller WHERE C_F_Name like '" & FirstName & "*' and C_L_Name like '" & LastName & "*'")
  3.     End If
  4.  
  5.     If (((FirstName = "") And (LastName = "")) Or ((FirstName = "") And (LastName <> "")) Or ((FirstName <> "") And (LastName = ""))) Then
  6.         lblerror1.Visible = True
  7.         lblError2.Visible = False
  8.     Else
  9.         If (rs.EOF) Then
  10.             lblerror1.Visible = False
  11.             lblError2.Visible = True
  12.             Call_ID = ""
  13.             Telephone = ""
  14.             Email = ""
  15.             Dist_ID = ""
  16.             'lstSearchResults.RemoveItem (1)
  17.             check1 = False
  18.  
  19.         Else
  20.             lblerror1.Visible = False
  21.             lblError2.Visible = False
  22.  
  23.         lstSearchResults.RowSource = "Value List"
  24.         lstSearchResults.ColumnCount = 4
  25.         lstSearchResults.RowSource = "Customer ID; First Name; Last Name; Distributor"
  26.  
  27.         While (Not rs.EOF)
  28.             Dim row As String
  29.             row = rs(0) & ";" & rs(1) & ";" & rs(2) & ";" & rs(5)
  30.             lstSearchResults.AddItem (row)
  31.             rs.MoveNext
  32.         Wend
  33.         End If
  34.     End If
  35. End Sub
  36.  
  37. Private Sub lstSearchResults_Click()
  38.     Dim dbs As DAO.Database
  39.     Dim qdf As DAO.QueryDef
  40.     Dim ci As String
  41.     Dim ctl As Control
  42.  
  43.     Set dbs = CurrentDb
  44.  
  45.     DoCmd.OpenForm "Current_Customer_Data", , , "ID = " & Me.lstSearchResults.Column(0)
  46.  
  47. End Sub

Here is the code you request for me. It starts when I enter the data needed to be searched for.

Thanks.
Mar 15 '07 #6

NeoPa
Expert Mod 15k+
P: 31,345
Post in the Row Source for your ListBox and we can check it over for you.
If you include the exact (pasted) code for opening the form too we'll check that as well.
The most important thing we need is the Row Source of your ListBox. Without that we can't even start on the rest.
Mar 15 '07 #7

P: 31
Ok, my green nature is showing here, what is it you mean by row source? I thought u were looking for the code:
Expand|Select|Wrap|Line Numbers
  1.  lstSearchResults.RowSource = "Value List"
I'm sorry about that.
Mar 15 '07 #8

Rabbit
Expert Mod 10K+
P: 12,347
You have to set RowSourceType to Value List not RowSource.
Mar 15 '07 #9

NeoPa
Expert Mod 15k+
P: 31,345
Ok, my green nature is showing here, what is it you mean by row source? I thought u were looking for the code:
Expand|Select|Wrap|Line Numbers
  1.  lstSearchResults.RowSource = "Value List"
I'm sorry about that.
Not a problem :)
Just go to the form in Design View and copy the value in the Row Source property of your control (probably lstSearchResults).
That is, unless you set it somewhere in the code, and we're talking after it's been changed. In that case, the whole procedure it's changed in may prove useful.
Mar 16 '07 #10

P: 31
Ok, so if I preformed the task correctly the Row Source is: Customer ID;First Name;Last Name;Distributor. This is because the top row of my list box acts as a header for the list box.

Thanks
Mar 16 '07 #11

NeoPa
Expert Mod 15k+
P: 31,345
But, surely, that would leave you with just a header line and no data lines :confused:
Mar 16 '07 #12

P: 31
You are right to be puzzled, but you are indeed correct. When I open the Form, which main function is to search for clients, The list box only has the header. I then input a first name and last name into there respective boxes and click a "Find Caller" button. This button runs a query to search th db for a match, then lists matches. The user will then select the proper person they are looking for.

Does this help your understanding any better? Please let me know, this forum has been really helpful.

Thanks
Mar 16 '07 #13

NeoPa
Expert Mod 15k+
P: 31,345
It certainly does :)
If you take another quick look at post #10 though, you'll see the last paragraph anticipates this scenario, and asks for the code that populates the control if it is done dynamically.
As this is the case, I need that from you now (The code of the (whole) procedure that sets up the RowSource of the control).
If you can do that for me I'm sure we can make some further progress.
Mar 16 '07 #14

P: 31
Ok, here is the code that defines my db and rs as well as the list box.

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database
  2. Dim rs As Recordset
  3. Dim rs2 As Recordset
  4. Dim rs3 As Recordset
  5.  
  6. Dim qdf As DAO.QueryDef
  7. Dim strSQL As String
  8.  
  9.  
  10. Set dbs = CurrentDb
  11.  
  12. lblerror1.Visible = False
  13. lblError2.Visible = False
  14.  
  15.  
  16.     If ((FirstName <> "") And (LastName <> "")) Then
  17.          Set rs = dbs.OpenRecordset("Select * FROM Caller WHERE C_F_Name like '" & FirstName & "*' and C_L_Name like '" & LastName & "*'")
  18.     End If
  19.  
  20.     If (((FirstName = "") And (LastName = "")) Or ((FirstName = "") And (LastName <> "")) Or ((FirstName <> "") And (LastName = ""))) Then
  21.         lblerror1.Visible = True
  22.         lblError2.Visible = False
  23.     Else
  24.         If (rs.EOF) Then
  25.             lblerror1.Visible = False
  26.             lblError2.Visible = True
  27.             Call_ID = ""
  28.             Telephone = ""
  29.             Email = ""
  30.             Dist_ID = ""
  31.             'lstSearchResults.RemoveItem (1)
  32.             check1 = False
  33.  
  34.         Else
  35.             lblerror1.Visible = False
  36.             lblError2.Visible = False
  37.  
  38.         lstSearchResults.RowSource = "Value List"
  39.         lstSearchResults.ColumnCount = 4
  40.         lstSearchResults.RowSource = "Customer ID; First Name; Last Name; Distributor"
  41.  
  42.         While (Not rs.EOF)
  43.             Dim row As String
  44.             row = rs(0) & ";" & rs(1) & ";" & rs(2) & ";" & rs(5)
  45.             lstSearchResults.AddItem (row)
  46.             rs.MoveNext
  47.         Wend
  48.         End If
  49.     End If
It is very similar to my post, I think it was #4. I hope this helps.
Mar 16 '07 #15

Denburt
Expert 100+
P: 1,356
I know I am busting in here but I think you need to look at the following line and make sure the "ID = " is named the same as the field in the database, from the appearance of things it might be [Customer ID]

DoCmd.OpenForm "Current_Customer_Data", , , "[Customer ID]
= " & Me.lstSearchResults.Column(0)


See where I am going this might just resolve your issue.
Mar 16 '07 #16

NeoPa
Expert Mod 15k+
P: 31,345
If that doesn't solve your problem (It probably will), look at the data type of the [whatever ID] field. If it is not numeric then we'll need to look again.
A further couple of points which you can use or ignore...
  1. Row Source Type (As Rabbit said earlier) should replace Row Source in your code that sets it to 'Value List'.
  2. You should maybe consider using a Table/Query type anyway.
    There is an option to show 'Column Heads' and it would mean you could lose a whole chunk of clumsy code.
Just some thoughts.
Mar 16 '07 #17

P: 31
Thank you both, it works!! I really appreciate it. I don't doubt this will be the last post, but once again thank you.
Mar 23 '07 #18

NeoPa
Expert Mod 15k+
P: 31,345
No problem. Pleased to be of use :)
Mar 23 '07 #19

Denburt
Expert 100+
P: 1,356
Glad to know your good to go... :)
Mar 23 '07 #20

Post your reply

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