Prev: How to tell what form a sub-form is in.
Next: Force Sub Report to Show Even If No Data
From: Gina Whipp on 19 Jan 2010 20:57
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 19 Jan 2010 21:19
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: Gina Whipp on 19 Jan 2010 21:56
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: Jon on 21 Jan 2010 19:40
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 21 Jan 2010 23:27
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
>> >>>
>> >>
>> >>
>>
>>
>> .
>>
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