Validation Rule

[blake7]

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

 

[Ofer Cohen]

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

 

[blake7]

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?

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

 

[Linq Adams via AccessMonster.com]

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

 

[Ofer Cohen]

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.

 

[blake7]

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

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

 

[Ofer Cohen]

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

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

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

 

[blake7]

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

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

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

 

[Ofer Cohen]

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.

--
Good Luck
BS"D

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

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

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

 

[blake7]

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

--
Good Luck
BS"D

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

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

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