How to let the cursor stay implace?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, everyone,

I have developed a Function to check the input TxtBox in order to prevent
user from inputing incorrect data. The TxtBox is to receive 7 digits in
String type. My problem is if I put this Function in the BeforeUpdate of the
TxtBox, I will encounter an erro message saying that I can not store
something in it. If I put this in the AfterUpdate of the TxtBox, everything
is fine except the cursor will jump to the next TxtBox after the error
message. That is, in this case I'd like to let the cursor stay in place to
let the user key in the correct data. Can anyone help me solve this problem?
Thank you.

Public Function (strData As String) As String
If Nz(strData, "") = "" Or strData = "" Then
Exit Function
ElseIf strData Like "*[!0-9]*" Then
MsgBox "You must key in 0 to 9"
Exit Function
ElseIf Len(strData) > 7 Then
MsgBox "You key in more than 7 digits"
Exit Function
ElseIf Len(strData) < 7 Then
strData = Format(strData, "0000000")
ValidData = strData
End If
End Function
 
I'd advise an input mask here. You can prevent entry of alpha
characters, and restrict the length, in one go, using "9999999". See the
Help on InputMask.

If you don't want that, I came up with this:

You can use the ValidationRule property of the control to call your
function (set the property to =isValid() ). It should return True for
valid data, False for invalid. You can even set the ValidationText
property of the control from this function, as I have just tested.
If the validation fails, the validationtext will be displayed. As a user
I'd appreciate not to know that something is wrong, but how it should be
corrected. So I propose this air coded validation function, watch line wrap:

function isValid() as boolean
dim i as integer'to scan the string
dim bRes as boolean'will keep the function result
dim cInvalid as string' we'll gather all messages here
bRes = true'initially, until we discover an error
with screen.activecontrol
if len(.value)>7 then
cInvalid="The entry is too long. You can enter up to 7 numerical digits."
bres=false
end if
'test if digits only
for i=1 to len(.value)
if instr("0123456789",mid(.value,i,1))>0 then
cInvalid = cInvalid & " '" & mid(.value,i,1) & "' is not a valid
character. Use only numerical digits."
bRes=false
exit for
end if
next
if not bres then
.validationtext = cInvalid
end if
end with
isValid = bRes
end function

Nice little feature is that this will work for the active control; you
don't have to explicitly put the control name in code.
 
I have developed a Function to check the input TxtBox in order to
prevent user from inputing incorrect data. The TxtBox is to receive 7
digits in String type. My problem is if I put this Function in the
BeforeUpdate of the TxtBox, I will encounter an erro message saying
that I can not store something in it
Click to expand...

The code you quoted was not valid VBA, so this is how I am guessing your
original code goes... there are still a number of problems:

Public Function ValidData (strData As String) As String

If Nz(strData, "") = "" Or strData = "" Then
Exit Function

ElseIf strData Like "*[!0-9]*" Then
MsgBox "You must key in 0 to 9"
Exit Function

ElseIf Len(strData) > 7 Then
MsgBox "You key in more than 7 digits"
Exit Function

ElseIf Len(strData) < 7 Then
strData = Format(strData, "0000000")
ValidData = strData

End If
End Function
Click to expand...


First of all, most of the Exit functions fail to specify a return value.
A useful default would be to put ValidData=strData before the If
statement.

The Exits are unneccessary anyway, since there is no processing after the
EndIf.

The first test is slow and unneccessary: since you are passing a string,
it cannot be Null (actually I guess this is a bug in waiting), and a zero
length string will be caught later on anyway.


Now, your problem is that you cannot set the .text or .value property of
a bound control while it is being validated or afterwards. You can,
however, use the Exit event to perform this kind of validation-override:

private sub MyControl_Exit(cancel as integer)
dim usTemp as string

' check the value
' nb .Text property is String not Variant
usTemp = ValidData(MyControl.Text)

' see if it failed or not
if usTemp = MyControl.Text Then
' do nothing, don't dirty the data

else
MyControl.Text = usTemp
' give the user another chance to do it right
Cancel = True

end if
end sub


The only thing you can do in the BeforeUpdate event is to Undo or leave
the thing alone: you do, however, get the chance to prevent the Save and
leave the focus on the control by setting the Cancel argument. Otherwise
there would be opportunity for horrendous recursion problems if you set
the .Value to something that would fail its own test, if you see what I
mean.

Hope that helps


Tim F
 
Hi Bas,

Thank you very much for your reply. I’ll test your code. I didn’t use the
InputMask because with it the user has to key in exactly 7 digits. If the
input data has only 6 digits then the user has to add a “0†to the front
himself. What I try to do is if the user key in 6 digits, a “0†is added to
the front automatically.

I look through the codes you give me, I find that in the second part, that
is the part for testing digits only, you have put exit for after bRes=false.
But in the first part, that is the part for testing the length of the input
string, you didn’t put exit for. Why? I didn’t test your code yet.

Thank you very much once again.
 
Hi Tim,

Thank you for your reply. I have once wanted to write it as a Sub, but a Sub
will not return a value. I think the problems come from I want this Function
to do two different jobs, that is to validate the input string and to add a
“0†to the front of the input string if it is <7 digits and then returns the
result. For the first part, if the validation is invalid I'd like the cursor
to stay there. In this case, nothing needs to be returned. For the second
part, an modified string is returned. In this case, it is alright for the
cursor to jump to the next TxtBox because the input is correct and finished.

The TxtBox is bound to a field that can have data or not. If the user keys
in something then finds that he input a wrong record then erase it with the
backspace key. When he press enter he will encounter an error message saying
that the improper use with ‘NULL’. And this can be avoided by adding If
Nz(strData, "") = "" Or strData = "" just as my first test.

Thank you very much once again.

--
Jeff


"Tim Ferguson" 來函：
I have developed a Function to check the input TxtBox in order to
prevent user from inputing incorrect data. The TxtBox is to receive 7
digits in String type. My problem is if I put this Function in the
BeforeUpdate of the TxtBox, I will encounter an erro message saying
that I can not store something in it
Click to expand...

The code you quoted was not valid VBA, so this is how I am guessing your
original code goes... there are still a number of problems:

Public Function ValidData (strData As String) As String

If Nz(strData, "") = "" Or strData = "" Then
Exit Function

ElseIf strData Like "*[!0-9]*" Then
MsgBox "You must key in 0 to 9"
Exit Function

ElseIf Len(strData) > 7 Then
MsgBox "You key in more than 7 digits"
Exit Function

ElseIf Len(strData) < 7 Then
strData = Format(strData, "0000000")
ValidData = strData

End If
End Function
Click to expand...


First of all, most of the Exit functions fail to specify a return value.
A useful default would be to put ValidData=strData before the If
statement.

The Exits are unneccessary anyway, since there is no processing after the
EndIf.

The first test is slow and unneccessary: since you are passing a string,
it cannot be Null (actually I guess this is a bug in waiting), and a zero
length string will be caught later on anyway.


Now, your problem is that you cannot set the .text or .value property of
a bound control while it is being validated or afterwards. You can,
however, use the Exit event to perform this kind of validation-override:

private sub MyControl_Exit(cancel as integer)
dim usTemp as string

' check the value
' nb .Text property is String not Variant
usTemp = ValidData(MyControl.Text)

' see if it failed or not
if usTemp = MyControl.Text Then
' do nothing, don't dirty the data

else
MyControl.Text = usTemp
' give the user another chance to do it right
Cancel = True

end if
end sub


The only thing you can do in the BeforeUpdate event is to Undo or leave
the thing alone: you do, however, get the chance to prevent the Save and
leave the focus on the control by setting the Cancel argument. Otherwise
there would be opportunity for horrendous recursion problems if you set
the .Value to something that would fail its own test, if you see what I
mean.

Hope that helps


Tim F
Click to expand...
 
Jeff said:
Hi Bas,

Thank you very much for your reply. I’ll test your code. I didn’t use the
InputMask because with it the user has to key in exactly 7 digits.
Click to expand...

I recognize that. But your user doesn't have to type 7 characters. You
can reformat the field in the BeforeUpdate event:

theField = right("0000000" & theField, 7)

This will prepend seven zeroes to the field, and then takes the last 7
characters. That way the user input ends up as you'd expect, with
prefixed zeroes if need be.
I look through the codes you give me, I find that in the second part, that
is the part for testing digits only, you have put exit for after bRes=false.
But in the first part, that is the part for testing the length of the input
string, you didn’t put exit for. Why? I didn’t test your code yet.
Click to expand...

The first part does a single test. There is little point Exiting an If.
The second part loops through the string, because every character must
be tested. Upon meeting the first non-numerical character, isValid must
fail, so there is no need to test the rest of the string.
The speed gain by the Exit is not exactly enormous here :-) I added it
more for completeness. I prefer to exit any loop when its purpose has
been met.

You're most welcome.
 
that the improper use with ‘NULL’. And this can be avoided by adding If
Nz(strData, "") = "" Or strData = "" just as my first test.
Click to expand...

I must point out that both the empty string and a Null value will be ""
after Nz(). The second clause of the Or is therefore completely redundant.
 
I think the problems come from I want
this Function to do two different jobs, that is to validate the input
string and to add a “0†to the front of the input string if it is
<7 digits and then returns the result.
Click to expand...

Reread my response: you cannot write back to the control from the
BeforeUpdate event, but can from within the Exit event.
When he press enter he will encounter an
error message saying that the improper use with ‘NULL’.
Click to expand...

Reread my response. I know perfectly well that a .Value can be a Null. It
is you who declared "strData As String": in fact, if you do pass it an
empty control.Value value then it'll fail right there with a Data Type
error. It's up to you -- you can either pass a .Value into a Variant, or
a .Text into a String, but putting a .Value into a String _will_ fail
sooner or later. You just have to understand how to use data types in
order to do anything useful in VB(A).


B Wishes


Tim F
 
Thank you all for your precious opinions. Actually I am still learning
VBA.
Click to expand...

That's okay: the first point about writing to controls during the
BeforeUpdate event is not particularly well documented: it's one of those
things you find out during debugging and then it makes sense later on
when you think about it.

The second point about data types is something that you will really have
to get a handle on, though.

Function Something (SomeString As String) As etc

means that you can only pass a String value to the function -- the
declaration in the brackets works exactly the same as a Dim. A string
cannot hold a Null, nor can it hold a Missing or Empty value etc. If you
need to be able to pass one of these values (for example, the contents of
an empty text box control) then you simply have to use a Variant: that is
what Variants are for. Alternatively, you could move the checking
earlier, so you get

If IsNull(MyControl.Value) Then
DoSomethingElse
Else
Call Something(MyControl.Value) ' since it now has to be a string
End If

There are advantages and disadvantages of either approach: it's up to you
as the designer and analyst to decide what you need.

Hope that helps


Tim F
 
Hi Tim,

Glad to hear from you again. You have answered to the point that I really
need. I have tested several ways to cope the problem of "Null" in these few
days but failed. I decide to separate my validate Function into two
functions. The first one is to add a "0" if the user key in less than 7
digits. The second one is to validate this string if it is what I really
need. The string must be exactly in 7 digits. And now you add the solution of
"Null" for me, it's great. I am appreciated.
 
Back
Top