Data validation
in [Forms]
|
From: Jon on 22 Jan 2010 04:42 Dear Allen I've tried putting 'DoCmd.RunCommand acCmdSaveRecord' both before and after the 'DoCmd.GoToRecord , , acNext' line. Is that what you mean? When I put it before the 'acNext', the validation message from the beforeupdate property fires as expected, however on 'OK' the record saves and form goes to the next record. When placed after 'acNext', the validation message fires and on 'OK' the runtime 2105 messgae comes up. I haven't used the test you suggest as previously had no need. (I thought!). Can you provide code please? The form and query both allow additions. Here's hoping Many thanks! "Allen Browne" wrote: > Did you include the explicit save? > > Did you include the test to see if you are already at the new record? > > Is your form's AllowAdditions property set to Yes? > > If the form is based on a query or SQL statement, open that directly and see > if it's possible to add records there. (If you can't do it in the source > query, you won't be able to do it in the form based on that query.) > > -- > 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:38F1C186-33C4-49D9-9234-17443C7663D9(a)microsoft.com... > > Phew, glad you got that sorted! > > > > Unfortuantely still got problems. Allen's solution worked ok for the > > 'save' > > button on the form. However the navigation buttons all throw the '2105 > > can't > > go to specific record' error. Code below. I added 2105. Is this correct? > > > > Sub Command186_Click() > > On Error GoTo Err_Command186_Click > > > > DoCmd.GoToRecord , , acNext > > > > Exit_Command186_Click: > > Exit Sub > > > > Err_Command186_Click: > > Select Case Err.Number > > Case 3314, 2101, 2115, 2501, 2105 > > 'do nothing > > Case Else > > MsgBox Err.Description > > End Select > > Resume Exit_Command186_Click > > > > End Sub > > > > I found I had to remove both DoCmd.CancelEvent and Cancel = True on the > > validation code on the BeforeUpdate property to get it to work with the > > save > > button. > > > > With the close button it simply throws up the validation message and on > > OK, > > closes the form. I assum this is because I've removed the Cancel = True? > > > > I'm sure this should be a simple piece of standard validation... but never > > managed to get this to work!! > > > > Anymore ideas gratefully received! > > > > The code on the before update is now: > > > > Private Sub Form_BeforeUpdate(Cancel As Integer) > > > > If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EmailUpdates = -1 > > Then > > MsgBox "You must enter an email address to be able to select 'By > > Email' communications for this record.", vbInformation, "Data Validation" > > Me.EmailUpdates = False > > Me.txtEmail.SetFocus > > ElseIf (IsNull(Me.Add1) Or Me.Add1 = "") And Me.txtByPost = -1 Then > > MsgBox "You must enter an address (at least line 1) to be able to > > select 'By Post' communications for this record.", vbInformation, "Data > > Validation" > > Me.txtByPost = False > > Me.Add1.SetFocus > > End If > > > > End Sub > > > > Thanks > > > > "Gina Whipp" wrote: > > > >> Allen, > >> > >> Scenario A as you don't have the information so you can't update the > >> record, > >> puts you in a loop. So, yes, that is what I mean... And yes you are > >> right > >> about the After_Update event, my mistake... <Holding head down> > >> > >> -- > >> 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 > >> > >> "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in message > >> news:utGLIcXmKHA.6096(a)TK2MSFTNGP02.phx.gbl... > >> > An indefinite loop? Not sure I've understood the problem here, Gina. > >> > > >> > Canceling Form_BeforeUpdate means you're stuck there until you: > >> > a) correct the data so it can be saved, or > >> > b) undo the record. > >> > Is that what you mean? > >> > > >> > Assigning a value to a bound control in Form_AfterUpdate dirties the > >> > form > >> > again, so you are in an indefinite loop. > >> > > >> > Sorry: I feel like I'm missing something obvious here. > >> > > >> > -- > >> > 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. > >> > > >> > > >> > "Gina Whipp" <NotInterested(a)InViruses.com> wrote in message > >> > news:OCPNqPXmKHA.3128(a)TK2MSFTNGP02.phx.gbl... > >> >> Allen, > >> >> > >> >> I always thought putting in the Before_Update would put you in an > >> >> indefinte loop? > >> >> > >> >> -- > >> >> 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 > >> >> > >> >> "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in message > >> >> news:%23LzdTGXmKHA.5040(a)TK2MSFTNGP06.phx.gbl... > >> >>> 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 > >> >>> > >> >> > >> >> > >> > >> > >> . > >> > . >
From: Allen Browne on 22 Jan 2010 04:49 If the validation problem recurs *after* moving to the new record, you must be doing something that dirties the record as soon as you arrive. That's highly undesirable. Look for anything that's assigning a value to a bound control too early (e.g. in Form_Current.) -- 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:725516B6-24A0-4730-9C09-D489F01D70B9(a)microsoft.com... > Dear Allen > > I've tried putting 'DoCmd.RunCommand acCmdSaveRecord' both before and > after > the 'DoCmd.GoToRecord , , acNext' line. Is that what you mean? When I put > it > before the 'acNext', the validation message from the beforeupdate property > fires as expected, however on 'OK' the record saves and form goes to the > next > record. When placed after 'acNext', the validation message fires and on > 'OK' > the runtime 2105 messgae comes up. > > I haven't used the test you suggest as previously had no need. (I > thought!). > Can you provide code please? The form and query both allow additions. > > Here's hoping > > Many thanks! > > > "Allen Browne" wrote: > >> Did you include the explicit save? >> >> Did you include the test to see if you are already at the new record? >> >> Is your form's AllowAdditions property set to Yes? >> >> If the form is based on a query or SQL statement, open that directly and >> see >> if it's possible to add records there. (If you can't do it in the source >> query, you won't be able to do it in the form based on that query.) >> >> -- >> 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:38F1C186-33C4-49D9-9234-17443C7663D9(a)microsoft.com... >> > Phew, glad you got that sorted! >> > >> > Unfortuantely still got problems. Allen's solution worked ok for the >> > 'save' >> > button on the form. However the navigation buttons all throw the '2105 >> > can't >> > go to specific record' error. Code below. I added 2105. Is this >> > correct? >> > >> > Sub Command186_Click() >> > On Error GoTo Err_Command186_Click >> > >> > DoCmd.GoToRecord , , acNext >> > >> > Exit_Command186_Click: >> > Exit Sub >> > >> > Err_Command186_Click: >> > Select Case Err.Number >> > Case 3314, 2101, 2115, 2501, 2105 >> > 'do nothing >> > Case Else >> > MsgBox Err.Description >> > End Select >> > Resume Exit_Command186_Click >> > >> > End Sub >> > >> > I found I had to remove both DoCmd.CancelEvent and Cancel = True on the >> > validation code on the BeforeUpdate property to get it to work with the >> > save >> > button. >> > >> > With the close button it simply throws up the validation message and on >> > OK, >> > closes the form. I assum this is because I've removed the Cancel = >> > True? >> > >> > I'm sure this should be a simple piece of standard validation... but >> > never >> > managed to get this to work!! >> > >> > Anymore ideas gratefully received! >> > >> > The code on the before update is now: >> > >> > Private Sub Form_BeforeUpdate(Cancel As Integer) >> > >> > If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EmailUpdates >> > = -1 >> > Then >> > MsgBox "You must enter an email address to be able to select 'By >> > Email' communications for this record.", vbInformation, "Data >> > Validation" >> > Me.EmailUpdates = False >> > Me.txtEmail.SetFocus >> > ElseIf (IsNull(Me.Add1) Or Me.Add1 = "") And Me.txtByPost = -1 Then >> > MsgBox "You must enter an address (at least line 1) to be able >> > to >> > select 'By Post' communications for this record.", vbInformation, "Data >> > Validation" >> > Me.txtByPost = False >> > Me.Add1.SetFocus >> > End If >> > >> > End Sub >> > >> > Thanks >> > >> > "Gina Whipp" wrote: >> > >> >> Allen, >> >> >> >> Scenario A as you don't have the information so you can't update the >> >> record, >> >> puts you in a loop. So, yes, that is what I mean... And yes you are >> >> right >> >> about the After_Update event, my mistake... <Holding head down> >> >> >> >> -- >> >> 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 >> >> >> >> "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in message >> >> news:utGLIcXmKHA.6096(a)TK2MSFTNGP02.phx.gbl... >> >> > An indefinite loop? Not sure I've understood the problem here, Gina. >> >> > >> >> > Canceling Form_BeforeUpdate means you're stuck there until you: >> >> > a) correct the data so it can be saved, or >> >> > b) undo the record. >> >> > Is that what you mean? >> >> > >> >> > Assigning a value to a bound control in Form_AfterUpdate dirties the >> >> > form >> >> > again, so you are in an indefinite loop. >> >> > >> >> > Sorry: I feel like I'm missing something obvious here. >> >> > >> >> > -- >> >> > 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. >> >> > >> >> > >> >> > "Gina Whipp" <NotInterested(a)InViruses.com> wrote in message >> >> > news:OCPNqPXmKHA.3128(a)TK2MSFTNGP02.phx.gbl... >> >> >> Allen, >> >> >> >> >> >> I always thought putting in the Before_Update would put you in an >> >> >> indefinte loop? >> >> >> >> >> >> -- >> >> >> 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 >> >> >> >> >> >> "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in message >> >> >> news:%23LzdTGXmKHA.5040(a)TK2MSFTNGP06.phx.gbl... >> >> >>> 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 >> >> >>> >> >> >> >> >> >> >> >> >> >> >> >> . >> >> >> . >>
From: BruceM via AccessMonster.com on 22 Jan 2010 09:26 This is sort of dodging the problem, but it seems to me there are three possibilities: 1) There can be an e-mail address, but EMailUpdates are not selected (the user has an e-mail address, but prefers receiving updates by another means) 2) The presence of an e-mail address means the user will receive e-mail updates 3) There is no e-mail address In the first instance, you could hide the EMailUpdates check box until the EMail field is filled in. You would have the code to make it visible in the After Update event of the EMail text box, and in the form's Current event. In the second instance there is no need for a check box, as the presence of an E-mail address means there will be e-mail updates. In the third instance none of this matters. On another point, you could save a little coding effort by the use of Nz: If Nz(Me.txtEmail,"") = "" Then etc. End If As I said, this does not address your specific problem, but I thought I would throw it into the mix anyhow. I have been watching this thread, as I am puzzled by the problem you are having, but I have seen nothing I can add. FWIW I tend to use Me.Dirty = False to save a record, and Me.Recordset. MoveNext syntax for record navigation, but I doubt it would make a difference. In any case, an explicit save would come before the navigation line of code, as you said you have tried, so I will wait to see what happens when you try the things Allen has suggested. Jon wrote: >Dear Allen > >I've tried putting 'DoCmd.RunCommand acCmdSaveRecord' both before and after >the 'DoCmd.GoToRecord , , acNext' line. Is that what you mean? When I put it >before the 'acNext', the validation message from the beforeupdate property >fires as expected, however on 'OK' the record saves and form goes to the next >record. When placed after 'acNext', the validation message fires and on 'OK' >the runtime 2105 messgae comes up. > >I haven't used the test you suggest as previously had no need. (I thought!). >Can you provide code please? The form and query both allow additions. > >Here's hoping > >Many thanks! > >> Did you include the explicit save? >> >[quoted text clipped - 191 lines] >> >> >> . -- Message posted via http://www.accessmonster.com
From: Jon on 23 Jan 2010 19:33 Thank you everyone for your time on this. Bruce... your suggestions on logic may well makes sense and i will look at implementing them when basic problem is solved. Do your code suggestions for navigation and saving have any particular advantages? On the core problem I may now add to the mystery.... or clarify. I have now started from scratch with a new simple form with a text field, check box and 'move to next record' button created by wizard. The Form also has the standard access form navigation buttons at the bottom. Its about as basic a form as possible and the data validation should be a pretty basic piece of code... I thought! The only code behand the form is now: Private Sub Command2_Click() On Error GoTo Err_Command2_Click DoCmd.RunCommand acCmdSaveRecord Me.Recordset.MoveNext Exit_Command2_Click: Exit Sub Err_Command2_Click: Select Case Err.Number Case 3314, 2101, 2115, 2501, 2105 'do nothing Case Else MsgBox Err.Description End Select Resume Exit_Command2_Click End Sub Private Sub Form_BeforeUpdate(Cancel As Integer) If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EMailMailings = -1 Then MsgBox "You must enter an email address to be able to select 'By Email' communications for this record.", vbInformation, "Data Validation" Me.EMailMailings = False Me.txtEmail.SetFocus End If End Sub The standard access form navigation buttons all work as expected with the beforeupdate code. The 'next record' button on the form however still does not work as expected. The msg fires and the 'ok' takes the form to the next record. By inserting breakpoints, what appears to be happening is that the code never goes to the error handling section. Instead it just exits on the Exit Sub line Very puzzzled now! "BruceM via AccessMonster.com" wrote: > This is sort of dodging the problem, but it seems to me there are three > possibilities: > > 1) There can be an e-mail address, but EMailUpdates are not selected (the > user has an e-mail address, but prefers receiving updates by another means) > 2) The presence of an e-mail address means the user will receive e-mail > updates > 3) There is no e-mail address > > In the first instance, you could hide the EMailUpdates check box until the > EMail field is filled in. You would have the code to make it visible in the > After Update event of the EMail text box, and in the form's Current event. > In the second instance there is no need for a check box, as the presence of > an E-mail address means there will be e-mail updates. > In the third instance none of this matters. > > On another point, you could save a little coding effort by the use of Nz: > > If Nz(Me.txtEmail,"") = "" Then > etc. > End If > > As I said, this does not address your specific problem, but I thought I would > throw it into the mix anyhow. I have been watching this thread, as I am > puzzled by the problem you are having, but I have seen nothing I can add. > FWIW I tend to use Me.Dirty = False to save a record, and Me.Recordset. > MoveNext syntax for record navigation, but I doubt it would make a difference. > In any case, an explicit save would come before the navigation line of code, > as you said you have tried, so I will wait to see what happens when you try > the things Allen has suggested. > > Jon wrote: > >Dear Allen > > > >I've tried putting 'DoCmd.RunCommand acCmdSaveRecord' both before and after > >the 'DoCmd.GoToRecord , , acNext' line. Is that what you mean? When I put it > >before the 'acNext', the validation message from the beforeupdate property > >fires as expected, however on 'OK' the record saves and form goes to the next > >record. When placed after 'acNext', the validation message fires and on 'OK' > >the runtime 2105 messgae comes up. > > > >I haven't used the test you suggest as previously had no need. (I thought!). > >Can you provide code please? The form and query both allow additions. > > > >Here's hoping > > > >Many thanks! > > > >> Did you include the explicit save? > >> > >[quoted text clipped - 191 lines] > >> >> > >> . > > -- > Message posted via http://www.accessmonster.com > > . >
From: John W. Vinson on 23 Jan 2010 19:56 On Sat, 23 Jan 2010 16:33:01 -0800, Jon <Jon(a)discussions.microsoft.com> wrote: >Private Sub Form_BeforeUpdate(Cancel As Integer) > > If (IsNull(Me.txtEmail) Or Me.txtEmail = "") And Me.EMailMailings = -1 >Then > MsgBox "You must enter an email address to be able to select 'By >Email' communications for this record.", vbInformation, "Data Validation" > Me.EMailMailings = False > Me.txtEmail.SetFocus > End If > >End Sub > >The standard access form navigation buttons all work as expected with the >beforeupdate code. The 'next record' button on the form however still does >not work as expected. The msg fires and the 'ok' takes the form to the next >record. By inserting breakpoints, what appears to be happening is that the >code never goes to the error handling section. Instead it just exits on the >Exit Sub line > >Very puzzzled now! There is no error, so you won't go to the error section (which, as far as that goes, does not exist). What you left out is setting the Cancel argument to True when you discover a problem. Doing so will prevent the record from being saved and allow the setfocus to take effect. Just put a line Cancel = True on any line between the Then and the End If. -- John W. Vinson [MVP]
First
|
Prev
|
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 |