Trap the cancel_click event

[sabavno]

Hi

I offer the user to enter a value into the input box. But if the user clicks on cancel button of the input box, my program crashes.
What should I do to trap this error.

THanks

 

[scriverb]

I think what is happening here is that the InputBox cancel button returns a Null value. This is being stored in your variable and you are then trying to do something with that value which is causing the crash.

Example:
Dim vPromptValue as Variant
vPromptValue = InputBox("Enter Number of Items: &quot
The variable vPromptValue is now Null. If you try to perform calculations using this value you will receive error messages of many kinds.

You can control what is returned from the InputBox with the NZ function:
Example:
Dim vPromptValue as Variant
vPromptValue = NZ(InputBox("Enter Number of Items: &quot,"Cancel&quot

The NZ function returns the value of the second parameter("Cancel&quot if the first parameter(InputBox(. . ) returns a Null.

The second parameter can be any value that you want to return. You can then check for "Cancel" and then not perform your numeric calculations with a null value.

Good luck. Hopefully, I have helped with this one.



Bob Scriver

 

[Junior1544]

you can also check to see if the variable is null befor running your code... i prafer this way instead of the nz because nz puts some thing in there instead of doing a real cancel which is why the person hit cancel...

Dim vPromptValue as Variant
vPromptValue = InputBox("Enter Number of Items: &quot
if vPromtValue = null then
msgbox ("you hit Cancel, bye bye.&quot
else
'code to run if the number given is valid
end if

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[sabavno]

I am not quite sure what function should I check for "Cancel" ?

 

[Junior1544]

post the code you have and we will see what we can do, we were both guessing to what you had based on what you were saying...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[sabavno]

Private Sub cmdFind_Click()

Dim intRow As Integer
Dim cntl As Control
Dim rst As DAO.Recordset
Dim sql As String
Dim varA As String

varA = InputBox("Enter PIN&quot

Set cntl = Me!List0

sql = "Select PIN From BlackBerryRecord Where PIN = " & varA & ""


Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
If rst.NoMatch = False Then
rst.MoveFirst
For intRow = 0 To cntl.ListCount - 1
If List0.Column(1, intRow) = varA Then
List0.Selected(intRow) = True
List0.SetFocus

Exit For
End If
Next intRow
End If

Set rst = Nothing



End Sub

 

[Junior1544]

'ok, i would suggest backing up your database now, because
'i am going to ask you to put the following code in place
'of what you had.

Private Sub cmdFind_Click()

Dim intRow As Integer
Dim cntl As Control
Dim rst As DAO.Recordset
Dim sql As String
Dim varA As String

varA = InputBox("Enter PIN&quot
if vars = null then
msgbox ("you clicked cancel.&quot
else

Set cntl = Me!List0

sql = "Select PIN From BlackBerryRecord Where PIN = " & varA & ""


Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
If rst.NoMatch = False Then
rst.MoveFirst
For intRow = 0 To cntl.ListCount - 1
If List0.Column(1, intRow) = varA Then
List0.Selected(intRow) = True
List0.SetFocus

Exit For
End If
Next intRow
End If

Set rst = Nothing


end if
End Sub
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[scriverb]

My suggestion to use the NZ was an attempt to help Sabavno understand his problem. Sure you can check for Null but you can also do the following which was suggested in my response. By using the NZ with the resulting "Cancel" value I felt that he would more likely understand the problem that he was experiencing.

varA = NZ(InputBox("Enter PIN", "Cancel&quot
if vars = "Cancel" then
msgbox ("you clicked cancel.&quot
else . . .

Good luck with this problem.



Bob Scriver

 

[sabavno]

Hi
Thank you all
But on all your solutions I still get a run-time error '3075' Syntax error (missing operator) in query expression 'PIN='.
I other words, sql still takes null and then crushes the code

 

[Junior1544]

ok, now i'm looking at the sql itself... i'm going to try a variation on your sql, let's see if that will work...

scriverb, i'm sorry, i see what you mean now...

sql = "Select BlackBerryRecord.* from BlackBerryRecord Where(((BlackBerryRecord.PIN) =' & varA & '));"

try this sql and see if it works...

i'm assuming that BlackBerryRecord is the table name and that PIN is the field you are looking through...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[sabavno]

I just tried it...
Gave me an error saying DATA TYPE MISMATCH IN CRITERIA EXPRESSION

 

[Junior1544]

what type of data are you putting into the pin ?? is it a number? how is varA declared, is it an integer?

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[scriverb]

The problem is in the SQL construct. Because the PIN is being stored in a string variable varA then the following syntax must be used:

sql = "Select BlackBerryRecord.* from BlackBerryRecord Where(((BlackBerryRecord.PIN) ='" & varA & "'));"

varA must be surrounded by single quotes. So, the full strings to be cancatenated have to include the single quotes.

Give this a try.

Bob Scriver