Validate Data

T

Trev

Hi All

I need to validate that data is entered into three of 12
text boxes on a form and have no idea about writing code.
I looked at using the inbuilt data validation
capabilities of access but the database is used by a
number of users and my users found the standard message a
little confusing.

I found some great code from the net that will check that
data is entered in all text boxes but we don't need to
have data in all.

Assuming my text boxes as TextBox1, TextBox2 & Textbox3
Can I modify the code below to make it work for me or is
there a better way.

Public Function RequiredData(ByVal TheForm As Form) As
Boolean
'Check that all TextBox controls have required data
entered
Dim Ctl As Control
Dim Num As Integer
On Error GoTo Err_RequiredData
RequiredData = False
Num = 0
For Each Ctl In TheForm
If Ctl.ControlType = acTextBox Then
If Ctl = "" Or IsNull(Ctl) Then
Num = 1
Exit For
End If
End If
Next Ctl
If Num = 1 Then
MsgBox "Data is required in the " & Ctl.Name & "
field," & vbCr & _
"please ensure this is entered.", _
vbInformation, "Required Data..."
RequiredData = True
Ctl.SetFocus
Else
RequiredData = False
End If
Exit_RequiredData:
On Error Resume Next
If Not (Ctl Is Nothing) Then
Set Ctl = Nothing
End If
Exit Function
Err_RequiredData:
Select Case Err
Case 0
Resume Next
Case Else
MsgBox "Error: " & Err.Number & vbCrLf &
vbCrLf & Err.Description, _
VbInformation
End Select
End Function

Any help appreciated

Regards
 
D

Dirk Goldgar

Trev said:
Hi All

I need to validate that data is entered into three of 12
text boxes on a form and have no idea about writing code.
I looked at using the inbuilt data validation
capabilities of access but the database is used by a
number of users and my users found the standard message a
little confusing.

I found some great code from the net that will check that
data is entered in all text boxes but we don't need to
have data in all.

Assuming my text boxes as TextBox1, TextBox2 & Textbox3
Can I modify the code below to make it work for me or is
there a better way.

Public Function RequiredData(ByVal TheForm As Form) As
Boolean
'Check that all TextBox controls have required data
entered
Dim Ctl As Control
Dim Num As Integer
On Error GoTo Err_RequiredData
RequiredData = False
Num = 0
For Each Ctl In TheForm
If Ctl.ControlType = acTextBox Then
If Ctl = "" Or IsNull(Ctl) Then
Num = 1
Exit For
End If
End If
Next Ctl
If Num = 1 Then
MsgBox "Data is required in the " & Ctl.Name & "
field," & vbCr & _
"please ensure this is entered.", _
vbInformation, "Required Data..."
RequiredData = True
Ctl.SetFocus
Else
RequiredData = False
End If
Exit_RequiredData:
On Error Resume Next
If Not (Ctl Is Nothing) Then
Set Ctl = Nothing
End If
Exit Function
Err_RequiredData:
Select Case Err
Case 0
Resume Next
Case Else
MsgBox "Error: " & Err.Number & vbCrLf &
vbCrLf & Err.Description, _
VbInformation
End Select
End Function

Any help appreciated

Regards
Click to expand...

I have a slightly different routine that I use. It's similar in
concept, but (I think) better. Here is the code:

'----- start of function code -----
Function fncRequiredFieldsMissing(frm As Form) As Boolean

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag = "Required" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If
If blnNoValue Then
strErrorMessage = strErrorMessage & vbCr & _
" " & .Name
If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If
End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, vbInformation, _
"Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'----- end of function code -----

You would put the above code into a standard module; that is, one that
is displayed on the Modules tab of the database window. If you create a
new module for this purpose, don't give that module the same name as the
function.

Now, after you've pasted this function into your module and saved it, to
use it you have to do two things:

1. Mark the controls you want to be required. You do this by setting
each such control's Tag property, on its property sheet in design view,
to "Required". So if you want Textbox1, Textbox2, and Textbox3 to be
required fields, you would open the form in design view, open the
property sheet of each of these controls in turn, go to the Other tab on
the property sheet, and type

Required

on the line for the Tag property.

2. Create an event procedure for the form's BeforeUpdate event, to call
the function. To do this, with the form open in design view, open the
property sheet of the form itself, go to the Event tab, and set the
Before Update event property to "[Event Procedure]". Then click the
little "build" button at the end of the line (captioned "..."), and
Access will create the shell of an event procedure for you. Fill it in
so that it looks like this:

'----- start of code for the form module -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'----- end of code for the form module -----

You can leave off the lines that begin '----- .

That should do it for you. Save and close the form, then reopen it and
see how it works.
 
T

Trev

Thanks Dirk
It works a treat.
-----Original Message-----
Hi All

I need to validate that data is entered into three of 12
text boxes on a form and have no idea about writing code.
I looked at using the inbuilt data validation
capabilities of access but the database is used by a
number of users and my users found the standard message a
little confusing.

I found some great code from the net that will check that
data is entered in all text boxes but we don't need to
have data in all.

Assuming my text boxes as TextBox1, TextBox2 & Textbox3
Can I modify the code below to make it work for me or is
there a better way.

Public Function RequiredData(ByVal TheForm As Form) As
Boolean
'Check that all TextBox controls have required data
entered
Dim Ctl As Control
Dim Num As Integer
On Error GoTo Err_RequiredData
RequiredData = False
Num = 0
For Each Ctl In TheForm
If Ctl.ControlType = acTextBox Then
If Ctl = "" Or IsNull(Ctl) Then
Num = 1
Exit For
End If
End If
Next Ctl
If Num = 1 Then
MsgBox "Data is required in the " & Ctl.Name & "
field," & vbCr & _
"please ensure this is entered.", _
vbInformation, "Required Data..."
RequiredData = True
Ctl.SetFocus
Else
RequiredData = False
End If
Exit_RequiredData:
On Error Resume Next
If Not (Ctl Is Nothing) Then
Set Ctl = Nothing
End If
Exit Function
Err_RequiredData:
Select Case Err
Case 0
Resume Next
Case Else
MsgBox "Error: " & Err.Number & vbCrLf &
vbCrLf & Err.Description, _
VbInformation
End Select
End Function

Any help appreciated

Regards
Click to expand...

I have a slightly different routine that I use. It's similar in
concept, but (I think) better. Here is the code:

'----- start of function code -----
Function fncRequiredFieldsMissing(frm As Form) As Boolean

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag = "Required" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If
If blnNoValue Then
strErrorMessage = strErrorMessage & vbCr & _
" " & .Name
If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If
End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, vbInformation, _
"Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'----- end of function code -----

You would put the above code into a standard module; that is, one that
is displayed on the Modules tab of the database window. If you create a
new module for this purpose, don't give that module the same name as the
function.

Now, after you've pasted this function into your module and saved it, to
use it you have to do two things:

1. Mark the controls you want to be required. You do this by setting
each such control's Tag property, on its property sheet in design view,
to "Required". So if you want Textbox1, Textbox2, and Textbox3 to be
required fields, you would open the form in design view, open the
property sheet of each of these controls in turn, go to the Other tab on
the property sheet, and type

Required

on the line for the Tag property.

2. Create an event procedure for the form's BeforeUpdate event, to call
the function. To do this, with the form open in design view, open the
property sheet of the form itself, go to the Event tab, and set the
Before Update event property to "[Event Procedure]". Then click the
little "build" button at the end of the line (captioned "..."), and
Access will create the shell of an event procedure for you. Fill it in
so that it looks like this:

'----- start of code for the form module -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'----- end of code for the form module -----

You can leave off the lines that begin '----- .

That should do it for you. Save and close the form, then reopen it and
see how it works.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
B

BigDuke6UK

I am struggling with form validation. I have used your code example, which is
just what I've been looking for, but I have a subForm to validate and this
subForm is also sitting inside a Tab Control. The 'Form_BeforeUpdtae
procedure does not fire no matter where I place the code (i.e inside the
subForm event procedure or the main form event procedure). Can anyone assist
me further?

Thanking you knowledgeable people in advance.
Regards
David Heywood

Dirk Goldgar said:
Trev said:
Hi All

I need to validate that data is entered into three of 12
text boxes on a form and have no idea about writing code.
I looked at using the inbuilt data validation
capabilities of access but the database is used by a
number of users and my users found the standard message a
little confusing.

I found some great code from the net that will check that
data is entered in all text boxes but we don't need to
have data in all.

Assuming my text boxes as TextBox1, TextBox2 & Textbox3
Can I modify the code below to make it work for me or is
there a better way.

Public Function RequiredData(ByVal TheForm As Form) As
Boolean
'Check that all TextBox controls have required data
entered
Dim Ctl As Control
Dim Num As Integer
On Error GoTo Err_RequiredData
RequiredData = False
Num = 0
For Each Ctl In TheForm
If Ctl.ControlType = acTextBox Then
If Ctl = "" Or IsNull(Ctl) Then
Num = 1
Exit For
End If
End If
Next Ctl
If Num = 1 Then
MsgBox "Data is required in the " & Ctl.Name & "
field," & vbCr & _
"please ensure this is entered.", _
vbInformation, "Required Data..."
RequiredData = True
Ctl.SetFocus
Else
RequiredData = False
End If
Exit_RequiredData:
On Error Resume Next
If Not (Ctl Is Nothing) Then
Set Ctl = Nothing
End If
Exit Function
Err_RequiredData:
Select Case Err
Case 0
Resume Next
Case Else
MsgBox "Error: " & Err.Number & vbCrLf &
vbCrLf & Err.Description, _
VbInformation
End Select
End Function

Any help appreciated

Regards
Click to expand...

I have a slightly different routine that I use. It's similar in
concept, but (I think) better. Here is the code:

'----- start of function code -----
Function fncRequiredFieldsMissing(frm As Form) As Boolean

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag = "Required" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If
If blnNoValue Then
strErrorMessage = strErrorMessage & vbCr & _
" " & .Name
If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If
End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, vbInformation, _
"Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'----- end of function code -----

You would put the above code into a standard module; that is, one that
is displayed on the Modules tab of the database window. If you create a
new module for this purpose, don't give that module the same name as the
function.

Now, after you've pasted this function into your module and saved it, to
use it you have to do two things:

1. Mark the controls you want to be required. You do this by setting
each such control's Tag property, on its property sheet in design view,
to "Required". So if you want Textbox1, Textbox2, and Textbox3 to be
required fields, you would open the form in design view, open the
property sheet of each of these controls in turn, go to the Other tab on
the property sheet, and type

Required

on the line for the Tag property.

2. Create an event procedure for the form's BeforeUpdate event, to call
the function. To do this, with the form open in design view, open the
property sheet of the form itself, go to the Event tab, and set the
Before Update event property to "[Event Procedure]". Then click the
little "build" button at the end of the line (captioned "..."), and
Access will create the shell of an event procedure for you. Fill it in
so that it looks like this:

'----- start of code for the form module -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'----- end of code for the form module -----

You can leave off the lines that begin '----- .

That should do it for you. Save and close the form, then reopen it and
see how it works.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

BigDuke6UK said:
I am struggling with form validation. I have used your code example,
which is just what I've been looking for, but I have a subForm to
validate and this subForm is also sitting inside a Tab Control. The
'Form_BeforeUpdtae procedure does not fire no matter where I place
the code (i.e inside the subForm event procedure or the main form
event procedure). Can anyone assist me further?

Thanking you knowledgeable people in advance.
Regards
David Heywood

Dirk Goldgar said:
I have a slightly different routine that I use. It's similar in
concept, but (I think) better. Here is the code:

'----- start of function code -----
Function fncRequiredFieldsMissing(frm As Form) As Boolean

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag = "Required" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If
If blnNoValue Then
strErrorMessage = strErrorMessage & vbCr
& _ " " & .Name
If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If
End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, vbInformation, _
"Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'----- end of function code -----

You would put the above code into a standard module; that is, one
that
is displayed on the Modules tab of the database window. If you
create a new module for this purpose, don't give that module the
same name as the function.

Now, after you've pasted this function into your module and saved
it, to use it you have to do two things:

1. Mark the controls you want to be required. You do this by setting
each such control's Tag property, on its property sheet in design
view,
to "Required". So if you want Textbox1, Textbox2, and Textbox3 to be
required fields, you would open the form in design view, open the
property sheet of each of these controls in turn, go to the Other
tab on the property sheet, and type

Required

on the line for the Tag property.

2. Create an event procedure for the form's BeforeUpdate event, to
call the function. To do this, with the form open in design view,
open the property sheet of the form itself, go to the Event tab, and
set the
Before Update event property to "[Event Procedure]". Then click the
little "build" button at the end of the line (captioned "..."), and
Access will create the shell of an event procedure for you. Fill it
in
so that it looks like this:

'----- start of code for the form module -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'----- end of code for the form module -----

You can leave off the lines that begin '----- .

That should do it for you. Save and close the form, then reopen it
and see how it works.
Click to expand...
Click to expand...

I need a bit more information about how you've got it set up. The tab
control is irrelevant; the subform may be relevant. Which form, the
main form or the subform, are you trying to validate? Bear in mind
that, to validate the subform with this code, you must operate on the
code module of the form that is being displayed in the subform control.

If it's the subform you're trying to validate, it may be clearest if you
open the subform (actually, the subform control's SourceObject form) as
a standalone object in design view, rather than working on it as
displayed within the design view of the main form. Then make sure that
the form's Before Update event property is set to "[Event Procedure]",
and that you have properly created the Form_BeforeUpdate event procedure
in that form's code module.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom class does not capture event 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Testing for required data entry in form fields. 6
Remove Identical words 0
How know if ControlType=acSubForm is SubReport... 2
AddPicturesToShapes 0
Search that creates a dynamic query 1
Argument not optional error 8

Top