Form Field Validation On The Fly

[Guest]

Hi,

I want to validate 3 fields on a form, two text boxes and a combo box. The only validation I require is that they are not empty. I have tried the controls' inbuilt 'Validation Rule/text' options but the macro's I have on the button which closes the form appear to force the form to close before the validation rule can come into effect.

I would appreciate it if someone could give me some pointers for some validation coding which would work whilst the user is filling the form out, I could attach this to the 'On lost focus' event or something similar.

Thanks

 

[Allen Browne]

The simplest solution is to:
1. Open the *table* in design view.
2. Select the field that must have an entry.
3. In the lower pane, set the Required property to Yes.
4. Repeat steps 2 and 3 for other fields.
5. Save the table.

If you want to do it in the form, use the BeforeUpdate event procedure of
the *form*. You cannot use the events of the controls, because there is no
guarantee that the user will ever visit all controls.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as String

If IsNull(Me.[SomeControl]) Then
Cancel = True
strMsg = strMsg & "SomeControl required." & vbCrLf
End If

If IsNull(Me.[AnotherControl]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

'etc

If Cancel Then
MsgBox strMsg
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to validate 3 fields on a form, two text boxes and a combo box. The

only validation I require is that they are not empty. I have tried the
controls' inbuilt 'Validation Rule/text' options but the macro's I have on
the button which closes the form appear to force the form to close before
the validation rule can come into effect.

I would appreciate it if someone could give me some pointers for some

validation coding which would work whilst the user is filling the form out,
I could attach this to the 'On lost focus' event or something similar.

 

[Allen Browne]

Setting the Required property of the field in the table does work, very well
in fact.

If you are not receiving the MsgBox() there is something wrong with your
database. Try a compact and repair:
Tools | Database Utilities | Compact/Repair

Then ensure that your code compiles. From the code window:
Debug | Compile
Fix any errors.

The open the code and run it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for your quick reply.

Have already tried your first solution with no noticable effect.

Used the following code in the form's before update event. When I open the

form it just hangs and requires a control alt delete to exit access. Didn't
mention that I am running Access 2000.

Thanks




Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.[visitor_fore]) Then
Cancel = True
strMsg = strMsg & "SomeControl required." & vbCrLf
End If

If IsNull(Me.[visitor_sir]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

If IsNull(Me.[visitorpassno_lookup]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

If Cancel Then
MsgBox strMsg
End If
End Sub

The simplest solution is to:
1. Open the *table* in design view.
2. Select the field that must have an entry.
3. In the lower pane, set the Required property to Yes.
4. Repeat steps 2 and 3 for other fields.
5. Save the table.

If you want to do it in the form, use the BeforeUpdate event procedure of
the *form*. You cannot use the events of the controls, because there is no
guarantee that the user will ever visit all controls.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as String

If IsNull(Me.[SomeControl]) Then
Cancel = True
strMsg = strMsg & "SomeControl required." & vbCrLf
End If

If IsNull(Me.[AnotherControl]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

'etc

If Cancel Then
MsgBox strMsg
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to validate 3 fields on a form, two text boxes and a combo box.

The
only validation I require is that they are not empty. I have tried the
controls' inbuilt 'Validation Rule/text' options but the macro's I have on
the button which closes the form appear to force the form to close before
the validation rule can come into effect.

I would appreciate it if someone could give me some pointers for some

validation coding which would work whilst the user is filling the form out,
I could attach this to the 'On lost focus' event or something similar.

Thanks

 

[Guest]

Allen,

Right, the Required property in the table does sort of work, it simply doesn't save any record that is mssing the required info, but it doesn't show any error message prompts.

After restarting Access your previous code works, only problem is that I click Ok to complete the form and the message box pops up asking to insert the missing 1, 2 or 3 pieces of data, but after pressing Ok, goes to the next form.

I tried to move the code to use the ok/finish buttons 'On Mouse Move' event instead as once this is clicked a number of macro's run, two add date and time information to two hidden fields, another opens the next form which is generated by info from the first and the second closes the old form. I used the following code:

Private Sub signin_button_MouseMove(Cancel As Integer, Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim strMsg As String

If IsNull(Me.[visitor_fore]) Then
Cancel = True
strMsg = strMsg & "Please insert forename." & vbCrLf
End If

If IsNull(Me.[visitor_sur]) Then
Cancel = True
strMsg = strMsg & "Please insert Surname." & vbCrLf
End If

If IsNull(Me.[visitorpassno_lookup]) Then
Cancel = True
strMsg = strMsg & "Please insert visitor pass number." & vbCrLf
End If

If Cancel Then
MsgBox strMsg
End If
End Sub



Which gives me the error:
The expression On Load you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name

Thanks

Setting the Required property of the field in the table does work, very well
in fact.

If you are not receiving the MsgBox() there is something wrong with your
database. Try a compact and repair:
Tools | Database Utilities | Compact/Repair

Then ensure that your code compiles. From the code window:
Debug | Compile
Fix any errors.

The open the code and run it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for your quick reply.

Have already tried your first solution with no noticable effect.

Used the following code in the form's before update event. When I open the

form it just hangs and requires a control alt delete to exit access. Didn't
mention that I am running Access 2000.

Thanks




Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.[visitor_fore]) Then
Cancel = True
strMsg = strMsg & "SomeControl required." & vbCrLf
End If

If IsNull(Me.[visitor_sir]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

If IsNull(Me.[visitorpassno_lookup]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

If Cancel Then
MsgBox strMsg
End If
End Sub

The simplest solution is to:
1. Open the *table* in design view.
2. Select the field that must have an entry.
3. In the lower pane, set the Required property to Yes.
4. Repeat steps 2 and 3 for other fields.
5. Save the table.

If you want to do it in the form, use the BeforeUpdate event procedure of
the *form*. You cannot use the events of the controls, because there is no
guarantee that the user will ever visit all controls.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as String

If IsNull(Me.[SomeControl]) Then
Cancel = True
strMsg = strMsg & "SomeControl required." & vbCrLf
End If

If IsNull(Me.[AnotherControl]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

'etc

If Cancel Then
MsgBox strMsg
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I want to validate 3 fields on a form, two text boxes and a combo box. The
only validation I require is that they are not empty. I have tried the
controls' inbuilt 'Validation Rule/text' options but the macro's I have on
the button which closes the form appear to force the form to close before
the validation rule can come into effect.

I would appreciate it if someone could give me some pointers for some
validation coding which would work whilst the user is filling the form out,
I could attach this to the 'On lost focus' event or something similar.

Thanks

 

[Allen Browne]

We are talking about a bound form here, I presume?

You should receive a prompt to warn you that the record could not be saved
*unless* you use the Close action/method to close the form. There is a bug
with that process that stops you being notified, as explained in this
article:
http://allenbrowne.com/bug-01.html


In your code, you used the wrong event. Cancelling Form_Update will prevent
the record being saved.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Right, the Required property in the table does sort of work, it simply

doesn't save any record that is mssing the required info, but it doesn't
show any error message prompts.

After restarting Access your previous code works, only problem is that I

click Ok to complete the form and the message box pops up asking to insert
the missing 1, 2 or 3 pieces of data, but after pressing Ok, goes to the
next form.

I tried to move the code to use the ok/finish buttons 'On Mouse Move'

event instead as once this is clicked a number of macro's run, two add date
and time information to two hidden fields, another opens the next form which
is generated by info from the first and the second closes the old form. I
used the following code:

Private Sub signin_button_MouseMove(Cancel As Integer, Button As Integer,

Shift As Integer, X As Single, Y As Single)

Dim strMsg As String

If IsNull(Me.[visitor_fore]) Then
Cancel = True
strMsg = strMsg & "Please insert forename." & vbCrLf
End If

If IsNull(Me.[visitor_sur]) Then
Cancel = True
strMsg = strMsg & "Please insert Surname." & vbCrLf
End If

If IsNull(Me.[visitorpassno_lookup]) Then
Cancel = True
strMsg = strMsg & "Please insert visitor pass number." & vbCrLf
End If

If Cancel Then
MsgBox strMsg
End If
End Sub



Which gives me the error:
The expression On Load you entered as the event property setting produced

the following error: Procedure declaration does not match description of
event or procedure having the same name

Thanks

Setting the Required property of the field in the table does work, very well
in fact.

If you are not receiving the MsgBox() there is something wrong with your
database. Try a compact and repair:
Tools | Database Utilities | Compact/Repair

Then ensure that your code compiles. From the code window:
Debug | Compile
Fix any errors.

The open the code and run it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for your quick reply.

Have already tried your first solution with no noticable effect.

Used the following code in the form's before update event. When I open

the
form it just hangs and requires a control alt delete to exit access. Didn't
mention that I am running Access 2000.

Thanks




Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.[visitor_fore]) Then
Cancel = True
strMsg = strMsg & "SomeControl required." & vbCrLf
End If

If IsNull(Me.[visitor_sir]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

If IsNull(Me.[visitorpassno_lookup]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

If Cancel Then
MsgBox strMsg
End If
End Sub







:

The simplest solution is to:
1. Open the *table* in design view.
2. Select the field that must have an entry.
3. In the lower pane, set the Required property to Yes.
4. Repeat steps 2 and 3 for other fields.
5. Save the table.

If you want to do it in the form, use the BeforeUpdate event

procedure
of

the *form*. You cannot use the events of the controls, because there

is
no

guarantee that the user will ever visit all controls.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as String

If IsNull(Me.[SomeControl]) Then
Cancel = True
strMsg = strMsg & "SomeControl required." & vbCrLf
End If

If IsNull(Me.[AnotherControl]) Then
Cancel = True
strMsg = strMsg & "AnotherControl required." & vbCrLf
End If

'etc

If Cancel Then
MsgBox strMsg
End If
End Sub



I want to validate 3 fields on a form, two text boxes and a combo

box.
The

only validation I require is that they are not empty. I have tried the
controls' inbuilt 'Validation Rule/text' options but the macro's I

have
on

the button which closes the form appear to force the form to close before
the validation rule can come into effect.

I would appreciate it if someone could give me some pointers for some
validation coding which would work whilst the user is filling the

form
out,

I could attach this to the 'On lost focus' event or something similar.

Thanks