Invalid Use of Null - help! :o)

[sandy2011]

Hi

If I put in an ack number and it is a duplicate, I want to delete the number and continue on.. but I get an error message and it says "Invalid use of Null" end or debug etc....

How do I get my code to allow me to delete numbers and continue on.. or allow the null field? I.e. allow record to have a blank ack number

here is my code on the field .. which works (apart from when I want to erase the ack number after finding it is a duplicate)



Private Sub Ack_No_BeforeUpdate(Cancel As Integer)

'*********************************
'TO CHECK FOR DUPLICATES
'*********************************
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Ack_No.Value
stLinkCriteria = "[Ack_No]=" & "'" & SID & "'"
'Check CALL LOG table for duplicate ACK NO
If DCount("Ack_No", "CALL LOG", stLinkCriteria) > 0 Then
'Message box warning of duplication
MsgBox "WARNING!! Duplicate Acknowledgement Number! " _
& SID & " this number has already been entered." _
& vbCr & vbCr & "Please mark this a 2nd/3rd etc. replacement.", vbInformation _
, "Duplicate Information"

End If
Set rsc = Nothing

End Sub

 

[GinaWhipp]

Is this field a Primary Key?

 

[sandy2011]

No - not a primary key..

 

[CBrighton]

If you allow null then presumably you will allow multiple records to have null without it being a duplication to be deleted?

If so you simply need to test the field for null before you start trying to compare things:

Private Sub Ack_No_BeforeUpdate(Cancel As Integer)
 
'*********************************
'TO CHECK FOR DUPLICATES
'*********************************
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Ack_No.Value
[B]If IsNull(SID) THEN[/B]
[B]  'Insert code telling it how to handle a null string - e.g. Exit Sub or GoTo[/B]
[B]End If[/B]
stLinkCriteria = "[Ack_No]=" & "'" & SID & "'"
'Check CALL LOG table for duplicate ACK NO
If DCount("Ack_No", "CALL LOG", stLinkCriteria) > 0 Then
'Message box warning of duplication
MsgBox "WARNING!! Duplicate Acknowledgement Number! " _
& SID & " this number has already been entered." _
& vbCr & vbCr & "Please mark this a 2nd/3rd etc. replacement.", vbInformation _
, "Duplicate [URL="http://www.access-programmers.co.uk/forums/showthread.php?t=214905#"][COLOR=darkgreen]Information[/COLOR][/URL]"
 
End If
Set rsc = Nothing
 
End Sub

:edit:

I am assuming the code is stopping here:

If DCount("Ack_No", "CALL LOG", stLinkCriteria) > 0 Then

Where it's trying to apply strLinkCriteria (which includes null) as the criteria.

If the error is actually here:

SID = Me.Ack_No.Value

Where it's trying to save a null value to SID then you will need to check for null in Me.Ack_No.Value before that point.

:edit2:

It might be worth looking up the Nz() function too to account for zero length strings as well as nulls.

 

[sandy2011]

cbrighton

You've been a great help (4 eyes are better than 2!).. i had tried something similar to what you advised before and then tried your code but it still doesnt work.. (put it before me.ack_no.value)
It just ignores the code and jumps to code below hightlight SID = Me.Ack_No.Value

 

[GinaWhipp]

Please copy/paste the code you have now. Also, when you say...

SID = Me.Ack_No.Value

...does SID have a value? I'm thinking not.

 

[CBrighton]

SID is a string, dimmed in the first line of code.

The only 2 problems I can think of in that code are Ack_No not being the correct name or a string failing when null is assigned to it (I've never tried).

Try this (checking for null before doing anything with SID & Ack_No):

Private Sub Ack_No_BeforeUpdate(Cancel As Integer)
 
'*********************************
'TO CHECK FOR DUPLICATES
'*********************************
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
[B]If IsNull(Me.Ack_No.Value) THEN
  msgbox "Missing account number, please check data"[/B]
[B]  Exit Sub[/B]
[B]End If[/B]
SID = Me.Ack_No.Value
stLinkCriteria = "[Ack_No]=" & "'" & SID & "'"
'Check CALL LOG table for duplicate ACK NO
If DCount("Ack_No", "CALL LOG", stLinkCriteria) > 0 Then
'Message box warning of duplication
MsgBox "WARNING!! Duplicate Acknowledgement Number! " _
& SID & " this number has already been entered." _
& vbCr & vbCr & "Please mark this a 2nd/3rd etc. replacement.", vbInformation _
, "Duplicate [URL="http://www.access-programmers.co.uk/forums/showthread.php?t=214905#"][COLOR=darkgreen]Information[/COLOR][/URL]"
 
End If
Set rsc = Nothing
 
End Sub