Wrong data in a field

[Guest]

When user keys in the wrong data in a field, the wrong data will be cleared
and the a pop up message will ask the user to key in again, and the setfocus
is back to the field for the user to key in data again. I would need help
on how to code it.

 

[Guest]

Put this code in the AfterUpdate event of the Field

if Field <> "What I want" then
Beep
MsgBox "Invalid data. please enter etc.etc.", vbExclamation + vbOKOnly,
"Invalid"
Field = ""
Field.SetFocus
end if

 

[Tim Ferguson]

Put this code in the AfterUpdate event of the Field

The BeforeUpdate is often a better choice because:

it has a Cancel argument that can be used to prevent the user
moving on to another control; and

it operates before the bad data are sent to the database,
thereby triggering a dbengine ValidationRule violation.


By the way, clearing the entire field is not a very userfriendly thing to
do if they've only missed one keystroke and they have to type the whole
lot in again. Leaving the focus on the field means they can simply edit
the thing to be correct.

Hope that helps


Tim F

 

[Guest]

Hi! Dennis,

Thanks for your reply. I have made some changes as below:

Private Sub Hour_AfterUpdate()

If Field <> "What I want" Then
Beep
MsgBox "Invalid data. please enter etc.etc.", vbExclamation + vbOKOnly,
"Invalid"
Field = ""
Forms![SWITCH FORM]![NEW ARTICLES]![ForFrm]!Hour.SetFocus

End If
End Sub

When I use field.setfocus, the system will prompt an error message saying
"Require Object..". Therefore, I made some change as above. There is no
error message again, but the focus is in the next field instead of the "hour"
field.

If the user put the time "12:2_" instead of "12:12", the system will prompt
an error message. Can you modify the code above that the system will clear
the data and the cursor will point to the first position of the field for the
user to put the time info again?

If I do not put any code, and the user types the incomplete time, the system
will prompt an error message, and then the cursor will point to the last
position of the field. That is not what I require.

Thanks!

Best regards,

sc

 

[Guest]

Hi! Tim,

Thanks for your reply.

I have an error message both in the before and afterupdate event after I put
the code

 

[Tim Ferguson]

I have an error message both in the before and afterupdate event after
I put the code

Beep
MsgBox "Invalid data. please enter etc.etc.", _
vbExclamation + vbOKOnly, "Invalid"
Field = ""
Forms![SWITCH FORM]![NEW ARTICLES]![ForFrm]!Hour.SetFocus

I am assuming that this is the code you are trying to run:

1) I can never get Beep to do anything on any computer I use. Perhaps you
are having better luck!

2) msgBox looks fine.

3) IIRC, there are rules about changing the contents of fields you are in
the process of updating i.e in the BeforeUpdate and AfterUpdate events.
This makes sense, since the change would cause a new cascade of the same
events with risk of horrid unexpected recursion. You can alter the value
using the Exit event, but it's a bit late then to avoid db engine errors.

3b) in any case, simply erasing your user's entry is a pretty user-
hostile thing to do. Just refusing to move on until it's corrected should
be unfriendly enough.

3c) Access text boxes clear to Null rather than "" anyway. The correct
instruction would be Field.Value=Null but it's still illegal and
unfriendly.

4) I don't understand the access chain for the control. If the code is
running in the same form as the control, you can simply make this

Me!Hour.SetFocus

or the more legible

Me.Controls("Hour").SetFocus

4b) In the AfterUpdate and BeforeUpdate events, SetFocus will not help
you very much as they occur before Access has passed the focus on to the
next control. If you don't prevent that, using the Cancel argument, then
the focus will go to !Hour transiently and invisibly, and then get moved
on to wherever it was going anyway. You might try looking up the help
files for Order Of Events for Controls on Forms, which is pretty plain in
this area.

4c) If you are trying to find a control on a subform, you have to go via
the Form property of the subform control:

Forms("MyMainForm").Controls("subformcontrol").Form.Controls("Hour")

and note that the "subformcontrol" is the name of the control, not the
name of the form that it contains.


I think the code you are after is something like

Private Sub txtHour_BeforeUpdate(Cancel as Integer)
' note: it's a good habit to name the controls differently
' from the data fields, particularly when you are accessing
' them programmatically. Saves a bundle full of confusion
' later.

' test for validity
If txtHour.Value <> "What I want" Then
' alert the user
Beep : MsgBox "blah blah"

' prevent the update
Cancel = True

End If

' all done
End Sub

Hope that helps


Tim F