Required if visible

[goaksmith]

I have an option group that is visible when the word "X" is in another combo box. This works fine and is not a problem. But what I would like to do it make the option group required when it is visible and not required when it is not visible. I have searched other posts and they have come up with the idea to have a message box pop up when exiting the form or on the after update of the form to remind people that the option group is required. I did this and we are still having lots of poeple not select the group. Is is not possible to do this and am I going to have to stick with the message box or is there someway to make the option group conditionally visible. I have Access 97. Thanks so much for your help in advance.

Gretchen

 

[Mile-O]

Have a look at the Form's Unload event.

Here you can check if the textbox says 'X' and if so perform your business by informing the user they've neglected the option group and then using the Unload event's Cancel argument to stop the form from unloading.

Only let it unload if the option group has a value or the value is not required.

 

[goaksmith]

That is a great idea...Thanks! I will give that a try.

 

[goaksmith]

I used the code below and it works like a charm. It doesn't make the field required, but hopefully they will select no and chose one since it prompts them. Thanks so much.


Private Sub Form_Unload(Cancel As Integer)
If Me.cbFund = "Catholic Funds" Then
If MsgBox("Did you answer the Catholic profile question?", vbYesNo) = vbYes Then
Exit Sub
Else
Cancel = True
End If
End If
End Sub

 

[Pat Hartman]

You are using the wrong event. The record is already saved at the time that the unload event runs. You are trying to "shut the barn door after the cows got out", so to speak. All the user needs to do is to force close Access and the bad data is left in your db.

You MUST move the code to the BeforeUpdate event of the form so that you can prevent the bad record from being saved.

Private Sub Form_BeforeUpdate(Cancel As Integer) 
If Me.cbFund = "Catholic Funds" Then 
    IIf IsNull(SomeField) Then
        Cancel = True
        MsgBox ("The recordd cannot be saved until you answer the Catholic profile question."), vbOKOnly
    End If
End If 
End Sub

 

[goaksmith]

What you said makes sense. I deleted the old coding and added the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.cbFund = "Catholic Funds" Then
IIf IsNull(optGroupCatholic) Then
Cancel = True
MsgBox ("The record cannot be saved until you answer the Catholic profile question."), vbOKOnly
End If
End If
End Sub

And I get the error message:

Complie Error
Expected: end of statement


The field that I am checking to see if it is null is an option group. Any suggestions on how to fix this?

 

[WayneRyan]

goaksmith,

Your Iif should be an If.

Wayne

 

[goaksmith]

Ok, I changed the IIF to IF and the compile error, but when I left the option group blank I got the error

"You can't save the record at this time"
Microsoft access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?

But when I select a member of the option group it gave me the message.

"The record cannot be saved until you answer the Catholic profile question."

So, I changed my code to look like this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.cbFund = "Catholic Funds" Then
If IsNull(optGroupCatholic) Then
Cancel = True
MsgBox ("The record cannot be saved until you answer the Catholic profile question."), vbOKOnly
Else
Cancel = False
End If
End If
End Sub

Now what it is doing is that when I do not select the option group it comes up with the message in the message box. But then it prompts me with the "you cannot save your record at this time" error. Is there a way that I can prevent this error from coming up and just having them select OK and then the form reloads? I thought that was what the vbOKOnly did.

 

[Pat Hartman]

The code should work without the "cancel = false". There is some other problem causing the cannot save message. Do you have the option group field set as required? Is the option group bound to the correct column in the table?

 

[goaksmith]

I took out the Cancel=False portion of the code and it still works. So that is good.

But as far as the cannot save. I only get that message when I do not select the option group. When I select the option group it closes the form normally as it should. And I have changed nothing in the database except for deleting the OnUnload event and adding the BeforeUpdate event.