Oh no! Not another cascading question!

[GordonR]

Sorry everyone - it looks like that this has been done to death, all except for this question......
I have a combo-box (cb1) from which a user selects a value (row source = a query), When the user make a choice, the 'after update' event runs a macro that performs a 'requery' on another combo-box (cb2) to recalculate its list based on the new value. I then set the 'focus' to cb2, want to clear any previously selected value (which may no longer be available) and make the user reselect. How can I force the user to reselect? cb2 is bound to a column in a table and if I make it not 'required' and then set cb2 to null, even with 'limit to list' set the user is not forced to make a selection. I even tried setting the value to '(reselect)' which is never a valid value but the validation still doesnt occur.
I have a set of cascading combo-boxes so this is quite critical to me!
Really would appreciate some help!

 

[Mile-O]

On the After_Update event of the first combo box, requery the second (as you are doing) but also set it to Null.

Add some more code, for whatever purpose you have, to ensure a value is selected.

i.e.

If [b]IsNull([/b]cboMyCombo[b])[/b] Then

 

[GordonR]

Thanks for the reply!

Problem is that I can only set it to null if the bound column is 'required = no' which I am trying to avoid. I assume that this also means that I have to define a 'lost focus' event (and before save etc ?) to check it has a value (and one that is valid).

I guess really that I am trying to force revalidation of the box.

Any thoughts?

 

[GordonR]

Hmm......

I have tried changing the column in the table to be 'required = no' on the basis I will ensure its population using form control.
The 'after update' event of cb1 now requeries cb2, sets its value to null and then sets the focus to cb2 (using gotocontrol).
I then wrote a litlle bit of code that checks it for a null value, issues a message if so and sets the focus to it. This is then attached to the 'lost focus' event of cb2.
The problem is that the setfocus event gets ignored (as does a cancel event) for the lostfocus and so if the user doesnt select anything, the record can be saved as the column is now 'not required'. Therfore, I would need to add it to other events.
OK - time to review what I m trying to do! I want to force a user to enter a valid value from a combobox whose contents are based on another.
Thanks for your help in advance.

 

[SforSoftware]

Hi,

You can do this by putting this code into an standard module:

Public Function FieldRequired()
  If Nz(Screen.ActiveControl.Value, "") = "" Then
    MsgBox "You must enter a value!"
    DoCmd.CancelEvent
  End If
End Function

Now, in the properties of your comboboxes that are required you put in the event "On Exit" or "Lost focus": "=FieldRequired()"

Good luck,
Bert

 

[GordonR]

Thanks!