Validation before adding record Lisa 12/9/2008 4:11:01 PM 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.

Re: Validation before adding record "Jeff Boyce" <nonsense[ at ]nonsense.com> 12/9/2008 4:43:49 PM 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 "Lisa" <Lisa[ at ]discussions.microsoft.com> wrote in message news:29447EB7-BA41-4A54-958C-354E12B6F667[ at ]microsoft.com... [Quoted Text] >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. >

RE: Validation before adding record Mrs. Ugh 12/9/2008 4:52:00 PM 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 "Lisa" wrote: [Quoted Text] > 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. >

RE: Validation before adding record Lisa 12/9/2008 5:55:03 PM 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" wrote: [Quoted Text] > 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 > Code for Button to go to next record Private Sub Command50_Click() DoCmd.GoToRecord , , acNewRec End Sub > Jill > "Lisa" wrote: > > > 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. > >

RE: Validation before adding record "tkelley via AccessMonster.com" <u47368[ at ]uwe> 12/9/2008 8:07:49 PM 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 Lisa wrote: [Quoted Text] >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 >[quoted text clipped - 7 lines] >> DoCmd.CancelEvent 'stop the update >> End If > >Code for Button to go to next record >Private Sub Command50_Click() > > DoCmd.GoToRecord , , acNewRec > > >End Sub > >> Jill >> >[quoted text clipped - 10 lines] >> > >> > Thanks for any information you can provide. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200812/1

RE: Validation before adding record Lisa 12/10/2008 3:26:04 PM Excuse my lack of knowledge.. but what is a boolean function.. and where would I put this code? Lisa "tkelley via AccessMonster.com" wrote: [Quoted Text] > 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 > > > > > > Lisa wrote: > >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 > >[quoted text clipped - 7 lines] > >> DoCmd.CancelEvent 'stop the update > >> End If > > > >Code for Button to go to next record > >Private Sub Command50_Click() > > > > DoCmd.GoToRecord , , acNewRec > > > > > >End Sub > > > >> Jill > >> > >[quoted text clipped - 10 lines] > >> > > >> > Thanks for any information you can provide. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200812/1 > >

RE: Validation before adding record "tkelley via AccessMonster.com" <u47368[ at ]uwe> 12/10/2008 4:01:50 PM 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). Lisa wrote: [Quoted Text] >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: >> >[quoted text clipped - 113 lines] >> >> > >> >> > Thanks for any information you can provide. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200812/1

RE: Validation before adding record Lisa 12/31/2008 1:27:01 PM 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" wrote: [Quoted Text] > 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). > > > > Lisa wrote: > >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: > >> > >[quoted text clipped - 113 lines] > >> >> > > >> >> > Thanks for any information you can provide. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200812/1 > >