Validation before saving a record 1

[RachelK]

Hi,

I would like to check that the surname and the DOB field has been populated before saving the record. I am not to sure where to put the code. I did try the form Before update event this did not work. All I would like is a message to advise the user they have not put data in these pretty important fields and give them option to say OK save the record otherwise cancel and go back into the record on the form.

I have got this far so far. Cheers Rachel.

Private Sub Surname_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrorHandler

If Me.Surname = "" Then

MsgBox "You have not entered a surname are you sure you want to go ahead and save this record", vbYesNoCancel, "Missing Surname"
Cancel = True
ElseIf Me.Surname <> "" Then
Cancel = False
End If

Exit Sub

ErrorHandler:
HandleError
Exit Sub

End Sub

 

[Crowley16]

you are putting this code in the surname textbox so it will run whenever your trying update the surname...

what you need to do is put a test code for surname, dob... into every event that would cause the form to exit or be bound to a new record or save the record...

an simpler way would be to have a command button to save the record, and then you can just put all your data validation code in there, before you actually save the record...

Procrastinate Now!

 

[RachelK]

Crowley,

Thanks for your help. Could I just not use a on exit of the form for that record check to see if the two fields are used and display a message to warn them. Thanks again Rachel

 

[Crowley16]

yes, but what about you moving to a new record without exsiting the form? i.e. using the record navigation buttons or tabing to the next record...

if you've disabled those other options then using onExit of the form is fine...

Procrastinate Now!

 

[RoyVidar]

Use the before update event of the form. It is probably the only event you are guaranteed will fire whenever a save operation is attempted. As the before update of controls, it can also be cancelled. (BTW - have you considered just making the fields required in the table?)

You'll probably seldom find a situation where a form control is "", try one of the other tests, for instance this one:

[tt] If trim$(Me.Surname & "") = "" Then[/tt]

- which will test for Null, Zero Length String and space (although space probably isn't much to worry about)

Roy-Vidar

 

[RachelK]

RoyVidar,

Thanks for your help I have this code so far:-

On Error GoTo ErrorHandler

If Trim(Me.Surname & "") = "" Then

MsgBox "You have not entered a surname are you sure you want to go ahead and save this record", vbYesNoCancel + vbExclamation, "Missing Surname"
Cancel = True
Else
Cancel = False
End If

Exit Sub

ErrorHandler:
HandleError
Exit Sub


Problem is I need to say if the user click yes cancel = False and if the user select No they don't want to save the record cancel = True. what do I need to do ? Cheers Rachel.

 

[RoyVidar]

Just as a suggestion to play with (use the msgbox function, not the method if you need some action performed based on user choice):
[tt]dim intAnswer as integer
...
intAnswer= MsgBox("You have not entered a surname are you sure you want to go ahead and save this record", vbYesNoCancel + vbExclamation, "Missing Surname")
select case intAnswer
case vbyes
' do nothing, it will save the record
case vbno
' if no - then they would probably not keep the
' unsaved changes, so undo?
me.undo
case else
' if "I don't know" let them back to amend the
' changes they've choosen not to save but not to
' undo either
cancel = true
end select[/tt]

I've included "all exits", so you should be able to "play" with different action on different user choices.

Roy-Vidar

 

[RachelK]

RodVidar,

Thanks works perfectly just one more question. I have to do this code for both Surname and DOB field would I combine the code or would I do two select statements. Cheers Rachel.

 

[RoyVidar]

What you would do, you'll have to decide. The lazy approach:

[tt]if Trim$(Me!Surname & "") = "" or Trim$(Me!dob & "") = "" Then
' one of them or both...[/tt]

I wouldn't have two selects, they would then perhaps contradict eachother (false in the first, true in the next), but perhaps test each condition too, to provide a specific message to the user relating to which control broke the validation, or use some more generic code (one attempt of such can be found here faq702-5010).

Roy-Vidar

 

[PHV]

You may also have to consider the BeforeInsert event procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244