invalid use of null

[Guest]

I have the following code which checks the ISBN check digit to ensure a
correct ISBN no is enter for a product. This works fine, however, if the
user enters the field and then decideds not to enter an isbn no, i get the
'Invalid Use of Null' error message which will not go away and even takes the
user into the code, which i don't want. Could anyone please tell me how to
stop this happening, i've tried setting the strnumer as variant but this
doesn't work.

Private Sub LinkISBN_Exit(Cancel As Integer)

varMultiplier = Array(Null, 1, 3, 1, 3, 1, 3, 1, 3, 1, 3, 1, 3)

'Dim strnumber As Variant

strnumber = [LinkISBN]
'strnumber = format(dwisbn, "000000000")


dwchecksum = 0
total = 0
'checkno = CInt(Mid(strnumber, 10, 1))
checkno = Mid(strnumber, 13, 1)
msg = "Incorrect ISBN Please Re-Enter"




For p = 1 To Len(strnumber)

'wdigit = CInt(Mid(strnumber, p, 1))
wdigit = Mid(strnumber, p, 1)
If p >= 1 And p <= 12 Then
dwchecksum = wdigit * varMultiplier(p)
total = total + dwchecksum
End If

'If p = 10 And [wdigit] <> "X" Then

'dwchecksum = wdigit * varMultiplier(p)
'total = total + dwchecksum
'End If

[tot] = total


Next p

[modfig] = [tot] Mod 10
[check] = 10 - [modfig]
If [modfig] = 0 Then
[check] = 0
End If

If [modfig] = 1 Then
[check] = "X"
End If
check = LTrim(check)

If check <> checkno Then
MsgBox (msg)

End If


End Sub

 

[Stefan Hoffmann]

hi blackcat,

I have the following code which checks the ISBN check digit to ensure a
correct ISBN no is enter for a product.

Caution:

The ISBN system was changed 2005, they added a new type of ISBN, see

http://en.wikipedia.org/wiki/Isbn

user enters the field and then decideds not to enter an isbn no, i get the
'Invalid Use of Null' error message which will not go away and even takes the
user into the code, which i don't want. Could anyone please tell me how to
stop this happening, i've tried setting the strnumer as variant but this
doesn't work.

First of all, make it a separate function:

Public Function IsValidIsbn(AIsbn As String) As Boolean

'your code..

End Function

Then allways name your controls, don't use the default given names. So
call your TextBox bound to [LinkISBN] txtLinkIsbn.

Private Sub LinkISBN_Exit(Cancel As Integer)

Cancel = False
If Not IsNull(txtLinkIsbn.Value) Then
If Not IsValidIsbn(txtLinkIsbn.Value) Then
Cancel = True
End If
Else
Cancel = True
End If

End Sub

mfG
--> stefan <--

 

[Guest]

I'm sorry, i don't understand what you mean by a seperate function - do i set
this up as a module and then call that? thanks for your help with this

hi blackcat,

I have the following code which checks the ISBN check digit to ensure a
correct ISBN no is enter for a product.

Caution:

The ISBN system was changed 2005, they added a new type of ISBN, see

http://en.wikipedia.org/wiki/Isbn

user enters the field and then decideds not to enter an isbn no, i get the
'Invalid Use of Null' error message which will not go away and even takes the
user into the code, which i don't want. Could anyone please tell me how to
stop this happening, i've tried setting the strnumer as variant but this
doesn't work.

First of all, make it a separate function:

Public Function IsValidIsbn(AIsbn As String) As Boolean

'your code..

End Function

Then allways name your controls, don't use the default given names. So
call your TextBox bound to [LinkISBN] txtLinkIsbn.

Private Sub LinkISBN_Exit(Cancel As Integer)

Cancel = False
If Not IsNull(txtLinkIsbn.Value) Then
If Not IsValidIsbn(txtLinkIsbn.Value) Then
Cancel = True
End If
Else
Cancel = True
End If

End Sub

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I'm sorry, i don't understand what you mean by a seperate function - do i set
this up as a module and then call that? thanks for your help with this

Yup.


mfG
--> stefan <--

 

[Guest]

sorry to be a pain but how do i do this? i've set up a module called
IsValidIsbn, then on the LinkIsbn field on exit event procedure i've typed in

Private Sub LinkISBN_Exit(Cancel As Integer)

Call IsValidIsbn

End Sub

and i get the error 'compile error expected variable or procedure, not module'

 

[David Cox]

Why Null in the array and not 0?

Why do the calculation if there is no ISBN?

 

[Jamie Collins]

I have the following code which checks the ISBN check digit

Private Sub LinkISBN_Exit(Cancel As Integer)
<<snipped>>

Rather than procedural code, consider using declarative functionality
such as a Validation Rule, which (among advantages) handles the NULL
value appropriately.

The following example uses CHECK constraints and ANSI-92 Query Mode
syntax:

CREATE TABLE Test (
isbn_10 CHAR(10),
CONSTRAINT isbn_10__pattern
CHECK (isbn_10 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9][0-9X]'),
CONSTRAINT isbn_10__checksum
CHECK (
IIF(MID(isbn_10, 10, 1) = 'X', 10, CLNG(MID(isbn_10, 10, 1)))
=
((CLNG(MID(isbn_10, 1, 1)) * 1)
+ (CLNG(MID(isbn_10, 2, 1)) * 2)
+ (CLNG(MID(isbn_10, 3, 1)) * 3)
+ (CLNG(MID(isbn_10, 4, 1)) * 4)
+ (CLNG(MID(isbn_10, 5, 1)) * 5)
+ (CLNG(MID(isbn_10, 6, 1)) * 6)
+ (CLNG(MID(isbn_10, 7, 1)) * 7)
+ (CLNG(MID(isbn_10, 8, 1)) * 8)
+ (CLNG(MID(isbn_10, 9, 1)) * 9)
) MOD 11
)
);

Jamie.

--

 

[Stefan Hoffmann]

hi,

sorry to be a pain but how do i do this? i've set up a module called
IsValidIsbn, then on the LinkIsbn field on exit event procedure i've typed in

Private Sub LinkISBN_Exit(Cancel As Integer)

Call IsValidIsbn

As you have named the module and the function using the same name, this
must be:

Cancel = Not IsValidIsbn.IsValidIsbn(Nz([LinkIsbn], ""))

End Sub

and i get the error 'compile error expected variable or procedure, not module'

Read my first posting again.


mfG
--> stefan <--