How to require user to select value from combo box?

[ncsthbell]

MS2007 - I have 2 combo boxes, 'cboDivisions' & 'cboGroups'. User selects the division from 'cboDivisions' but not all Divisions have groups associated. If there are no groups for the selected division, I set cboGroups invisible property. The problem I am having is my users are not always selecting a Group for a Division when there are some present. I want to force them to select a Group when the Divsion had them. I have put some code in the Form_BeforeUpdate event. The test for cbo_group.value works and the msbbox is displayed
Here is the code I am using:

If IsNull(cbo_group.value) or (cbo_group.value) = "" Then
   msgbox "Group is required"
   cbo_group.setfocus
   Exit sub
End If
]

I thought about putting the code on the exit of the cbo_groups event, however, if the user does not click in the group combo box, it won't work. Any suggestions?

[Seth Schrock]

You could use a before update event on the form to check if cbo_group is null and cancel if it is null. You would probably have to come up with another If/Then/Else check to see if cbo_group is supposed to have a value. You might be able to check if it is visible and if it is, then run the check to see if it is null.

[Rabbit]

Set the field as required in the table.

[ncsthbell]

Seth, I did put the code in the before update event on the form and I check to see if null. I do have an if/then/else test to see if the division has groups wrapped around the code I have above. How do I 'cancel'? Haven't used this before. I have always used 'exit sub'.

[ncsthbell]

Rabbit,
The divisions and groups are in two different tables and some groups can belong to multiple divisions so setting the field as required in the table does not work in this situation.
But thanks for the suggestion!

[TheSmileyCoder]

Before the Exit sub, you simply do:

Cancel=True

[ncsthbell]

I put the cancel=True in my code before the Exit Sub so my code looks like this:

If vGroupRqd = True Then 
  If IsNull(cbo_Group.Value)  Then
     MsgBox "Group is required for this Division.", , "Select Group from drop down list"
    [cbo_Group].SetFocus
     cancel = True
     Exit Sub
  End If
End If
 

The problem is now that it closes the form it is on and goes back to the main form. I start with 'Main Menu' (form) and from there I select 'Edit Data' (form). This is the form that has all info for the asset that has division/group assignments. This is the form I put the code on, I want to stay on this data edit form and have the 'focus' set on the group combo box. Not sure why it is closing the form completly.

[Seth Schrock]

What action is triggering the form's Before_Update event? Are you trying to close the form or just going on to the next record?

[ncsthbell]

On the form, I am selecting a Division from the combo box, I assume this is what is triggering the Before_update event.

[zmbd]

don't assume where the before_update event is being triggered from... double check as both the form and the control can have such an event.
-z

[Seth Schrock]

Good point z. The form's before_update event shouldn't trigger until the record is left, either by going to the next record or closing the form. Otherwise, there isn't anything in that code to close the form. You should look through your other events to see if some other event is triggering it to close.