Validation before adding record

L

Lisa

I have a form created off a table. I have set validation the validation rule
and text in the table design.

On the form I have created a button to create a new record. When I click
that button and I leave a field blank it gives me a visual basic popup with a
runtime error... and the error message what i indicated in my validation
text. There are buttons for end, debug or help. Is there a way that I can
customize this so that they don't get those options.. or is there a better
way to get the fields to error out so they can't add a new record until this
record is completed with the required field input.

Thanks for any information you can provide.
 
J

Jeff Boyce

Lisa

Check into using the BeforeUpdate event on the form. You can add the code
to check validation of any/all fields there, provide custom messages, and
cancel the <save>, if necessary.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Mrs. Ugh

You can do your validation using code in the Before Update event of your
form. Open the form's property box, go to the Event tab and click on the
Before Update line. Click on the ... next to the line and select Code
Builder. Enter code like the following:
Dim stMsgText as string
If Nz([fieldname], 0) = 0 Then ' if the field is null tell them
stMsgText = "You must enter whatever." ' text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Data" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update
End If

Jill
 
L

Lisa

Jill,

Thank you.. i am still having a bit of trouble.. here is my code that i used
on the before update.. when i click on the button (see code further down for
this button) that i have on the form to add a new record it gives me the
first popup box where i am missing information then when I hit ok on that
popup box i get a microsoft visual basic popup box with a runtime error..you
cant go to the specified record.

Can you figure out what I did wrong?


Dim stMsgText As String
If Nz([Rnwl_MB_MidTerm], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the policy type from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Type" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([Segment], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the segment from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Segment" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([RatingState], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the rating state from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Rating State" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo1], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy number for policy number 1." ' text for
the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 1" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo1-EffDate], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy effective date for policy number 1." '
text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 1 Effective Date"
'put up a message box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo2], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy number for policy number 2." ' text for
the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 2" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo2-EffDate], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy effective date for policy number 1." '
text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 2 Effective Date"
'put up a message box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolicyTerm], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the policy term from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Term" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update
End If


Mrs. Ugh said:
You can do your validation using code in the Before Update event of your
form. Open the form's property box, go to the Event tab and click on the
Before Update line. Click on the ... next to the line and select Code
Builder. Enter code like the following:
Dim stMsgText as string
If Nz([fieldname], 0) = 0 Then ' if the field is null tell them
stMsgText = "You must enter whatever." ' text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Data" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update
End If
Click to expand...
Code for Button to go to next record
Private Sub Command50_Click()

DoCmd.GoToRecord , , acNewRec


End Sub
 
L

Lisa

Excuse my lack of knowledge.. but what is a boolean function.. and where
would I put this code?

Lisa

tkelley via AccessMonster.com said:
Put your validation code in a boolean function:

Public Function fnValidateEntry() as Boolean

fnValidateEntry = FALSE 'this is just to be certain it's always false to
start

'put your validation code here, except
'replace "DoCmd.CancelEvent 'stop the update"
'with fnValidateEntry = FALSE

'then at the end, after all your validation passes you put:

fnValidateEntry = TRUE

End Function

Once that is done:

Private Sub Command50_Click()

If fnValidateEntry = FALSE then
Exit Sub
Else
DoCmd.GoToRecord , , acNewRec
Endif

End Sub




Jill,

Thank you.. i am still having a bit of trouble.. here is my code that i used
on the before update.. when i click on the button (see code further down for
this button) that i have on the form to add a new record it gives me the
first popup box where i am missing information then when I hit ok on that
popup box i get a microsoft visual basic popup box with a runtime error..you
cant go to the specified record.

Can you figure out what I did wrong?

Dim stMsgText As String
If Nz([Rnwl_MB_MidTerm], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the policy type from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Type" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([Segment], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the segment from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Segment" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([RatingState], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the rating state from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Rating State" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo1], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy number for policy number 1." ' text for
the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 1" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo1-EffDate], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy effective date for policy number 1." '
text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 1 Effective Date"
'put up a message box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo2], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy number for policy number 2." ' text for
the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 2" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo2-EffDate], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy effective date for policy number 1." '
text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 2 Effective Date"
'put up a message box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolicyTerm], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the policy term from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Term" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update
End If
You can do your validation using code in the Before Update event of your
form. Open the form's property box, go to the Event tab and click on the
Click to expand...
[quoted text clipped - 7 lines]
DoCmd.CancelEvent 'stop the update
End If
Click to expand...

Code for Button to go to next record
Private Sub Command50_Click()

DoCmd.GoToRecord , , acNewRec


End Sub
Jill
Click to expand...
[quoted text clipped - 10 lines]
Thanks for any information you can provide.
Click to expand...
Click to expand...
Click to expand...
 
L

Lisa

So I got this to work.. but now i have another problem. I have a form that
is run off a query that has a subform on it. I want the fields on the
subform to be required how do I reference the subform on the before update
code, here is what i have for the code:

The subform name is: FRM_RenewalQuoting-2 SupportSvcs

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stMsgText As String
If Nz([SvcOps_RepCode], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the your rep code from the dropdown." ' text for
the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Type" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([SvcOps_dateWorked], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input todays date." ' text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Segment" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([Disposition], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the disposition from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Rating State" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo1], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy number for policy number 1." ' text for
the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 1" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo1-EffDate], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy effective date for policy number 1." '
text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 1 Effective Date"
'put up a message box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo2], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy number for policy number 2." ' text for
the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 2" 'put up a message
box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolNo2-EffDate], 0) = 0 Then ' if the field is null tell them
stMsgText = "Input the policy effective date for policy number 1." '
text for the error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Number 2 Effective Date"
'put up a message box explaining the error
DoCmd.CancelEvent 'stop the update

ElseIf Nz([PolicyTerm], 0) = 0 Then ' if the field is null tell them
stMsgText = "Choose the policy term from the dropdown." ' text for the
error message
MsgBox stMsgText, vbOKOnly, "Missing Policy Term" 'put up a message box
explaining the error
DoCmd.CancelEvent 'stop the update
End If


End Sub


tkelley via AccessMonster.com said:
In the same form's code window. A boolean function runs the code (in your
case the validation code) and allows you to pass back a true or false to the
calling code (in your case, Private Sub Command50_Click).


Excuse my lack of knowledge.. but what is a boolean function.. and where
would I put this code?

Lisa
Put your validation code in a boolean function:
Click to expand...
[quoted text clipped - 113 lines]
Thanks for any information you can provide.
Click to expand...
Click to expand...
Click to expand...
 

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

Validation Rule Popup 1
Validation Rule Error Messages 1
Validation in child form 1
Validation Rule assistance 0
Validation Question 4
Validation Rules for a Control on a Form (?) 4
Problem with Validation Error 10
Validation Rule not working in table 2

Top