Data validation

[Jon]

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

 

[Gina Whipp]

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]

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]

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

 

[Allen Browne]

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.

 

[Gina Whipp]

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]

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.

 

[Gina Whipp]

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

 

[Jon]

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

 

[Allen Browne]

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.)

 

[Jon]

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]

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.)

 

[Jon]

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:

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.

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]

.

 

[John W. Vinson]

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.

 

[Jon]

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]

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

 

[Jon]

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.