Access list boxes

  52MikeH 18:38 06 Nov 2005

I have 2 linked list boxes (list0 and list2)which have the following Vb Code
Private Sub Form_Load()
With Me.List0
.RowSource = _
.ColumnCount = 2
.BoundColumn = 1
.ColumnWidths = "0.5 IN;1.2 IN"

End With
End Sub

Private Sub List0_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT RangeName FROM Ranges " & _
"WHERE Supplier = " & Me.List0
End With
Me.Label5.Caption = Me.List2.ListCount & " Ranges in " & _
End Sub

When list box loads the list only shows the ID key for the field and not the text!
Now if I look at the table where the data is stored the Supplier field(which is a combo box based on another table)show text for the suppliers name.
Does anyone know how I can get the supplier text into the list box ?


  Chris the Ancien 20:08 06 Nov 2005


I can't offer an answer, but I know a place where you might get help.

There's a useful forum at click here that has some real whiz-kids in Access.

The forum is free - but you need to register - and there are (usually) quite rapid responses to questions.

Worth a try, anyway.


  52MikeH 06:56 07 Nov 2005

Thanks I'll give it a try.

  Crunchy 10:32 07 Nov 2005

In the row source of your combo box add your supplier table linking with the SupplierID then add your supplier name to the fields list. Increase your List Width and Column Count. Add another entry in Column Width.

Hope this helps.

  52MikeH 21:13 07 Nov 2005

Hi Crunchy,
I have tried this but I either get the type mismatch or the list box stops working !
To explain a little further what I am trying to do.
I have a table 'Suppliers' which has the following fields:-Supplier,street,Town,postcode,TelNo
I have a table 'Ranges' which has the following fiels:- RangeName,RollPrice,RetailPrice,Widths,Supplier(which is a combo box sourcing data from the Table Suppliers)
Both tables look okay and all data is fine.
I have created a Form with 3 list boxes,on opening the form it loads the first list box with all the 'Distinct' Supplier names (or it should)!
Then when one of the supplier list are selected the second list box loads with all the RangeName details of that supplier.
Going back to the original posting if in the first part of the code I have "SELECT DISTINCT Supplier FROM Suppliers" then the first list box loads okay and the names are visible,but the second list does not load when on of the Supplier are selected.
Does this make any sense ?


  Crunchy 15:56 08 Nov 2005

The reason it won't work is because whilst you see text in Supplier in your file Ranges it is actually storing the SupplierID. The ComboBox is causing the confusion.

You may want to approach this in a different way. Look at this tutorial on combobox linking click here

All the code is then in the comboboxes on the form. List0 gets Supplier and SupplierID from your file Suppliers and use after Update to pass the SupplierID to an ordinary text box, which you can hide later.

The code would be Me.TextBox1 = Me.List0.Column(1) Also put in the requery statement Me.List2.Requery

List2 would select from your file Ranges and use TextBox1 as the criteria.

Look at the tutorial first and I hope all will make sense.

  52MikeH 07:02 09 Nov 2005

Thanks Crunchy,
Tried but still could not get the Supplier name into the list box, even when i had it on the form in a Text box.
So I have had to change the Combo in Ranges table to a text box and text box in suppliers table to a como,I can now use all the fields from Ranges table and the List boxes work fine.



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires