Data validation 01-19-10

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
0
Utf
1/19/2010 11:35:02 PM
access.formscoding 7493 articles. 0 followers. Follow

17 Replies
898 Views

Similar Articles

[PageSpeed] 40

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@discussions.microsoft.com> wrote in message 
news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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 


0
Gina
1/20/2010 12:00:29 AM
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@discussions.microsoft.com> wrote in message 
> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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 
> 
> 
> .
> 
0
Utf
1/20/2010 1:11:01 AM
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@discussions.microsoft.com> wrote in message 
news:4D25FD95-126D-4730-AB1F-DEA54DC12BF3@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@discussions.microsoft.com> wrote in message
>> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
>>
>>
>> .
>> 


0
Gina
1/20/2010 1:35:38 AM
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@discussions.microsoft.com> wrote in message 
news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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 

0
Allen
1/20/2010 1:40:39 AM
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@SeeSig.Invalid> wrote in message 
news:%23LzdTGXmKHA.5040@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@discussions.microsoft.com> wrote in message 
> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
> 


0
Gina
1/20/2010 1:57:32 AM
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@InViruses.com> wrote in message 
news:OCPNqPXmKHA.3128@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@SeeSig.Invalid> wrote in message 
> news:%23LzdTGXmKHA.5040@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@discussions.microsoft.com> wrote in message 
>> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
>>
>
> 
0
Allen
1/20/2010 2:19:41 AM
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@SeeSig.Invalid> wrote in message 
news:utGLIcXmKHA.6096@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@InViruses.com> wrote in message 
> news:OCPNqPXmKHA.3128@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@SeeSig.Invalid> wrote in message 
>> news:%23LzdTGXmKHA.5040@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@discussions.microsoft.com> wrote in message 
>>> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
>>>
>>
>> 


0
Gina
1/20/2010 2:56:23 AM
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@SeeSig.Invalid> wrote in message 
> news:utGLIcXmKHA.6096@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@InViruses.com> wrote in message 
> > news:OCPNqPXmKHA.3128@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@SeeSig.Invalid> wrote in message 
> >> news:%23LzdTGXmKHA.5040@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@discussions.microsoft.com> wrote in message 
> >>> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
> >>>
> >>
> >> 
> 
> 
> .
> 
0
Utf
1/22/2010 12:40:01 AM
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@discussions.microsoft.com> wrote in message 
news:38F1C186-33C4-49D9-9234-17443C7663D9@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@SeeSig.Invalid> wrote in message
>> news:utGLIcXmKHA.6096@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@InViruses.com> wrote in message
>> > news:OCPNqPXmKHA.3128@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@SeeSig.Invalid> wrote in message
>> >> news:%23LzdTGXmKHA.5040@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@discussions.microsoft.com> wrote in message
>> >>> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
>> >>>
>> >>
>> >>
>>
>>
>> .
>> 
0
Allen
1/22/2010 4:27:00 AM
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@discussions.microsoft.com> wrote in message 
> news:38F1C186-33C4-49D9-9234-17443C7663D9@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@SeeSig.Invalid> wrote in message
> >> news:utGLIcXmKHA.6096@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@InViruses.com> wrote in message
> >> > news:OCPNqPXmKHA.3128@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@SeeSig.Invalid> wrote in message
> >> >> news:%23LzdTGXmKHA.5040@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@discussions.microsoft.com> wrote in message
> >> >>> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
> >> >>>
> >> >>
> >> >>
> >>
> >>
> >> .
> >> 
> .
> 
0
Utf
1/22/2010 9:42:03 AM
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@discussions.microsoft.com> wrote in message 
news:725516B6-24A0-4730-9C09-D489F01D70B9@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@discussions.microsoft.com> wrote in message
>> news:38F1C186-33C4-49D9-9234-17443C7663D9@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@SeeSig.Invalid> wrote in message
>> >> news:utGLIcXmKHA.6096@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@InViruses.com> wrote in message
>> >> > news:OCPNqPXmKHA.3128@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@SeeSig.Invalid> wrote in message
>> >> >> news:%23LzdTGXmKHA.5040@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@discussions.microsoft.com> wrote in message
>> >> >>> news:9E295B84-9729-47FD-8F65-5C74A5CDE5EB@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
>> >> >>>
>> >> >>
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>> .
>> 
0
Allen
1/22/2010 9:49:21 AM
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

0
BruceM
1/22/2010 2:26:54 PM
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
> 
> .
> 
0
Utf
1/24/2010 12:33:01 AM
On Sat, 23 Jan 2010 16:33:01 -0800, Jon <Jon@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]
0
John
1/24/2010 12:56:13 AM
Thank you John. 

Yes... I had left it out and of course that does mean that the form doesn't 
move on to the next record! I had cut and paste from one of many versions. 
Although inserting cancel = true does solve the record navigation issue I 
still have the original problem of Error 2501 (Run cmd action cancelled). 

I am now down to testing on a new database with 1 table, 3 fields, two 
records just to make sure that there is no extraneous coding etc!

What is so odd is that the standard access form navigation buttons work 
absolutely as expected. It is the navigation button created by the wizard 
that do not work. Equally, a save record button created by the wizard doesn't 
work with the validation code as expected. (error 2501 again)

Any more ideas?

"John W. Vinson" wrote:

> On Sat, 23 Jan 2010 16:33:01 -0800, Jon <Jon@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]
> .
> 
0
Utf
1/24/2010 11:41:01 PM
I use MoveNext because it is more compact, and it seems to do the same thing
as DoCmd syntax for basic navigation.  There are arguments available for
DoCmd.GoToRecord that may not be available for MoveNext.  I have searched for
a while on this topic, but it is difficult to frame the search string, so I
am not much the wiser for my investigations, except to say that they seem to
confirm that for basic navigation there is no real difference.  If somebody
with definitive information on the topic can provide clarification I would be
interested in hearing about it.


Jon wrote:
>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!
>
>> This is sort of dodging the problem, but it seems to me there are three
>> possibilities:
>[quoted text clipped - 48 lines]
>> >> >> 
>> >> .

-- 
Message posted via http://www.accessmonster.com

0
BruceM
1/25/2010 12:47:39 PM
Dear John

Sorry! No go. All code for form is as now below; its very similar to Allen's 
original suggestion. (pasted with save command commented out) With the Save 
command back in I get 2501 (RunCmd action was cancelled). Without it I get 
2105 (Can't go to specific record). This occurs with or without error 
trapping code... ie it makes no difference. As I say the standard access 
navigation buttons work fine with the beforeupdate property. Can soemoene 
explain why?

Could I be missing a libaray reference?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If (IsNull(Me.Email) Or Me.Email = "") And Me.ByEmail = -1 Then
        MsgBox "You must enter an email address to be able to select 'By 
Email' communications for this record.", vbInformation, "Data Validation"
        Me.ByEmail = False
        Me.Email.SetFocus
        Cancel = True
    End If
        
End Sub

Private Sub Command8_Click()
On Error GoTo Proc_Error

    'DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNext

Proc_Exit:
  Exit Sub

Proc_Error:
 Select Case Err.Number
    Case 2105 
        Resume Proc_Exit
    Case Else
        MsgBox "Error " & Err.Number & " in MySubName:" _
              & vbCrLr & Err.Description
        Resume Proc_Exit
  End Select
End Sub

This very weird.

"John W. Vinson" wrote:

> On Sun, 24 Jan 2010 15:41:01 -0800, Jon <Jon@discussions.microsoft.com> wrote:
> 
> > I 
> >still have the original problem of Error 2501 (Run cmd action cancelled). 
> 
> Trap and ignore the error:
> 
> On Error GoTo Proc_Error
> <your code>
> Proc_Exit: 
>   Exit Sub
> Proc_Error:
>  Select Case Err.Number
>     Case 2501
>         Resume Proc_Exit
>     Case Else
>         MsgBox "Error " & Err.Number & " in MySubName:" _
>               & vbCrLr & Err.Description
>         Resume Proc_Exit
>   End Select
> End Sub
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/25/2010 10:53:01 PM
Reply:

Similar Artilces:

WM_QUERYENDSESSION and saving data through a worker thread
Hello I have an application that uses a worker thread to save/load data. I'm wondering what is the best reaction to WM_QUERYENDSESSION in my case. I have to possible scenarios: 1. When WM_QUERYENDSESSION comes fire the thread and wait for it to end. Only then return TRUE from WM_QUERYENDSESSION handler. The problem is that I will get nusty dialog that my application "is not responding". 2. When WM_QUERYENDSESSION comes fire the thread and return FALSE from the handler. When thread is done force application to end. But this way I will probably prevent Windows from closing,...

Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different data. Is there any way I can simply copy one set of data from one graph and paste it into another graph so that I can avoind going through all the hassle plotting each curve again? I want to have graphs showing different combinations of the same data and have hundreds of curves to plot so this could be a huge timesaver... Cheers. -- Alan_Partridge ------------------------------------------------------------------------ Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295 V...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Document map 05-12-10
The document map for one of my documents has stopped displaying headings from the beginning of the document and instead displays headings from a point within it. The headings that aren't displayed are correctly formatted as Level 1. And I've entered and exited Outline view, with, unfortunately, no benefit. What might the solution be please? Are the headings that are shown the document map in the same style? If so, try reapplying the style to the headings that are not showing up. If not, make sure that it is the paragraph outline level that you have set to 1 (& if it ...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

Subform question 04-09-10
I have a form (Form1) that contains a subform (Subform1). Within this subform I have a combo box which, depending on what is chosen, pops up another form (Popup1)for additional information. I need this additional information in the form that pops up to be 'linked' with the subform. The problem I am running into is that when the user enters information in Popup1, the table has not been populated witht he data that is in the subform so there is no record to 'link' to. What is the best way to force te esubform to pass its information to the table? Thanks i...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

sending mail 05-20-10
The message could not be sent. The authentication setting might not be correct for your outgoing e-mail [SMTP] server. For help solving this problem, go to Help, search for "Troubleshoot Windows Mail", and read the "I'm having problems sending e-mail" section. If you need help determining the proper server settings, please contact your e-mail service provider. The rejected e-mail address was 'michael.porth@bafin.be'. Subject 'Hello', Account: 'pop-server.tampabay.rr.com', Server: 'smtp-server.tampabay.rr.com', Protocol: SMT...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Upgrade GP 9.0 SP2 to GP 10.0 Advice
Hi, We are planning our upgrade of GP9.0 SP2 to GP10.0. We are running SQL 2000 and would like to also upgrade to SQL 2005. I am using the following url (and read some threads on this forum): https://mbs.microsoft.com/customersource/support/knowledgebase/hottopics/hot_topic_updatingmicrosoftdynamicsgp10.htm?printpage=false (page: Updating to Microsoft Dynamics GP 10.0) The chart Microsoft provides says I can go from 9.00.0281 (which is SP2) to 10.0 or any patch install. What does "any patch install" mean? Does this mean I can't go directly to 10.0 Feature Pack 1? Would i...

Add data to cell w/o loosing initial data
I would like to know if there is a way to add data to data without retyping. For example I have a colum of 18015555555 and I want to add [rfax:(cell #)@/fn=(phone number)] So I would like to add the brackets - copy from a cell - @/fn= and not loose the data already in the spread sheet. Example 2. Add [rfax:company name@fn/=(saved data here) then close bracket. So I want to add data to cells without loosing the data already in the cells. I have about 600 of them to do and I really don't want to do each one by hand. Please let me know if anyone knows how to accomplish this. Tha...

Removing filters from data
Hi. I have recorded a macro to remove filters from data lasts in advance of performing other actions. However if the data is unfiltered the macro falls over with the message Run time error '1004' ShowAllData method of Worksheet class failed. I think I need some sort of if error continue code or something to check filtering first. I would be grateful if someone could point me in the right direction please. Hi Philip Try If Activesheet.Filtermode Then ActiveSheet.ShowAllData -- Jacob "Philip J Smith" wrote: > Hi. > > I have re...

Looking up and matching data
I have two sets of data with the same information but not in the same order and am trying to match the data. In each data set I have 10 pools containing 100 loans. Each pool has a unique ID and each loan within the applicable pool has an ID of 1 to 100. I need to look up the Pool ID, then look up the loan ID so that I can extract the property type information from a third column. The Pool ID and property type is text but the loan ID is a number. I am struggling to put together the right combination of formulas to give the property type for each loan within each pool. Any suggestion...

invisible listbox data
I have developed software in Acces XP that is distributed to two different locations and I have noticed some odd behaviour at one of the locations. Sometimes the data in listboxes or combo boxes is invisible. The data IS PRESENT because you can select and use the records as before (although you can't see which ones you're selecting). The combo boxes are poplulated and have the correct dropdown length for the records one would expect. The listboxes have scroll bars where one would expect a scrollbar and multiselcetion is possible where apropriate. I have played around with th...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...

create a letter with the data fron work sheet
i have a data sheet for the order.like people order staff lik tea,coffee...etc,i ve data including date,name,department,.....etc. when i put the data i want a letter format which picks up the data fro the sheet then i can send it to the person who made the order as confirmatino letter. like it the letter starts dear "......"(it could pick the name fro c1) also how can i create this letter? does it have to be in excel ?? help pleaseeeeeeeeeeeeeeeeeeee!!!!!!!!!!:( :( :( : -- Message posted from http://www.ExcelForum.com As answered in microsoft.public.excel and microsoft.public....

excel 2007
I have data that looks something like this account#, invoice amount 1, 50 1, 70 1, 80 2, 10 2, 50 2, 52 2, 50 3, 10 4, 30 I need a way to combine all the invoice totals for each account so I have results like this 1, 200 2, 162 3, 10 4, 30 Any suggestions on how to go about doing this? I thought I had done something similar to this with filters before in Excel 2003, but I can't figure out how I did it. Thanks! You can use subtotal or a pivot table in the pivot put the account numbers in the row field and the invoice amount in the data, in subtotal use at each change in account numb...

Validation Problem #2
I have a schema file PDDSch.xsd which validates the PDD.xml file. the rootnodes are as shown below PDD.xml <PDD xmlns="http://tempuri.org/PDDSch.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-Instance" > PDDSch.xsd <xs:schema id="PDD" targetNamespace="http://tempuri.org/PDDSch.xsd" xmlns="http://tempuri.org/PDDSch.xsd" xmlns:mstns="http://tempuri.org/PDDSch.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="quali...

Copying of non-consecutive, columnar data into consecutive-columnar cells
Hi: I have data located at a1:a9, then a:10 is blank; then, I have 9 more lines of data from a11:a19, and a20 is blank... a1, a11, a21...are all the texts/numbers that I want printed on a different page. But here's the catch: I'd like to print it at locations a1, a2, a3... Specifically, the data that I have appears like this on THIS IS ON SHEET 1: A1: prefix: Mr A2: emailAddress: john.smith@1.com A3: firstName: John A4: lastName: Smith A5: company: ABC Company A6: A7: prefix: Mrs A8: emailAddress: kim.davids@2.com A9: firstName: Kim A10: lastName: Davids A11: company: X...

Nested if 10 / Vlookup Therapist
I am working on a Scheduling program for a counseling center. Thi center has 10 + therapists and 20 + clients. The plan is for th manager to only have to enter the clients name into the therapist page and to have the therapists name auto populate into the clients page. can get this to work fine with 7 therapists (the limit for nested i statements) but I cannot figure out how to get vlookup or any othe function to work. ohh by the way the or() statements are used because there are possibilities for the clients name to be entere (CLIENT/CLIENTc/CLIENTp) Code ------------------- =(IF...