2 lisboxes 1

[djeeten]

Hi,

probably a stupid question but: I have 2 lisboxes on this form. One is based on the table tblSuppliers and has 2 columns: SupplierId and SupplierName. Only SupplierName is visible.
The second lisbox is based on the table tblProducts and has 4 columns: ProductId, ProductName, SupplierId, Price.

Now I only want to make sure that when I choose a certain product in the second listbox, the supplier gets selected in the first listbox. I(m not sure which property I should use here and how.

Thanks in advance,

dj.

 

[danvlas]

I will assume the multiselect property of the Suppliers listbox is not enabled (only one item can be selected at a time)

Private Sub SecondListBoxName_AfterUpdate()
SuppliersListBoxName = SecondListBox.Column(2)
End Sub

Column numbering start at 0, your SupplierID is in the 3-rd position, so it has the index of 3-1 = 2

Good luck





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[GHolden]

You could limit the supplier listbox to only show the relevant supplier..

Private Sub lstProduct_Click()
lstSupplier.RowSource = "SELECT SupplierID,Supplier " & _
"FROM tblSupplier " & _
"WHERE SupplierID = " & lstProduct.Column(2)


End Sub

There are two ways to write error-free programs; only the third one works.

 

[djeeten]

Thanks danvlas. Silly me...

GHolden, it's not a good idea to change the recordsource when choosing a product from the second list, as the user should be able to choose every supplier at all times. I am changing the recordsource for the products list when the user chooses a certain supplier from the first list though. Thanks anyway.

Hi danvlas, thanks again. I would like to give you a star, but this question was not challenging enough I assume. Therefore I would like to ask you another (again not that difficult) question.

Besides the two listboxes there's also a subform on the form. When a product is choosen from the second listbox (with products), all customers that have already ordered this product are shown in the subform, together with the total amount.

On the main form, there's a field txtTotal. On the subform there's another total field (value = sum of the total amounts of products of all customers). The total field on the main form then just takes the value of the total field on the main form.

Now, when there are no records shown in the subform, I would like to set the visible property of txtTotal on the main form to False (as an (harmless) error is shown in the textbox). I tried this in the click-event of the Product_List:

If IsEmpty(Forms!frmSupplier!sfrmSupplier.Form.RecordSource) Then
Me!txtTotal.Visible = False
Else
Me!txtTotal.Visible = True
End If

What am I doing wrong?

Thanks in advance,

dj.

 

[danvlas]

You refer to the RecordSource of the subform, which is a string and is never empty. It can be however, a zero-length string ("&quot


Normally, the sum field should be empty in the subform, therefore also empty in the main form. However, you can use the IsError function to test the result of the expression:

txtTotal.Visible = Not IsError(sfrmSupplier.Form("TotalTextBox&quot)

Or, you could display a custom message setting the control source to:
=IIf(IsError(sfrmSupplier.Form("TotalTextBox&quot),"No records", sfrmSupplier.Form("TotalTextBox&quot)


That should do the trick...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html