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

Validation rules

P: 7
Hello,

I have a form in my database and I have a check box (PO Received) , when it is ticked I would like to make the user enter the PO Number in a text box. I have tried to use the validation rule [PO Received] and with the validation text [Please enter PO number] but this does not seem to work.

Thanks in advance : )
Dec 10 '07 #1
Share this Question
Share on Google+
7 Replies


100+
P: 167
Hi Connoly!
Have you tried to use after update event for that check box?
in the after update event you can use code like this:

Expand|Select|Wrap|Line Numbers
  1. If Me.[PO received] = True Then
  2. Me.[PO Number].Visible=True
  3. '(or you can open a new modal form with PO Number control on it for the user to fill it in)
  4.  
  5.  

Hope this helps. I'll be around if you have more questions.
H.
Dec 10 '07 #2

P: 7
Great I will give it a try and let you know : )

Hi Connoly!
Have you tried to use after update event for that check box?
in the after update event you can use code like this:

Expand|Select|Wrap|Line Numbers
  1. If Me.[PO received] = True Then
  2. Me.[PO Number].Visible=True
  3. '(or you can open a new modal form with PO Number control on it for the user to fill it in)
  4.  
  5.  

Hope this helps. I'll be around if you have more questions.
H.
Dec 11 '07 #3

P: 7
Hello Hjozinovic,

I used the following as suggested:

Private Sub PO_Received_AfterUpdate()
If Me.[PO received] = True Then
Me.[PO Number].Visible = True
End If
End Sub

But what I want it to do is to force the user to enter a PO Number or it wont let them save the record. i.e. All Records with a PO Received must have a PO number.

Hope this make sense, Cheers
Dec 11 '07 #4

100+
P: 167
Hi C.

I just tested this code and it run just fine. I believe this is what you need:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.[PO received] = True And Me.[PO number] = "" Then
  3. MsgBox "Please enter PO number"
  4.  
  5. DoCmd.CancelEvent
  6. DoCmd.GoToControl "PO number"
  7. End If
  8. End Sub
This code checks the current record before updating it. If PO is checked, and PO number is empty it will cancel the save record event and it will move focus to PO number control for the user to enter the number inside.

Hope it helps...
h.
Dec 11 '07 #5

P: 7
H,

I most be going mad, have tried the following, but it does not produce the message box if I check PO or not I have tried 'Not Null' instead of "" as well but still not working. Sorry to be a pain, the form is populated with a query will this make a difference or could the problem be with the space in [PO Received].

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.[PO Received] = True And Me.[PO number] = "" Then
  3. MsgBox "Please enter PO number"
  4. DoCmd.CancelEvent
  5. DoCmd.GoToControl "PO number"
  6. End If
  7. End Sub
  8.  
and also:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_AfterUpdate(Cancel As Integer)
  2. If Me.[PO Received] = True And Me.[PO number] = "" Then
  3. MsgBox "Please enter PO number"
  4. DoCmd.CancelEvent
  5. DoCmd.GoToControl "PO number"
  6. End If
  7. End Sub
  8.  
  9.  
Hi C.

I just tested this code and it run just fine. I believe this is what you need:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.[PO received] = True And Me.[PO number] = "" Then
  3. MsgBox "Please enter PO number"
  4.  
  5. DoCmd.CancelEvent
  6. DoCmd.GoToControl "PO number"
  7. End If
  8. End Sub
This code checks the current record before updating it. If PO is checked, and PO number is empty it will cancel the save record event and it will move focus to PO number control for the user to enter the number inside.

Hope it helps...
h.
Dec 11 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
You had the right idea in checking for Null, just the wrong syntax, I expect:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  If Me.[PO Received] = True And IsNull(Me.[PO number]) Then
  3.  MsgBox "Please enter PO number"
  4.  DoCmd.CancelEvent
  5.  DoCmd.GoToControl "PO number"
  6.  End If
  7.  End Sub
  8.  
Please remember to always use Code Tags when posting code.

Welcome to TheScripts!

Linq ;0)>
Dec 11 '07 #7

P: 7
Hello Linq,

I will make sure I use code tags in the future : )

And we are rocking and rolling with the new code the only problem was it didn't like running the code when updating on the field and said that I needed to save the form before running the 'GoToControl' method. However when I did it on the form itself it worked fine, (another although) although I did have to close the form and display a message saying not saved and that the user would need to go back in and re-enter and inc a PO number. This is not the end of the world as I am chuffed with it at the moment.

Thanks for your input 'A*'


You had the right idea in checking for Null, just the wrong syntax, I expect:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  If Me.[PO Received] = True And IsNull(Me.[PO number]) Then
  3.  MsgBox "Please enter PO number"
  4.  DoCmd.CancelEvent
  5.  DoCmd.GoToControl "PO number"
  6.  End If
  7.  End Sub
  8.  
Please remember to always use Code Tags when posting code.

Welcome to TheScripts!

Linq ;0)>
Dec 11 '07 #8

Post your reply

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