'related@ Combo boxes and Not In List advice needed

[Paul Cooke]

Hi guys,

could someome please advise me if this is possible?

I have 2 combo boxes on a form - each with thier own table (for simplicty called Box1 and Box 2).

So if box1 has nothing in it they must put something in box2 or
if box2 has nothing in it they must put something in box1

Bsically the user has to put something in either both of them or at least one of them.

The other issue is both these boxes are limited to the list so the not in list event needs to be triggered as well. (I'm assuming if nothing is put in box1 (or box2) the not in list event won't come into play anyway?)

I would be grateful for any adivce you can offer on how to do this?

many thanks

Paul

 

[JamesMcS]

You could probably do an 'if' statement in the on current event of the form. When you say 'has nothing in it', what do you mean? That that particular record hasn't got a value in the field the combo is bound to? Or that there's nothing to select in the box?

 

[John Big Booty]

Paul you are correct you Not In List event will only fire if the user attempts to enter a value in the the combo that in not in the list.

Now to ensure that your user makes a selection from on or both of your combos I would put a test in the Form's Before Update event along the lines of;

If Me.Box1 = 0 And Me.Box2 = 0 Then [COLOR="SeaGreen"]'Assumes the default value for this control is zero (0)[/COLOR]
        MsgBox "Please make a selection from on of the combos"
        Cancel = True
End If

 

[Paul Cooke]

hi James thanks for the reply - sorry I should of been clearer in my explantion..

so...

Box1 is a combo sourced to Table1
Box2 is a combo sourced to Table2

Each table has loads of different names in them.

On the form:

The user tabs into Box1 but does not select a name from the list
then
Tabs into box2 again does not select an item but trys to tab to the next field.

I need to know if it is possible to 'force' the user to either pick a name from one of the Boxes or both of them, but they cannot leave both of them empty.

So if they leave Box1 empty they must select and item from Box2's list

I think in hindsight as Box2 comes after Box1 in the flow of the form I only need to look at coding box1

again in hindsight the Notinlist event may not be an issue here as it is simply dependant on each control???

Hope that now makes sense?

Thnaks again

 

[vbaInet]

Following John's advice, a variation would be:

If len(Me.Control1 & Me.Control2 & vbNullString) = 0 then
     cancel = true
     MsgBox "Please make a selection from on of the combos"
end if

It might also be worth including the ListIndex of the combo boxes in the criteria if you're going to be programmatically changing the Selected() and/or Value property.

 

[Paul Cooke]

Many thanks John & VBaInet

I think I am complicated things in my head as usual ! lol

Just for information the user will not be allowed to add to the list so I am assuming the listIndex bit does not really matter or this?


Thank you again

 

[vbaInet]

So you can also do:

if me.combobox1.listindex + me.combobox2.listindex < 0 then
...

 

[Paul Cooke]

Thanks again for all the help

 

[gemma-the-husky]

you could have an option group - and depending which radio button they pick - then enable/disable (or even hide/show) the combo boxes - and clear down the inactive one.

It may make for an easier interface, also - eg don't the users ever try and use both combo boxes?

 

[Paul Cooke]

Thanks for the tip Dave - this has been sorted now with the earlier posts - the option button hide, ect is not really applicable to this as the user could put information in both fields if they had it. I just needed to insue information was entered into at least 1 field as a minimum.

Thanks again Tho