Validate a field

[tweety]

Hi

I have a few forms that are used to enter customer details and product details. i would like to ensure a field is entered and if not the display an error message and then force the user to go back to that field and correct the problem.

I have tried placing validation in the table and the forms, but wasnt too happy with the error message as its not always displaying my message i typed in






i dont the idea of having a help button????
i have had alook online but cant apply it to my system, any ideas?

thanks

[RuralGuy]

I do all of my field validation in the BeforeUpdate event of the control and the BeforeUpdate event of the form.

[tweety]

can you give me an example please, of the code.
i would like to have it so that a field must have some data and not missed out, if it is missed out then a message box appears and set focus back to that control

[RuralGuy]

Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.MyControl & "") = 0 Then
   Cancel = True
   MsgBox "Please complete this control", vbCritical + vbOKOnly
   Me.MyControl.SetFocus
End If
End Sub

...using your control name of course.

[tweety]

Thanks for the code, i have put it into the before update on the form event.


i have 3 other feilds that need the same error messages too, if you can help out
1. a town field which has a default value set from the table.
2. post code\zip code with >LA99\ 0LL;0;_ as the input mask
3. contact number with \(9999") "0009000;;_ as the input mask

what code is needed to move these to vba coding? as i can have the error messages (with out help button)

missingling also gave me this code Me.ControlName.Text = StrConv(Me.ControlName.Text, vbProperCase) to convert the text to proper case, this is in the control name on exit event, shall i keep it there or should it be moved into your code?

thank you

[RuralGuy]

I would convert to ProperCase in the BeforeUpdate event of the control and use the .Value property rather than the .Text property. As for the other controls, just add another If...EndIf to the code in the Form's BeforeUpdate event to test for validity.

[Missinglinq]

When validating multiple Fields, in the Form_BeforeUpdate event, you have to add one more line, as shown below in red:

If Len(Me.MyControl & "") = 0 Then
   Cancel = True
   MsgBox "Please complete this control", vbCritical + vbOKOnly
   Me.MyControl.SetFocus
   Exit Sub 
End If

If you don't have the Exit Sub line, Access will drop immediately from one If...Then to the next, not stopping at the first offending Control, before moving on to the next If...Then, despite the MyControl.SetFocus line of code.

Linq ;0)>

[tweety]

thanks for that, my code looks like.....

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.ShopName & "") = 0 Then
Cancel = True
MsgBox "Please enter a shop name", vbCritical + vbOKOnly
Me.ShopName.SetFocus
Exit Sub
End If

If Len(Me.ContactName & "") = 0 Then
Cancel = True
MsgBox "Please enter a contact name", vbCritical + vbOKOnly
Me.ContactName.SetFocus
Exit Sub
End If

If Len(Me.AddressLine1 & "") = 0 Then
Cancel = True
MsgBox "Please enter address line 1", vbCritical + vbOKOnly
Me.AddressLine1.SetFocus
Exit Sub
End If
End Sub

[tweety]

i can change where the code is from the exit event to beforeupdate, but i didnt understand the value and text bit? should it now be
Me.ShopName.Value = StrConv(Me.ShopName.Value, vbProperCase) rather then
Me.ShopName.Text = StrConv(Me.ShopName.Text, vbProperCase)

can you please write the code out for me to validate the zip code\postcode, contact number and town please (town default value is Leicester)

thanks again

[RuralGuy]

Yes, change .Text to .Value as you have shown. You write the code for the other three controls and try it and let us know how it works.

[tweety]

i have changed the .text to .value and placed the code in the beforeupdate event, but now iam getting this error message lol
I have checked the system and there is no validation in the tables or the form, everything is in the vba coding



any ideas???
iam trying to write some code out for the other fields.

thanks people

[Missinglinq]

You can check input in a Control's BeforeUpdate event, but you cannot do something like changing the formatting of that data in this event, or you'll get the error you just posted. Don't remember your previous thread, or why OnExit was used, at that time, but you can move the formatting to the Control's AfterUpdate event, in which case you will need to change the .Text to .Value:

Me.ShopName.Value = StrConv(Me.ShopName.Value, vbProperCase)

which can be simplified to

Me.ShopName = StrConv(Me.ShopName, vbProperCase)

Linq ;0)>

[tweety]

i managed to get this from the net
Me.Town.Value = Leicester

after testing it, when you create a new record it comes up (town displays leicester) but once you tab into the field and tab out again (as its already filled) it gets cleared?

any ideas, also for the other 2 fields iam not sure where to start from, if you can help out


thanks

[tweety]

Is this ok to validate a phone number????

If Not IsNumeric(ContactNumber1) Then
Cancel = True
MsgBox "Please enter contact number 1", vbCritical + vbOKOnly
Me.txtContactNumber1.SetFocus
End If
If Len(ContactNumber1) <> 11 Then
Cancel = True
MsgBox "Please enter contact number 1", vbCritical + vbOKOnly
Me.txtContactNumber1.SetFocus
Exit Sub
End If

[tweety]

I have played around with the leicester as the defualt value for leicester, i have moved the code to the event when a new customer button is clicked and it seems to stay there now without being moved, on one more to go now, the postcode (zip code)