Manipulating user options of multiple checkboxes

[thebatfink]

Hi, I have a form for creating new records in a table which contains 2 individual check boxes (which store their boolean values in fields in the same table). What I want to do is allow the following conditions -

True, True
True, False
False, True

but prevent -

False, False

Is there anyway to do this?

Thanks!

 

[pr2-eugin]

On the before update event of the Form, you have to just use a simple Check.. if both the check boxes are =0 then cancel the update..

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Check32 = 0 And Check34 = 0 Then
    Call Msgbox("The values you entered are not acceptable, please check the checkboxes", vbCritical) 
    Cancel = True
End If
End Sub

 

[thebatfink]

Ahh I didn't think of using the update event

Could you tell me what I should be using to suppress the update cancelled prompt? DoCmd.Setwarnings False doesn't seem to work..

Thanks!

 

[pr2-eugin]

Well you can use On Error GoTo staement and trap the error by giving a gentle reminder... The edited code would be..

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo errCatch:
If Check32 = 0 And Check34 = 0 Then
    Cancel = True
End If
Exit Sub
errCatch:
    Call Msgbox("The values you entered are not acceptable, please check the checkboxes", vbCritical) 
    Exit Sub
End Sub

 

[thebatfink]

Hey, thanks for the update.

I did however attempt that prior to posting. Access still prompts that the run command was canceled 'after' my msgbox..

 

[pr2-eugin]

Could you also post the exact error with error number please?
EDIT: Also what are you trying to do before/after this?? Have you used DoCmd anywhere??

 

[thebatfink]

Ahh hang on, yes.. The form has a button to save the record with this code..

Private Sub btnsave_Click()
On Error GoTo Err
DoCmd.RunCommand acCmdSaveRecord
Exit Sub
Err:
MsgBox Err.Description
End Sub
[code]
 
So I guess the update event is being triggered by the acCmdSaveRecord. How does that affect things?
 
Its not erroring, its just Access internal message prompt to say the RunCommand was canceled. Seeing as I am providing the user a response from the system with my custom message boxes, I don't want Access to repeat that response with its own prompt (which is less verbose for a general user operating the database).
 
Thanks!
 
EDIT: Is there any way in which a record could be saved other than with the command button and the above code? If not then I will obviously put the logic check on the checkboxes in this sub. I would like to make it water tight though.

 

[pr2-eugin]

Access normally saves the record when you move from one record to another, so there is actually no need of using a save button unless you have explicitly taken off the navigation buttons..

When you use DoCmd to make a Save your record, that will trigger the Before_Update but since we cancelled it; the message appears again, in other words the error handler has not been instructed to let go and move on.. so you have to use resume..

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo errCatch:
If Check32 = 0 And Check34 = 0 Then
    Cancel = True
End If
exitLbl:
Exit Sub
errCatch:
    Call Msgbox("The values you entered are not acceptable, please check the checkboxes", vbCritical) 
    Resume exitLbl:
End Sub

I guess the above should work..

 

[thebatfink]

Yeah don't navigate through records. We have seperate Edit, New record and Search forms. I'll have a play with it Monday. Thanks for your help sir.