How to stop form from closing

[oxicottin]

I have the function below on my forms Unload Event and all I want it to do is when I close the form, if there isn't a record created then just close the form.

If frm.NewRecord Then
MsgBox "TEST 1 CLOSE FORM"

If a record was created, then it runs through qry_FindNullRecords and displays what missing info. in a popup IF there is missing data in the query BUT it still closes the form, and I don't want it to close the form if there is missing data, I want to cancel the function. What am I missing and where in the VBA below to cancel the function?

I call it from my forms unload event:

Private Sub Form_Unload(cancel As Integer)

cancel = fncSa(Me)

End Sub

Function fncSa(frm As Form) As Boolean

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intX As Integer
    Dim strMissnData As String
    Dim strMissnProd As String
    Dim lastproduct As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qry_FindNullRecords", dbOpenSnapshot)
'--------------------------------------------------------------------------------------------------
'Open the switchboard its a new record and there isnt data missing
    If frm.NewRecord Then
        MsgBox "TEST 1 CLOSE FORM"
'--------------------------------------------------------------------------------------------------
'Ops some data is missing, stop and show whats missing so it can be filled out, _
        Run through qry_FindNullRecords and find products lengths quanity that data wasnt entered
    Else
        With rs
            .MoveLast: .MoveFirst: intX = .RecordCount
            lastproduct = .Fields("product")
            strMissnData = .Fields("Product") & .Fields("strProductLength") & vbCr
            
            Do Until .EOF
                
                If Trim(lastproduct) <> Trim(.Fields("product")) Then
                    strMissnData = strMissnData & .Fields("Product") & .Fields("strProductLength") & vbCrLf
                End If
                lastproduct = .Fields("product")
                
                .MoveNext
            Loop
        End With
        
        MsgBox "There are " & intX & " records that are missing information for the following Products/Lengths." & vbCrLf & vbCrLf _
        & strMissnData & vbCrLf _
        & "You have to follow up before saving or sending!", vbExclamation, "Missing Information"

        End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[theDBguy]

I don't see you returning any result from your fncSa() function.

 

[oxicottin]

@theDBguy it does work somewhat. The result is if it's a new record with no data entered then (MsgBox "TEST 1 CLOSE FORM")

Then it supposed to run through the query to look for null records and if there is some its displayed in the message box pop up but after that I need to stop or exit the function but leave the form open.

UPDATE: I figured it out.... I added fncSa = True and it stops the form from closing.....

        MsgBox "There are " & intX & " records that are missing information for the following Products/Lengths." & vbCrLf & vbCrLf _
        & strMissnData & vbCrLf _
        & "You have to follow up before saving or sending!", vbExclamation, "Missing Information"

        End If
        
fncSa = True

 

[theDBguy]

UPDATE: I figured it out.... I added fncSa = True and it stops the form from closing.....

That's exactly what I was saying. Glad to hear you figured it out.

 

[Gasman]

Why not stop the records with invalid data in the first place?

 

[oxicottin]

@Pat Hartman, there is a reason for the pop up and this is sorta a validation for my subform to let you know there is data still.

I have validation in place for the main form but i cant validate on the sub because it might take 2-3 days to enter data being the application is opened and a little is entered at a time. This is just a hay these areas still need filled in and display which ones.