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?

 

[Pat Hartman]

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?

You are going about this the wrong way. You need to validate your data BEFORE the record gets saved with the missing/invalid data. You are closing the barn door after the horses have escaped.

The form's BeforeUpdate event is the last event that runs before a record gets saved (REGARDLESS of what prompted the save). Think of it as the flapper at the bottom of a funnel. If the flapper is open, the record falls through and gets saved. If the flapper is closed, the record does not get saved. That means that you don't have to go back later and try to clean up bad records.

You might want to view a couple of the videos I made on why you should be using the BeforeUpdate event

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

There is even a database you can download which will help you to expand your understanding of when certain events run and therefore, what type of code goes into specific events to use them correctly.

Allowing bad data to be saved is simply wrong. Use the correct form level event so you can stop the save before it happens.

 

[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.

 

[Pat Hartman]

You should be able to validate each child record as it is added in the subform. If incomplete records are invalid, then don't allow them to be saved.

One technique for entering data "slowly" is to have a flag on the parent record that starts out as Incomplete. Then as each child record is added, if there is an automatic way to know when the data is complete, your code can automatically set the button to Complete if everything has been entered. You would run the check code in the AfterUpdate event of the main form AND the subform. If only a user knows when he is done, the user can press the button and your query can check for missing data in any record.

When you allow incomplete data to be entered piecemeal, you should probably run a query as the user opens the application and search for incomplete items and then display them in a list form so the user can easily get to them. If the records are tied to an individual, then when Tom logs in, only show Tom the list of incomplete records that he is responsible for.

The form's Close event is an inappropriate place to validate data because you don't actually want to prevent people from closing a form. If you block them, they will just use the three-finger salute and shut down Access. It's like trying to control your children. Only set rules you are prepared to enforce. The rest you do by using other types of controls as I suggested. When people want to try to prevent a form from closing, I always remember the chaos one of my programmers caused when he locked up the entire CICS (the company wide network of 4,000 terminals) region because he wouldn't let a particular transaction form close.