Data validation
in [Forms]
|
From: Jon on 19 Jan 2010 18:35 I have set up a simple validation proceedure on the BeforeUpdate property as follows: Private Sub Form_BeforeUpdate(Cancel As Integer) If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EmailUpdates = -1 Then MsgBox "Enter an email address.", vbInformation, "Data Validation" Me.txtEmail.SetFocus DoCmd.CancelEvent Cancel = True End If End Sub The form also has a several command buttons (e.g. save, next record, previous record) which trigger the validation warning as expected. However the OK or close button on the data validation Msgbox triggers a Runtime error message; either 2105 'Can't go to specified record' or 2001 'You cancelled previous operation', depending on the cmdButton The go to next record proceedure is: Sub Command186_Click() On Error GoTo Err_Command186_Click DoCmd.GoToRecord , , acNext Exit_Command186_Click: Exit Sub Err_Command186_Click: MsgBox Err.Description Resume Exit_Command186_Click The save record proceedure is: Sub Command189_Click() On Error GoTo Err_Command189_Click DoCmd.RunCommand acCmdSaveRecord Exit_Command189_Click: Exit Sub Err_Command189_Click: MsgBox Err.Description Resume Exit_Command189_Click Can anyone explain why this is happening and/or how to avoid it. Many thanks
From: Gina Whipp on 19 Jan 2010 19:00 Jon, I would try moving it to the On_Current event, unless there is some specific reason you want it on the Before_Update? As to what is going on... are you trying to go the next record without filling in the eMail? -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Jon" <Jon(a)discussions.microsoft.com> wrote in message news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB(a)microsoft.com... >I have set up a simple validation proceedure on the BeforeUpdate property >as > follows: > > Private Sub Form_BeforeUpdate(Cancel As Integer) > > If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EmailUpdates = -1 Then > MsgBox "Enter an email address.", vbInformation, "Data Validation" > Me.txtEmail.SetFocus > DoCmd.CancelEvent > Cancel = True > End If > > End Sub > > The form also has a several command buttons (e.g. save, next record, > previous record) which trigger the validation warning as expected. However > the OK or close button on the data validation Msgbox triggers a Runtime > error > message; either 2105 'Can't go to specified record' or 2001 'You cancelled > previous operation', depending on the cmdButton > > The go to next record proceedure is: > > Sub Command186_Click() > On Error GoTo Err_Command186_Click > > DoCmd.GoToRecord , , acNext > > Exit_Command186_Click: > Exit Sub > > Err_Command186_Click: > MsgBox Err.Description > Resume Exit_Command186_Click > > The save record proceedure is: > > > Sub Command189_Click() > On Error GoTo Err_Command189_Click > > DoCmd.RunCommand acCmdSaveRecord > > Exit_Command189_Click: > Exit Sub > > Err_Command189_Click: > MsgBox Err.Description > Resume Exit_Command189_Click > > Can anyone explain why this is happening and/or how to avoid it. > > Many thanks
From: Jon on 19 Jan 2010 20:11 Thanks Gina. I was trying to have the data validation happen as the last thing before the record is saved or the user closes the form or moves to the next or preceeding record. This is actually only one of several simple validations I have to do after a record is added or edited. I was advised to use BeforeUpdate as ithe most appropriate event property for this type of proceedure. The OnCurrent event picks up the data validation error when I go into the record. "Gina Whipp" wrote: > Jon, > > I would try moving it to the On_Current event, unless there is some specific > reason you want it on the Before_Update? > > As to what is going on... are you trying to go the next record without > filling in the eMail? > > -- > Gina Whipp > 2010 Microsoft MVP (Access) > > "I feel I have been denied critical, need to know, information!" - Tremors > II > > http://www.regina-whipp.com/index_files/TipList.htm > > "Jon" <Jon(a)discussions.microsoft.com> wrote in message > news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB(a)microsoft.com... > >I have set up a simple validation proceedure on the BeforeUpdate property > >as > > follows: > > > > Private Sub Form_BeforeUpdate(Cancel As Integer) > > > > If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EmailUpdates = -1 Then > > MsgBox "Enter an email address.", vbInformation, "Data Validation" > > Me.txtEmail.SetFocus > > DoCmd.CancelEvent > > Cancel = True > > End If > > > > End Sub > > > > The form also has a several command buttons (e.g. save, next record, > > previous record) which trigger the validation warning as expected. However > > the OK or close button on the data validation Msgbox triggers a Runtime > > error > > message; either 2105 'Can't go to specified record' or 2001 'You cancelled > > previous operation', depending on the cmdButton > > > > The go to next record proceedure is: > > > > Sub Command186_Click() > > On Error GoTo Err_Command186_Click > > > > DoCmd.GoToRecord , , acNext > > > > Exit_Command186_Click: > > Exit Sub > > > > Err_Command186_Click: > > MsgBox Err.Description > > Resume Exit_Command186_Click > > > > The save record proceedure is: > > > > > > Sub Command189_Click() > > On Error GoTo Err_Command189_Click > > > > DoCmd.RunCommand acCmdSaveRecord > > > > Exit_Command189_Click: > > Exit Sub > > > > Err_Command189_Click: > > MsgBox Err.Description > > Resume Exit_Command189_Click > > > > Can anyone explain why this is happening and/or how to avoid it. > > > > Many thanks > > > . >
From: Gina Whipp on 19 Jan 2010 20:35 Jon, The Before Update event actually fires as soon as you hit the record which might be a wee bit too soon (that would be before the On_Current event). Code found there is usually that to update a Primary Key field. Perhaps you want to have a look at the On_Dirty event, you can stop them from leaving the record till they fill in your required fields OR are you also trying to leave them an out, like if they don't know the eMail? -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Jon" <Jon(a)discussions.microsoft.com> wrote in message news:4D25FD95-126D-4730-AB1F-DEA54DC12BF3(a)microsoft.com... > Thanks Gina. > > I was trying to have the data validation happen as the last thing before > the > record is saved or the user closes the form or moves to the next or > preceeding record. This is actually only one of several simple validations > I > have to do after a record is added or edited. I was advised to use > BeforeUpdate as ithe most appropriate event property for this type of > proceedure. The OnCurrent event picks up the data validation error when I > go > into the record. > > "Gina Whipp" wrote: > >> Jon, >> >> I would try moving it to the On_Current event, unless there is some >> specific >> reason you want it on the Before_Update? >> >> As to what is going on... are you trying to go the next record without >> filling in the eMail? >> >> -- >> Gina Whipp >> 2010 Microsoft MVP (Access) >> >> "I feel I have been denied critical, need to know, information!" - >> Tremors >> II >> >> http://www.regina-whipp.com/index_files/TipList.htm >> >> "Jon" <Jon(a)discussions.microsoft.com> wrote in message >> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB(a)microsoft.com... >> >I have set up a simple validation proceedure on the BeforeUpdate >> >property >> >as >> > follows: >> > >> > Private Sub Form_BeforeUpdate(Cancel As Integer) >> > >> > If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EmailUpdates = -1 >> > Then >> > MsgBox "Enter an email address.", vbInformation, "Data Validation" >> > Me.txtEmail.SetFocus >> > DoCmd.CancelEvent >> > Cancel = True >> > End If >> > >> > End Sub >> > >> > The form also has a several command buttons (e.g. save, next record, >> > previous record) which trigger the validation warning as expected. >> > However >> > the OK or close button on the data validation Msgbox triggers a Runtime >> > error >> > message; either 2105 'Can't go to specified record' or 2001 'You >> > cancelled >> > previous operation', depending on the cmdButton >> > >> > The go to next record proceedure is: >> > >> > Sub Command186_Click() >> > On Error GoTo Err_Command186_Click >> > >> > DoCmd.GoToRecord , , acNext >> > >> > Exit_Command186_Click: >> > Exit Sub >> > >> > Err_Command186_Click: >> > MsgBox Err.Description >> > Resume Exit_Command186_Click >> > >> > The save record proceedure is: >> > >> > >> > Sub Command189_Click() >> > On Error GoTo Err_Command189_Click >> > >> > DoCmd.RunCommand acCmdSaveRecord >> > >> > Exit_Command189_Click: >> > Exit Sub >> > >> > Err_Command189_Click: >> > MsgBox Err.Description >> > Resume Exit_Command189_Click >> > >> > Can anyone explain why this is happening and/or how to avoid it. >> > >> > Many thanks >> >> >> . >>
From: Allen Browne on 19 Jan 2010 20:40 Form_BeforeUpdate is the right place to do record-level validation. Suggestions: 1. Remove DoCmd.CancelEvent. You don't need that as well as Cancel = True. 2. In the error handler for your command buttons, trap and ignore the error numbers related to not being able to save. In some cases, you can get 3314 and 2115 as well as 2101, so this kind of thing: Sub Command189_Click() On Error GoTo Err_Command189_Click DoCmd.RunCommand acCmdSaveRecord Exit_Command189_Click: Exit Sub Err_Command189_Click: Select Case Err.Number Case 3314, 2101, 2115 'do nothing Case Else MsgBox Err.Description End Select Resume Exit_Command189_Click End Sub 3. Consider adding the line to explicitly save the record to your 'next' button's code. This will help by avoiding other weird errors when the record cannot be saved but there's a whole queue of events waiting to run. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jon" <Jon(a)discussions.microsoft.com> wrote in message news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB(a)microsoft.com... > I have set up a simple validation proceedure on the BeforeUpdate property > as > follows: > > Private Sub Form_BeforeUpdate(Cancel As Integer) > > If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EmailUpdates = -1 Then > MsgBox "Enter an email address.", vbInformation, "Data Validation" > Me.txtEmail.SetFocus > DoCmd.CancelEvent > Cancel = True > End If > > End Sub > > The form also has a several command buttons (e.g. save, next record, > previous record) which trigger the validation warning as expected. However > the OK or close button on the data validation Msgbox triggers a Runtime > error > message; either 2105 'Can't go to specified record' or 2001 'You cancelled > previous operation', depending on the cmdButton > > The go to next record proceedure is: > > Sub Command186_Click() > On Error GoTo Err_Command186_Click > > DoCmd.GoToRecord , , acNext > > Exit_Command186_Click: > Exit Sub > > Err_Command186_Click: > MsgBox Err.Description > Resume Exit_Command186_Click > > The save record proceedure is: > > > Sub Command189_Click() > On Error GoTo Err_Command189_Click > > DoCmd.RunCommand acCmdSaveRecord > > Exit_Command189_Click: > Exit Sub > > Err_Command189_Click: > MsgBox Err.Description > Resume Exit_Command189_Click > > Can anyone explain why this is happening and/or how to avoid it. > > Many thanks
|
Next
|
Last
Pages: 1 2 3 4 Prev: How to tell what form a sub-form is in. Next: Force Sub Report to Show Even If No Data |