Validation Rule

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blake7 - 09 Mar 2008 13:32 GMT

Hi, I have a form which contains a text box, when you enter a number in this
text box it displays the results in a another text box below, because it is
looking up the values in a table. I need to set a validation rule so that
the user must enter at least 000000 before tabbing to the next field, I want
to display "you must enter 000000" can you help please. Thank You

Reply to this Message

Ofer Cohen - 09 Mar 2008 14:10 GMT

Do you have code that runs after the value is entered?

If so, before this code runs you can add this code that check the length of
the field, and if it less then 6, return a message and exit the code

If Len(Me.[TextBoxName]) < 6 Then
MsgBox "Must enter 6 digit number"
Exit Sub
End If

If you don't want the leading zero's to be counted, you can remove them by
using Int

If Int(Me.[TextBoxName]) < 100000 Then
MsgBox "Must enter 6 digit number"
Exit Sub
End If

If there is no code running, you can use the above example on the OnExit
event of the text box, and instead of
Exit Sub

Use
Cancel = True
So it wont exit the text box

Signature

Good Luck
BS"D

> Hi, I have a form which contains a text box, when you enter a number in this
> text box it displays the results in a another text box below, because it is
> looking up the values in a table. I need to set a validation rule so that
> the user must enter at least 000000 before tabbing to the next field, I want
> to display "you must enter 000000" can you help please. Thank You

Reply to this Message

blake7 - 09 Mar 2008 20:53 GMT

Hi Thanks for info, I not really sure what you mean ?, is there a way of
making sure data has been entered into a specific field on the form even if
the user doesn't click or tab to that particular field ? Thanks again

> Do you have code that runs after the value is entered?
>
[quoted text clipped - 27 lines]
> > the user must enter at least 000000 before tabbing to the next field, I want
> > to display "you must enter 000000" can you help please. Thank You

Reply to this Message

Linq Adams - 09 Mar 2008 23:13 GMT

Very good! Most people never consider that when checking to see if a control
has data or not. In order to do this your validation code has to go into the
form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.[TextBoxName]) < 6 Then
MsgBox "The Whatever Field Must Contain Six Digits"
Cancel = True
End If
End Sub

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message

Ofer Cohen - 10 Mar 2008 08:12 GMT

As the OP wrote "before tabbing to the next field", so the BeforeUpdate event
of the form wont give the desire resault.
In addition, if that text box is unbound, then the before update event of
the text box wont work.

This why I suggested the OnExit event of the text box.

Signature

Good Luck
BS"D

> Very good! Most people never consider that when checking to see if a control
> has data or not. In order to do this your validation code has to go into the
[quoted text clipped - 6 lines]
> End If
> End Sub

Reply to this Message

blake7 - 10 Mar 2008 22:30 GMT

Thanks Ofer, I entered the statement you provided into the onExit field of
the text box, but it still allowed me to leave the form without entering
data, so i tried it again but just clicked in the field then when i tried to
exit this time an error message came up saying " Cannot find Macro If Int
(Me'. ?????
Thanks again anymore suggestions ? i am a newbee sorry

> As the OP wrote "before tabbing to the next field", so the BeforeUpdate event
> of the form wont give the desire resault.
[quoted text clipped - 13 lines]
> > End If
> > End Sub

Reply to this Message

Ofer Cohen - 11 Mar 2008 00:36 GMT

When the cursor located in the OnExit property you'll see a button with three
dots on the right, click on it and select code view.
Put the code between the

Sub ...
' the code here
End Sub

when the code is written directly in the property, it will look for a macro,
and this is why you get this error

Signature

Good Luck
BS"D

> Thanks Ofer, I entered the statement you provided into the onExit field of
> the text box, but it still allowed me to leave the form without entering
[quoted text clipped - 20 lines]
> > > End If
> > > End Sub

Reply to this Message

blake7 - 12 Mar 2008 21:32 GMT

Thanks Ofer that works fine now, Phew !!!

Another quick question - I have created a query that requires the user to
input the date upon launching the query, it works fine, but it there any way
you can have the date separators --/--/---- to appear in the parameter entry
box that appears on screen ????

Thanks Tony.

> When the cursor located in the OnExit property you'll see a button with three
> dots on the right, click on it and select code view.
[quoted text clipped - 31 lines]
> > > > End If
> > > > End Sub

Reply to this Message

Ofer Cohen - 13 Mar 2008 00:26 GMT

You can create a form that has a text box in it, in the text box set the
input mask as you want it. in the query refer to the text box in the form.
In the form add a button that run the query, in the OnClick event write

Docmd.OpenQuery "QueryName"

And the query SQL, instead of

Select * From TableName Where FieldName = [Please enter a date]

Use something like
Select * From TableName Where FieldName = Forms![FormName]![TextBoxName]

That way you can use a date picker also in the form.

Signature

Good Luck
BS"D

> Thanks Ofer that works fine now, Phew !!!
>
[quoted text clipped - 40 lines]
> > > > > End If
> > > > > End Sub

Reply to this Message

blake7 - 13 Mar 2008 20:43 GMT

Perfect - Thanks Again Ofer

> You can create a form that has a text box in it, in the text box set the
> input mask as you want it. in the query refer to the text box in the form.
[quoted text clipped - 55 lines]
> > > > > > End If
> > > > > > End Sub

Reply to this Message