Make My Code Work for MultiColumn LIstBox

Question

I found this code that will populate a listbox in an Access form with the results of a query to a database. I modified the code to do what I need. It works fine. However, there's three things I'd like to get some help. 

My listbox has four columns (First Name, Middle Name, Last Name, Address). This code only populates one column. Also this code does not put the headings in the box. I can fake the headings instead of getting them from the database, but..some help there would be appreciated. Then lastly, some times a field is NULL. I'd like to enter a blank for when the value is NULL.

Tackle one ore all or some combination of these issues, please. Here's my code (without declarations at the top and and closing ado objects at the end):

    

    cn.Open ConnectionString

    rs.Open "SELECT TOP 10 Client_First_Name, Client_Last_Name FROM TableName WHERE Client_Last_Name = 'smith'", cn, adOpenStatic

    rs.MoveFirst

    With Me.lstClientList
        .RowSourceType = "Value List"
        .RowSource = ""
        Do
            .AddItem rs![Client_First_Name]
            rs.MoveNext
        Loop Until rs.EOF
    End With

Answer 1

Hi Tod,

If you want to populate four columns in your Listbox, won't you need four fields from your query? Unless I am mistaken, I can only see two fields in your query: FirstName and LastName

Have you tried simply assigning the same query to the RowSource property of your Listbox?

If you do, you can then simply adjust the column numbers and then turn on the headings.

Hope it helps...

Answer 2

I would simply set RowSourceType to table/query, and set RowSource to the SQL statement.

Assuming the lastname is variable, the RowSource would be concatenated wherever the lastname is selected, and assigned to the property.

We would also use the Replace function to replace single-quotes with two single-quotes (think Patty O'Brien).

---

-Tom. Microsoft Access MVP

Answer 3

It would be far simpler to use the SQL statement as the control's RowSource property, as Tom says.  The only code you would need would be to requery the list box if 'smith' is in real life a parameter rather than a literal string expression, which I'd assume to be the case.

Is this list box being used to select a client or merely to display the list?  If the former, a serious omission from the query is the primary key column.  Personal names can legitimately be duplicated, and this is not uncommon, even in quite small groups of people.  Returning a person's address in addition to their name is therefore a good idea, but the query should also return the primary key, e.g. ClientID as its first column, which should be its BoundColumn and hidden by setting the first dimension of the control's ColumnWidths property to zero.

---

Ken Sheridan, Stafford, England

Answer 4

I'm going to try out the response posted so far and come back and mark them as answers if they work for me. In the meantime I found some advice regarding AddItem with multiple value in a row. It also addresses the NULL issue and headings problem, all in one whack.

            

strSQL = "SELECT Client_First_Name, Client_Middle_Name, Client_Last_Name, Client_Street_Address FROM TableName"

    Me.lstClientList.RowSource = ""
    Me.lstClientList.AddItem "First Name;Middle Name;Last Name;Address"

    Do Until rs.EOF

        With Me.lstClientList
            .RowSourceType = "Value List"
            .AddItem rs(0) & ";" & rs(1) & ";" & rs(2) & ";" & rs(3)
        End With
        rs.MoveNext
    Loop

 

Answer 5

Hi Tod. You certainly can do it that way if you want to. We just thought it would be easier/simpler to use a Table/Query than a Value List. Good luck with your project.

Answer 6

I have a UID for each name, but I don't display it. The listbox displays the results of a name search. If there's no result the user is prompted to create a new client. If there's more than one result the address helps the user decide to either select one of the results or create a new client. If a row is selected from the list the UID for that record is used to tie the client to the additional data entered by the user. 

I forgot to mention in my original post that this form is boundless. I'm not sure, but that may be why other solutions don't work for me. See my post at the bottom with the solution I went with.

Answer 7

If there's more than one result the address helps the user decide to either select one of the results................

That's the point I was making.  Unless you identify each client by a guaranteed distinct value in a (usually) hidden column which is the BoundColumn property of the list box you will not be able to select from two clients with the same name, which can't be ruled out.  Including the address visually distinguishes them in the list, but you won't be able to return a value for the selected client which identifies one or the other.  I was once present at a clinic when two female patients arrived within minutes of each other, both having exactly the same names and date of birth.  Without the distinct HospitalNumber value which each patient was given, although they could be visually distinguished in a list box or combo box by their address data, the system would not know which was selected as no column in the RowSource query could be used reliably as the BoundColumn to return a distinct value.  Names are completely unsuitable as keys.

For an example of how people of the same names can not only be visually distinguished by address in a combo but distinctly identified in the database take a look at NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, the opening form includes an unbound combo box to select a contact and open a bound form at that contact's record.  The BoundColumn property of the combo box is the hidden ContactID column which is the autonumber primary key of the Contacts table, and basis on which the bound form is filtered to the correct record.  Consequently it is possible for the code which opens the form to distinguish between two or more contacts of the same name.

As regards filling the list box, you are complicating things unnecessarily by using a Value List as the RowSourceType property.  Simply using the SQL statement as the RowSource property would be far simpler.

---

Ken Sheridan, Stafford, England