Making it required that one of the option buttons in a group be selected before saving.

[frobeniustaco]

Hello access gurus! I have a question.

I'm working with a pre-existing access database (Windows 10, office 365). There are several grouped option buttons on a form that update a record in a table.

I want to force the user to have to choose one of these options before saving the record. Ideally if the user did not choose one of these, access spits out a message box prompting the user to pick one.

Example:

LetterType:
A


B
C
D
E

Using the example, my thought was somewhere in the 'On Click' procedure for the save button, I tell it to create my message box if the frame containing the "Letter Type" options reads null.

Psudeo-Code Brainstorm:

If Frame1.Value=Null
MsgBox "Please Choose a LetterType"
End If

I feel like I'm on the right track here, but I'm certain there's more that I need, like something to break the save process if the message box pops up. Also probably a little syntax help.

I've been out-of-industry for a year so while I'm at least reasonably knowledgeable (I think :P) I am a bit rusty.

Any help you folks could provide would be appreciated!

[June7]

Cannot test if anything is = Null. Use Is Null in queries and IsNull() in VBA. Review http://allenbrowne.com/casu-12.html

If IsNull(Frame1) Then MsgBox "Please Choose a LetterType"

Data validation is usually in form BeforeUpdate event. If you want to use a button to control this then need to prevent other methods of closing the form (X close, File>Exit, switching to design view on ribbon, right click shortcut menu) and/or set form as modal.

Or set the field as Required in table and let Access nag users.

Or use ValidationRule and ValidationText properties.

[Dave14867]

I have a similar situation on a form and used the method that june7 is describing but then in the same sub set the focus back to the form control that needed to be selected.

Dave

[Missinglinq]

Here's an example putting all of these bits together:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
 If IsNull(Me.YourOptionGroupName) Then
  Cancel = True
  YourOptionGroupName.SetFocus
  MsgBox "A Letter Option Must Be Selected!"
  Exit Sub
 End If

End Sub

Note that the Command

Exit Sub

is only needed if you need to validate more than one Control.

You can leave your 'Save' button's code as it originally was. The reason not to use the OnClick event of your 'Save' button is that you cannot Cancel the Save from an OnClick event. Also note June7's advice on preventing other methods of closing the Form if you only want saving the Record being done using your button.

Linq ;0)>

[frobeniustaco]

Thanks folks! This is helpful. Although, I've since I made this post discovered that my validation has to be a little more complicated. It only makes sense to require these buttons to be filled if particular products are entered on a subform within this form.

My form has a few different tabs. The radio group is on tab 2, the subform is on tab one, and has a table where you can enter a product (via a dropdown when you click in that field) along with quantity. I would need to somehow detect if the user has entered data from a number of products for which "letter type" would be relevant, and make the radio buttons required only then.

[June7]

This does get complicated. Is the main form bound to data and subform has dependent records? If yes, the main form record is committed before dependent records in the subform are entered. So you don't know before the main form record is committed whether or not the radio buttons must be selected.

Where is the Save button - on the main form?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Dave14867]

I have a similar situation in a database I am working on now. When I hit the save button, it looks at the contents of a field and based on those contents, a specific cmd button becomes visible, that cmd button runs a specific append query. This is all on 1 form but I see no reason why being on a tabbed form would be any different.

Like June7 said, if you would like to provide a copy for us to assist with, follow his instructions and we'll take a look. It's much easier if we can see the actual file.

Thanks

Dave

[Peter M]

For what it is worth, in the past I have setup different data validation functions (instead of subs) to validate form entries. This way you can call them at different times based on different criteria. e.g.

Private Function validate_Tab2(optional Var_ToDoSomethingDifferent) as Boolean
'Use Var_ToDoSomethingDifferent to skip steps etc.

'criteria and fields to check
'code to go through things
'if everything passes then
validate_Tab2=TRUE
end function

And then in the Form Subs

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
if validate_Tab2 then
 'Tab2 validation passes
else
'Tab2 validation fails
 msgbox("Tab 2 validation fails!")
 Cancel = True
end if

If IsNull(Me.YourOptionGroupName) Then
  Cancel = True
  YourOptionGroupName.SetFocus
  MsgBox "A Letter Option Must Be Selected!"
 End If

exit sub

End Sub