MsgBox Mayhem

[Kirk P.]

I wrote this code that works fine:

Function CheckforNull()

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

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT INVI_RESP_FOR_ID, PROD_ID " _
& "FROM OnHandInv " _
& "WHERE (((OnHandInv.TOTAL_PO_COST_PER)Is
Null)) " _
& "GROUP BY INVI_RESP_FOR_ID, PROD_ID",
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 start over!", _
vbOKOnly + vbInformation

End If

rec.Close

End Function

Trouble is, I'm calling this function from another function. This functions
runs first and correctly displays the MsgBox I want. When I click OK, it
continues processing the function that called it. What I really want is when
I click OK, the function ends right there and does NOT continue.

 

[Alex White MCDBA MCSE]

Hi Kirk,

you have not specified a return value from the function e.g. Function
CheckforNull() as boolean
at the top of your code do CheckforNull = False

and after the msgbox call do
CheckforNull = True

then in your calling function you should be able to do

if CheckForNull = true then
... you have an error
end if

hope it's what you need...

 

[Klatuu]

:

You are not returning any value from CheckforNull. If you return no value,
you may as well use a Sub.

I have added a couple of lines below that assign the values to return as a
Boolean. In the function that calls CheckforNull, test to see what value you
got back to determine whether to continue. CheckforNull will now return True
if it found Nulls and False if it did not:

If CheckforNull then
.....Whatever else you need to do before you leave the function.
Exit Function
End If

I wrote this code that works fine:

Function CheckforNull() As Boolean

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

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT INVI_RESP_FOR_ID, PROD_ID " _
& "FROM OnHandInv " _
& "WHERE (((OnHandInv.TOTAL_PO_COST_PER)Is
Null)) " _
& "GROUP BY INVI_RESP_FOR_ID, PROD_ID",
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 start over!", _
vbOKOnly + vbInformation

CheckForNull = True
Else
CheckForNill = False

 

[Kirk P.]

Your suggestion works great, but now I've got another problem. If there are
no nulls in my recordset, the function returns "no current record" instead of
the False I expected. Works great when True, not so good when False.

Seems to me the If statement tests for recordcount>0 and handles the case
when 0 records are found, so I'm confused why it doesn't work.