Validation rules

[ConnollyS]

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 : )

[hjozinovic]

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:

If Me.[PO received] = True Then
Me.[PO Number].Visible=True
'(or you can open a new modal form with PO Number control on it for the user to fill it in)
 
 

Hope this helps. I'll be around if you have more questions.
H.

[ConnollyS]

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:

If Me.[PO received] = True Then
Me.[PO Number].Visible=True
'(or you can open a new modal form with PO Number control on it for the user to fill it in)
 
 

Hope this helps. I'll be around if you have more questions.
H.

[ConnollyS]

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

[hjozinovic]

Hi C.

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

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.[PO received] = True And Me.[PO number] = "" Then
MsgBox "Please enter PO number"
 
DoCmd.CancelEvent
DoCmd.GoToControl "PO number"
End If
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.

[ConnollyS]

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].

 Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.[PO Received] = True And Me.[PO number] = "" Then
MsgBox "Please enter PO number"
DoCmd.CancelEvent
DoCmd.GoToControl "PO number"
End If
End Sub
 

and also:

 Private Sub Form_AfterUpdate(Cancel As Integer)
If Me.[PO Received] = True And Me.[PO number] = "" Then
MsgBox "Please enter PO number"
DoCmd.CancelEvent
DoCmd.GoToControl "PO number"
End If
End Sub
 
 

Hi C.

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

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.[PO received] = True And Me.[PO number] = "" Then
MsgBox "Please enter PO number"
 
DoCmd.CancelEvent
DoCmd.GoToControl "PO number"
End If
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.

[missinglinq]

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

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Me.[PO Received] = True And IsNull(Me.[PO number]) Then
 MsgBox "Please enter PO number"
 DoCmd.CancelEvent
 DoCmd.GoToControl "PO number"
 End If
 End Sub
 

Please remember to always use Code Tags when posting code.

Welcome to TheScripts!

Linq ;0)>

[ConnollyS]

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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Me.[PO Received] = True And IsNull(Me.[PO number]) Then
 MsgBox "Please enter PO number"
 DoCmd.CancelEvent
 DoCmd.GoToControl "PO number"
 End If
 End Sub
 

Please remember to always use Code Tags when posting code.

Welcome to TheScripts!

Linq ;0)>