Two questions on drop down boxes

[martinbanks]

I need to get to the bottom of 2 problems that both are related to drop down boxes.



1. Can the drop down list be wider than the box? I have the box at a width of 4.7cm and this can't increase, but when the user drops down the list it would be much better if they could see the full length of the options. I have the column widths set at 0cm;8cm but it still only drops down 4.7cm wide.


2. How do you get an event to fire when the drop down arrow is clicked (ie the box is 'dropped down'). There are a series of product drop downs that do a select query via vba and at present it is set in the Got Focus event but that means that if the user unknowingly (and these users do lots of stuff unknowingly!) tabs past or click in the box it resets back to the first option of the select query. If it could just run the vba event when the arrow is click that would be magnificient.

[martinbanks]

An example of the event that needs to fire on 'dropping down' the box:
Private Sub cboProductSelectorOrders2_GotFocus()
Me.cboProductSelectorOrders2.RowSource = "SELECT ProductID, ProductName FROM" & _
" tblProducts WHERE CustomerID = " & _
Me.cboCustomerSelectorOrders & _
" ORDER BY ProductName"

Me.cboProductSelectorOrders2 = Me.cboProductSelectorOrders2.ItemData(0)
End Sub

Another possible solution to this, though not as ideal, would be if the list could have a blank row appear as the first option. That way if they unknowingly tab past or click in a box they aren't working on then it will go empty and that will be more obvious to them they've changed a value they didn't mean to.

[ajetrumpet]

martin,

first of all, widths are listed in inches, not centimeters. and there are two in the property sheet: WIDTH, and COLUMN WIDTH. first is the control's display box, second is the dropdown list's widths. in terms of vertical length of the list, I don't think that can change. You'll have to scroll. (almost sure on that).

but as far as an event on the actual arrow, why? There's never a need. That's getting close to the point of perfection, and there are plenty of tools to use besides burdening yourself with requiring something like that. Besides, it's not possible without heavy manipulation.

and again, an sql should be given to the box on a control event outside of the box. why? because it makes no difference whether the box gets values then or when it gets focus. make sense?

if you want to alert users that they did something wrong, use message boxes. those are your best bet for getting their attention. It's certainly better than a blank line at the top of a list.

[martinbanks]

martin,

first of all, widths are listed in inches, not centimeters.

That must depend on your windows settings then because mine are in centimetres.

and there are two in the property sheet: WIDTH, and COLUMN WIDTH. first is the control's display box, second is the dropdown list's widths. in terms of vertical length of the list, I don't think that can change. You'll have to scroll. (almost sure on that).

I have the Width at 4.7cm and the Columns Widths at 0cm;8cm but the list still drops down at just 4.7cm. I have no desire to change the vertical length but this can be done by changing the List Rows property. It defaults at 16 but if you change it to say 1 it will show just one option and have scroll buttons. In checking that I just found the solution to problem 1. There is a property for List Width, which defaults at Auto (seemingly therefore the width of the box). I changed this to 8cm and bingo. Problem 1 solved. Sweet. Onto problem 2, which doesn't seem as simple:

but as far as an event on the actual arrow, why? There's never a need.

and again, an sql should be given to the box on a control event outside of the box. why? because it makes no difference whether the box gets values then or when it gets focus. make sense?

The reason why I need to do this is because the same sql is applied to 9 boxes (ie the form allows 9 products to be ordered) but all 9 boxes won't usually be used. Most orders will just be for 2 or 3 different products, so having the vba in the after update of the customer selection box (the vba then narrows the product selection boxes down to just that customer's products) makes it put the first product in all 9 boxes. Do you now understand why there's a need?

if you want to alert users that they did something wrong, use message boxes. those are your best bet for getting their attention. It's certainly better than a blank line at the top of a list.

But, as has been highlighted in usage, the staff are sometimes tabbing past or clicking in a product box that they had already set and in doing so (with my event that I mentioned wasn't applicable to the after update event of another box) that product resets to the first product in their list. So that is why I need it to be if they drop the box down.

As for using message boxes, it is possible but it would need to be able to know that they had already set a product and have now reset it to the first in the list. So I hoped there was a way to switch the event as mentioned as the more preferable solution.