By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,057 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,057 IT Pros & Developers. It's quick & easy.

Disabling fields for one record only/ validation rule with If statement

P: 4

Here is my question.

I've got several check boxes on my form (yes/no questions for users). And one check box that says "None of the above".
If the latter is ticked: 1) all other check boxes become disabled, and 2) Access checks if any of them were ticked and if it's true alerts user about the error.

For #1 I wrote a code in After Update event to disable all other boxes, but it disables them for all records. How can I do it only for current record.
For #2 I also wrote a code, but I'm wondering if it can be done using validation rule of the control. Is it possible to write an expression for validation rule to accomplish the task.

Would appreciate any suggestions!
Apr 8 '12 #1
Share this Question
Share on Google+
7 Replies

P: 759
For #1: No way (as far as I know)

For #2: I don't think so. Again as far as I know, the validation rule can't be a function. Here I am not so sure, sorry.
Apr 8 '12 #2

Expert Mod 10K+
P: 12,366
For 1, use the On Current event. If fires whenever the record changes. Use it to enable/disable appropriately.

For 2, it may be possible. Try it and see. I don't have Access on this computer to confirm.
Apr 8 '12 #3

P: 4
There is no On Current event for check box control, only for the Form in general. In this case for the action to take place I need to either go to next record or push Refresh All. But I would like to have a check box that disables some of the fields while user can continue to work with other fields for this record.
What I'm thinking is - I can use On Current event of my form, and use the check box to run Requery command, but this command takes me back to first record.
Apr 9 '12 #4

Expert Mod 10K+
P: 12,366
You don't need an On Current event for the check box. You need to use the one on the form. Use it to check the value of the checkbox and enable/disable as appropriate.

I'm not sure why you would use the Requery command. That doesn't disable or enable anything.
Apr 9 '12 #5

P: 4
"You need to use the one on the form. Use it to check the value of the checkbox and enable/disable as appropriate."
Yes, I've tried it as you suggested. But It doesn't disable right after I tick the check box, only when I go to the next record or Refresh All.
Apr 9 '12 #6

P: 759
From what I read until now, your goal is not to enable/disable the checkboxes. Your goal is to deny to be checked the checkbox that say "None of the above".
Or, if this one is checked, to deny to be checked any other checkbox for a choice.
And this can be achived with simple code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ChkChoice_1_BeforeUpdate(Cancel As Integer)
  2.     If ChkChoice_1 Then
  3.         If ChkNoChoice Then
  4.             MsgBox ("Message from chkChoice_1:  chkNoChoice is checked")
  5.             Cancel = True
  6.         End If
  7.     End If
  8. End Sub
  10. Private Sub chkChoice_2_BeforeUpdate(Cancel As Integer)
  11.     If ChkChoice_2 Then
  12.         If ChkNoChoice Then
  13.             MsgBox ("Message from chkChoice_2:  ChkNoChoice is checked")
  14.             Cancel = True
  15.         End If
  16.     End If
  17. End Sub
  19. Private Sub chkNoChoice_BeforeUpdate(Cancel As Integer)
  20.     If ChkNoChoice Then
  21.         If ChkChoice_1 Or ChkChoice_2 Then
  22.             MsgBox ("Message from chkNoChoice: You already made a choice")
  23.             Cancel = True
  24.         End If
  25.     End If
  26. End Sub

Also you can use another strategy:
Expand|Select|Wrap|Line Numbers
  1. Private Sub chkChoice_1_BeforeUpdate(Cancel As Integer)
  2.     If ChkChoice_1 Then
  3.         ChkNoChoice = False
  4.     End If
  5. End Sub
  7. Private Sub ChkChoice_2_BeforeUpdate(Cancel As Integer)
  8.     If ChkChoice_2 Then
  9.         ChkNoChoice = False
  10.     End If
  11. End Sub
  13. Private Sub ChkNoChoice_BeforeUpdate(Cancel As Integer)
  14.     If ChkNoChoice Then
  15.         ChkChoice_1 = False
  16.         ChkChoice_2 = False
  17.     End If
  18. End Sub
Apr 9 '12 #7

Expert Mod 10K+
P: 12,366
I didn't say remove your code from your other event. I only said use the On Current event. You use the On Current to solve the issue of changing records. You keep your existing code in the After Update to handle user input.
Apr 9 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.