Access list boxes

  52MikeH 18:38 06 Nov 05
Locked

I have 2 linked list boxes (list0 and list2)which have the following Vb Code
Private Sub Form_Load()
With Me.List0
.RowSource = _
"SELECT DISTINCT SUPPLIER FROM Ranges " & _
"WHERE Supplier IS NOT NULL"
.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
.Requery
End With
Me.Label5.Caption = Me.List2.ListCount & " Ranges in " & _
Me.List0
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 ?

Thanks
Mike

  Chris the Ancien 20:08 06 Nov 05

Mike...

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.

CtA

  52MikeH 06:56 07 Nov 05

Thanks I'll give it a try.

  Crunchy 10:32 07 Nov 05

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 05

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 ?

Mike

  Crunchy 15:56 08 Nov 05

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 05

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.

Thanks

Mike

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

What is Google Allo? What is Google Duo? Google Allo UK release date and features: Google Allo is…

1995-2015: How technology has changed the world in 20 years

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…