Having to click Ok twice to clear message box

[graeme34 via AccessMonster.com]

Hi
I havre a function that returns a False if the Order Number entered in a
textbox control is not numeric. But it is creating a small glitch in that I
have to press Ok twice to clear the MsgBox.
The code is as follows:
Private Function TestOrderValue() As Boolean

TestOrderValue = True
If IsNumeric(Me!txtOrderNumberChoice) Then
lngOrderNum = Me!txtOrderNumberChoice
Else
TestOrderValue = False
MsgBox "Sorry, the Order Number has to be a numeric value!", _
, "NUMERIC VALUE REQUIRED!"
Me!txtOrderNumberChoice.SetFocus
End If

End Function

The in the calling procedure I have

If TestOrderValue = False Then Exit Sub

Is there a way to prevevnt the user having to click Ok twice??

 

[Tim Ferguson]

I havre a function that returns a False if the Order Number entered in
a textbox control is not numeric. But it is creating a small glitch in
that I have to press Ok twice to clear the MsgBox.

You don't say what event is calling your code. Using SetFocus to move the
focus during some events can cause all kinds of side effects: I guess that
you may be causing a superfluous Exit event and calling your own code
twice.

In general, using the Cancel-able events (BeforeUpdate, Exit, etc) when you
want to block the user action is safer in terms of avoiding cascading other
events.

Hope that helps


Tim F

 

[graeme34 via AccessMonster.com]

Hi Tim
The event(s) that call the function are all on click events from command
buttons, as there is a fair bit of code behind the form the function is
called from a number of different cmd button on clicks. Your suggestion for
placing the code in the before update event of the text box, is something I
hadn't considered, still a newbie to this
It would certainly save on lines of code....as there are a number of command
buttons using the same lines of code for calling the function and testing it..
.....unfourtunately when I tried running the code...it failed giving a runtime
error 2108....stating I must save the field before using the setfocus method..
.....the function calls...an error message procedure that has .....Me!
txtOrderNumberChoice.SetFocus as a line of code to set the focus back to the
text box contol. It didnt bring up this runtime error when the function was
called from within the on click event of the command button....Any ideas.....
my only other option that currently works is to not place the error message
sub in the function and instead place the code for the error message directly
on the command button....i.e:
TestRecordEntered

If TestRecordEntered = False Then
MsgBox "Sorry there is no Order with the Order Number " _
& lngOrderNum, , "NO ORDERS MATCHING!"
Me!txtOrderNumberChoice.SetFocus
Exit Sub
End If
But TestRecordEntered is called from seven command buttons (up to now and
still under development) so its making the coding look a bit messy. But if
its the only way to prevent the 'superfluous Exit event ' as you called it
then I'll have to live with the extra lines of code.....unless you know of
another way without having to first save the field to be able to use the
setfocus method in the before update event. Even taking out the Setfocus
method line the msgbox still appeared twice. Looks like I'll just have to
repeat my code in each on click event

 

[Tim Ferguson]

But TestRecordEntered is called from seven command buttons (up to now
and still under development) so its making the coding look a bit
messy. But if its the only way to prevent the 'superfluous Exit event
' as you called it then I'll have to live with the extra lines of
code.....unless you know of another way without having to first save
the field to be able to use the setfocus method in the before update
event. Even taking out the Setfocus method line the msgbox still
appeared twice. Looks like I'll just have to repeat my code in each on
click event

I can't immediately see why a commandButton_Click event should cause a
big problem... but from here I cannot see how all your interdependencies
work. One thing to do is to breakpoint the MsgBox line, and inspect the
Call Stack to see why the code is being run twice. Are you sure it's the
same line of code both times?

Having said that, it's nicer for the user to avoid error messages than to
respond to them. That is why I'd be validating this thing up front:

private sub TestRecordEntered_BeforeUpdate(cancel as integer)

if len(testrecordentered.text) = 0 then
' empty value is not allowed
cancel = true

elseif not isnumeric(testrecordentered.text) then
' no good either
cancel = true

elseif dcount("*", "Orders", _
"OrderNum = " & testrecordentered.text) = 0 Then
msgbox "This is not a valid order!"
cancel = true

else
' not strictly necessary
cancel = false

end if

end sub


-- although you do need to recheck when actually using the value, because
the user will bypass the BeforeUpdate if he or she does not actually
change the text box, most problems are already gone.

Not much of an answer, I'm afraid, but hope it helps anyway.

All the best


Tim F

 

[graeme34 via AccessMonster.com]

Hi Tim
Thanks for your suggestion although I dont think I explained my problem as
clearly as I could last night.
The text box control is an unbound control that is only used to search the
value entered for seek and find first/last methods for locating records
within recordsets and for criteria matches in queries. Is it still possible
to use before update as the value in the text box is not bound to anything
and in turn is not updated to anything.
Again this is all new to me but I am learning fast
Thank you
Graeme.

But TestRecordEntered is called from seven command buttons (up to now
and still under development) so its making the coding look a bit

[quoted text clipped - 5 lines]

appeared twice. Looks like I'll just have to repeat my code in each on
click event

I can't immediately see why a commandButton_Click event should cause a
big problem... but from here I cannot see how all your interdependencies
work. One thing to do is to breakpoint the MsgBox line, and inspect the
Call Stack to see why the code is being run twice. Are you sure it's the
same line of code both times?

Having said that, it's nicer for the user to avoid error messages than to
respond to them. That is why I'd be validating this thing up front:

private sub TestRecordEntered_BeforeUpdate(cancel as integer)

if len(testrecordentered.text) = 0 then
' empty value is not allowed
cancel = true

elseif not isnumeric(testrecordentered.text) then
' no good either
cancel = true

elseif dcount("*", "Orders", _
"OrderNum = " & testrecordentered.text) = 0 Then
msgbox "This is not a valid order!"
cancel = true

else
' not strictly necessary
cancel = false

end if

end sub

-- although you do need to recheck when actually using the value, because
the user will bypass the BeforeUpdate if he or she does not actually
change the text box, most problems are already gone.

Not much of an answer, I'm afraid, but hope it helps anyway.

All the best

Tim F

 

[Tim Ferguson]

Is it still possible
to use before update as the value in the text box is not bound to
anything and in turn is not updated to anything.

The BeforeUpdate event will fire only if the control is changed, has a
Cancel parameter, but will not allow the .Text property to be changed,
except with the .Undo method, but that does not work with unbound
controls.

The Exit event will fire only if the control has been entered and is now
being exited(!); has a Cancel parameter; will allow the .Text property to
be changed. It's probably more useful for an unbound control.

If the user has any way of avoiding entering your control, you still need
to be validating it when you come to use the value in interrogating the
recordset (by the way, Seek and FindFirst is _soooo_ Sixties...). Which
leaves the question, why are you getting two MsgBoxes?

All the best


Tim F

 

[graeme34 via AccessMonster.com]

Hi again Tim

Thank for your time in explaining the event properties. One last question
what do you recommend for interrogating the recordset, as Seek and Find
first/last are sixties
Is creating a query better than seek, performance wise or is there a method
I dont know about?
As the seek method searches only the index I thought this would be faster,
but you know I'm learing each day on here

Thanks again Tim
Graeme.

 

[graeme34 via AccessMonster.com]

By the way Tim the on Exit event worked just fine,
Thank you very much!

 

[Tim Ferguson]

Thank for your time in explaining the event properties. One last
question what do you recommend for interrogating the recordset, as
Seek and Find first/last are sixties
Is creating a query better than seek, performance wise or is there a
method I dont know about?
As the seek method searches only the index I thought this would be
faster, but you know I'm learing each day on here

Okay, I admit it: I remember dBase 2!!

Yes, a Seek can be shown to be a fast way of locating a record but (a) if
the whole thing is sufficiently slow to notice the difference, you need a
newer faster server anyway; (b) it only works on Table-type dynasets,
which are only available if you have the tables in the same mdb as the
application, which is a Really Bad Idea, and (c) it won't scale to any
kind of real client-server database system.

Before anyone yells at me, I know that you _can_ OpenDatabase the back
end database and Seek on the tables there, but it's a long way round and
I stand by the other reasons all the same.

Yes: creating a query is better because (a) it'll scale to any other kind
of environment; (b) it'll work with a FE/BE split system. As a general
rule, if there are dumb slavy things to be done (like choosing an
appropriate index) then it's better to leave that up to the database
itself. The Jet engine uses a thing called Rushmore technology which does
a pretty good job of optimising query performance -- let it do its job.

On the subject of speed, did you see the fastest _ever_ square root
calculation function? It's a bit inaccurate for some values, but you
should see its benchmark results:

Public Sub FastSquareRoot(SomeValue as Double) As Double
FastSquareRoot = 2.548947
End Function

All the best


Tim F