Selecting Data from List Box

[UAlbanyMBA]

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:

ID            First Name           Last Name
4             John                    Doe
23           John                    Doe
58           John                    Doe

Now I want to click an entry and have that John Doe's record show up.

Private Sub lstSearchResults_Click()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim ci As String
    Dim ctl As Control
 
    Set dbs = CurrentDb
 
    Set ctl = lstSearchResults
    'ci = ctl.ItemsSelected.Item(1)
 
    lstSearchResults.AddItem (ci)
 
    'Dim strSQL As String
    'strSQL = "Select * FROM Request WHERE Customer_Order_Number = " & lstSearchResults.ItemsSelected.Item(1) 'lstSearchResults.ItemData(1)
    'Set qdf = dbs.CreateQueryDef("GetRequest", strSQL)
 
    DoCmd.OpenForm ("Current_Customer_Data")
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.

[Rabbit]

Assuming that ID is the first column in your listbox.

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

[UAlbanyMBA]

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?

[Rabbit]

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.

[NeoPa]

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.

[UAlbanyMBA]

    If ((FirstName <> "") And (LastName <> "")) Then
         Set rs = dbs.OpenRecordset("Select * FROM Caller WHERE C_F_Name like '" & FirstName & "*' and C_L_Name like '" & LastName & "*'")
    End If
 
    If (((FirstName = "") And (LastName = "")) Or ((FirstName = "") And (LastName <> "")) Or ((FirstName <> "") And (LastName = ""))) Then
        lblerror1.Visible = True
        lblError2.Visible = False
    Else
        If (rs.EOF) Then
            lblerror1.Visible = False
            lblError2.Visible = True
            Call_ID = ""
            Telephone = ""
            Email = ""
            Dist_ID = ""
            'lstSearchResults.RemoveItem (1)
            check1 = False
 
        Else
            lblerror1.Visible = False
            lblError2.Visible = False
 
        lstSearchResults.RowSource = "Value List"
        lstSearchResults.ColumnCount = 4
        lstSearchResults.RowSource = "Customer ID; First Name; Last Name; Distributor"
 
        While (Not rs.EOF)
            Dim row As String
            row = rs(0) & ";" & rs(1) & ";" & rs(2) & ";" & rs(5)
            lstSearchResults.AddItem (row)
            rs.MoveNext
        Wend
        End If
    End If
End Sub
 
Private Sub lstSearchResults_Click()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim ci As String
    Dim ctl As Control
 
    Set dbs = CurrentDb
 
    DoCmd.OpenForm "Current_Customer_Data", , , "ID = " & Me.lstSearchResults.Column(0)
 
End Sub

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

Thanks.

[NeoPa]

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.

[UAlbanyMBA]

Ok, my green nature is showing here, what is it you mean by row source? I thought u were looking for the code:

 lstSearchResults.RowSource = "Value List"

I'm sorry about that.

[Rabbit]

You have to set RowSourceType to Value List not RowSource.

[NeoPa]

Ok, my green nature is showing here, what is it you mean by row source? I thought u were looking for the code:

 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.

[UAlbanyMBA]

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

[NeoPa]

But, surely, that would leave you with just a header line and no data lines :confused:

[UAlbanyMBA]

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

[NeoPa]

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.

[UAlbanyMBA]

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

Dim dbs As DAO.Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
 
Dim qdf As DAO.QueryDef
Dim strSQL As String
 
 
Set dbs = CurrentDb
 
lblerror1.Visible = False
lblError2.Visible = False
 
 
    If ((FirstName <> "") And (LastName <> "")) Then
         Set rs = dbs.OpenRecordset("Select * FROM Caller WHERE C_F_Name like '" & FirstName & "*' and C_L_Name like '" & LastName & "*'")
    End If
 
    If (((FirstName = "") And (LastName = "")) Or ((FirstName = "") And (LastName <> "")) Or ((FirstName <> "") And (LastName = ""))) Then
        lblerror1.Visible = True
        lblError2.Visible = False
    Else
        If (rs.EOF) Then
            lblerror1.Visible = False
            lblError2.Visible = True
            Call_ID = ""
            Telephone = ""
            Email = ""
            Dist_ID = ""
            'lstSearchResults.RemoveItem (1)
            check1 = False
 
        Else
            lblerror1.Visible = False
            lblError2.Visible = False
 
        lstSearchResults.RowSource = "Value List"
        lstSearchResults.ColumnCount = 4
        lstSearchResults.RowSource = "Customer ID; First Name; Last Name; Distributor"
 
        While (Not rs.EOF)
            Dim row As String
            row = rs(0) & ";" & rs(1) & ";" & rs(2) & ";" & rs(5)
            lstSearchResults.AddItem (row)
            rs.MoveNext
        Wend
        End If
    End If

It is very similar to my post, I think it was #4. I hope this helps.

[Denburt]

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.

[NeoPa]

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.

[UAlbanyMBA]

Thank you both, it works!! I really appreciate it. I don't doubt this will be the last post, but once again thank you.

[NeoPa]

No problem. Pleased to be of use :)

[Denburt]

Glad to know your good to go... :)