Checking for Null with Code

[Kirk P.]

I've got this code that checks for null values in a certain field:

Function CheckforNull() As Boolean

Dim db As Database
Dim rec As Recordset
Dim intRecords As Integer

On Error GoTo ErrorHandling_Err

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT * FROM OnHandInv " _
& "WHERE TOTAL_PO_COST_PER Is Null", dbOpenDynaset)
rec.MoveLast
intRecords = rec.RecordCount

If intRecords > 0 Then
MsgBox "You have " & intRecords & " invalid (null)" _
& " records in table OnHandInv" _
& vbNewLine & "Delete these records and re-export!", _
vbOKOnly + vbInformation
CheckforNull = True
Else
CheckforNull = False
End If

rec.Close

ErrorHandling_Exit:
Exit Function

ErrorHandling_Err:
Select Case Err.Number
Case 3021
CheckforNull = False
Case Else
Resume ErrorHandling_Exit
End Select

End Function

I then call this function from this sub:

Private Sub cmdExport_Click()

Call CheckforNull
If CheckforNull = False Then

On Error GoTo cmdExport_Click_Err

DoCmd.CopyObject "\\ighpmf229\DATA\Shared Department
Data\Accounting\Access Queries and class\Warehouse dbs\CP Variance
Project\Kirkdb.mdb", "OnHandInv", acTable, "OnHandInv"
MsgBox "Export Complete!", vbInformation, "Status"


cmdExport_Click_Exit:
Exit Sub

cmdExport_Click_Err:
MsgBox "ERROR" & vbNewLine & vbNewLine & Err.Description & " - " &
Err.Number
Resume cmdExport_Click_Exit

End If

End Sub

Everything works fine and dandy except for one little annoying problem. If
CheckforNull evaluates to True, I get the appropriate message box. When I
click OK, I get the same message box again. When I click OK again, the
message box disappears and everything returns to normal. What can I do to
prevent having to click OK twice to the same message box?

 

[David Lloyd]

By using the CheckforNull in the if statement you are rerunning the
function. You can just use the if statement with out the Call statement,
because the if statement must call the function to evaluate it.

Try:

If CheckforNull = False Then


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I've got this code that checks for null values in a certain field:

Function CheckforNull() As Boolean

Dim db As Database
Dim rec As Recordset
Dim intRecords As Integer

On Error GoTo ErrorHandling_Err

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT * FROM OnHandInv " _
& "WHERE TOTAL_PO_COST_PER Is Null",
dbOpenDynaset)
rec.MoveLast
intRecords = rec.RecordCount

If intRecords > 0 Then
MsgBox "You have " & intRecords & " invalid (null)" _
& " records in table OnHandInv" _
& vbNewLine & "Delete these records and re-export!", _
vbOKOnly + vbInformation
CheckforNull = True
Else
CheckforNull = False
End If

rec.Close

ErrorHandling_Exit:
Exit Function

ErrorHandling_Err:
Select Case Err.Number
Case 3021
CheckforNull = False
Case Else
Resume ErrorHandling_Exit
End Select

End Function

I then call this function from this sub:

Private Sub cmdExport_Click()

Call CheckforNull
If CheckforNull = False Then

On Error GoTo cmdExport_Click_Err

DoCmd.CopyObject "\\ighpmf229\DATA\Shared Department
Data\Accounting\Access Queries and class\Warehouse dbs\CP Variance
Project\Kirkdb.mdb", "OnHandInv", acTable, "OnHandInv"
MsgBox "Export Complete!", vbInformation, "Status"


cmdExport_Click_Exit:
Exit Sub

cmdExport_Click_Err:
MsgBox "ERROR" & vbNewLine & vbNewLine & Err.Description & " - " &
Err.Number
Resume cmdExport_Click_Exit

End If

End Sub

Everything works fine and dandy except for one little annoying problem. If
CheckforNull evaluates to True, I get the appropriate message box. When I
click OK, I get the same message box again. When I click OK again, the
message box disappears and everything returns to normal. What can I do to
prevent having to click OK twice to the same message box?

 

[Kirk P.]

Got it - thanks for the help!