Form Closes Automatically when Code Ends - WHY?!

[willknapp]

I've come across some sort of weird bug. I have a combo box that contains a list of values, but users can add their own if they choose. If the value they type in isn't on the list, the Not In List event fires. Once the procedure is done, the form inexplicably closes.

Oddly enough, it only closes under certain circumstances - the code provides several different options for saving the new data, and if I choose different options, the form works as its designed.

Further, if I insert a breakpoint anywhere in the code, then hit F5 to let the code run, the form works fine. Literally, the breakpoint can be anywhere in the procedure from the first line (On Err...) to the last line in the Exit block, "Exit Sub".

This one is truly baffling me - i can't believe I'm the only one to have experienced this, and I'm pretty sure it's just one of those buggy things that will go away if I recreate the form, but that's way too much work to do for what should be something that cleans up after a compile or Compact and Repair.

Any suggestions?

Thanks!

Will

 

[willknapp]

Also, to be clear, the procedure doesn't call any other procedures (other than an error handler in the event of an error), and nowhere in the code do I have any sort of me.close statement...

 

[willknapp]

Private Sub cboClientContactID_NotInList(NewData As String, Response As Integer)
10     On Error GoTo PROC_ERR
20     PushCallStack "frmInquiries: cboClientContactID_NotInList"
    ' Adds a new client contact if they don't already exist
 
30     Dim fName As String, lName As String, ContactID As Long, strEmail As String
40     Dim strClient As String
50     Dim lNameStart As Long
60     Dim rsInq As DAO.Recordset
70     If Me.chkInternal = True Then
80         MsgBox "You must choose a department from the list provided.", vbInformation, "Unknown Contact"
90         Response = acDataErrContinue
100    Else
110        If MsgBox("The name you typed is not on the contact list for this client.  Would you like to add it?", vbYesNo + vbQuestion, _
                    "Add " & NewData & "?") = vbYes Then
120            Response = acDataErrAdded
 
130            If Me.cboClientID = 131 Then
                ' Client is not known
140                MsgBox "You must select a client before adding " & NewData & " as a client contact.", vbOK + vbInformation, _
                        "Unknown Contact"
150                Me.cboClientID.SetFocus
160                Me.cboClientContactID.Dropdown
170                Response = acDataErrContinue
180            Else
                ' Client is known
190                lNameStart = InStrRev(NewData, " ")
200                If lNameStart = 0 Then  ' no spaces in the new data, so first and last name cannot be parsed
210                    MsgBox "Both a first and last name are required to add a new client contact.", vbInformation, "Save New Contact"
220                    Response = acDataErrContinue
230                Else
                    ' A space exists, so make the last name all text to the right of the last space, and the firstname
                    ' everything to the left of it.
24                    lName = right(NewData, Len(NewData) - lNameStart)
250                    fName = Trim(Left(NewData, lNameStart))
260                    If Me.chrSenderEmail = "Not Found" Then
                        ' Email address could not be scraped from the original email message
270                        strEmail = InputBox("Enter " & NewData & "'s email address:", "Save new Contact", _
                                            "Email address...")
280                        Do While Not strEmail Like "*@*.*"
290                            If strEmail = "" Then   ' User Canceled the input box for the email address
300                                MsgBox "No email address saved for " & NewData & ".", vbInformation, "Save New Contact"
310                                strEmail = "No Email Address"
320                                Exit Do
330                            End If
340                            strEmail = InputBox("The email is not in a valid format." & vbCrLf & vbCrLf & _
                                                "Please re-enter " & NewData & "'s email address:", "Email Not Found")
350                        Loop
360                        Response = acDataErrAdded
370                    Else
                        ' Email was scraped from the original email, so confirm that it belongs to the new client contact.
380                        If MsgBox("Is " & NewData & "'s email address " & Me.chrSenderEmail & "?", vbYesNo + vbQuestion, _
                                    "Confirm Email Address") = vbNo Then
390                            strEmail = InputBox("Enter " & NewData & "'s email address:", "Enter New Address")
400                            Do While Not strEmail Like "*@*.*"
410                                If strEmail = "" Then   ' User Canceled the input box for the email address
420                                    MsgBox "No email address saved for " & NewData & ".", vbInformation, "Save New Contact"
430                                    strEmail = "No Email Address"
440                                    Exit Do
450                                End If
460                                strEmail = InputBox("The email is not in a valid format." & vbCrLf & vbCrLf & _
                                                    "Please re-enter " & NewData & "'s email address:", "Email Not Found")
470                            Loop
480                        Else
490                            strEmail = Me.chrSenderEmail
500                        End If
510                        Response = acDataErrAdded
520                    End If
                    ' We now have a valid clientID, first name, last name and email address - add the client contact to the table
530                    If db Is Nothing Then Set db = CurrentDb
540                    Set rs = db.OpenRecordset("tblClientContacts")
550                    With rs
560                        .AddNew
570                        !chrClientContactFirstName = fName
580                        !chrClientContactLastName = lName
590                        !lngClientID = Me.cboClientID
600                        !chrClientContactEmail = strEmail
610                        .Update
620                        .FindFirst "ChrClientContactEmail = '" & strEmail & "'"
630                        ContactID = !lngClientContactID
640                        .Close
650                    End With
660                    Set rs = db.OpenRecordset("Select chrClient from tblClients where lngClientID = " & Me.cboClientID)
670                    With rs
680                        .MoveFirst
690                        strClient = !chrClient
700                        .Close
710                    End With
720                    MsgBox fName & " " & lName & " has been added to the list of available client contacts for " & _
                            strClient & ".", vbInformation, "New Client Contact Added"
                    'Update all existing unknown records that have the same send email account
730                    Set rs = Nothing
740                    Set rsInq = db.OpenRecordset("SELECT * from tblInquiries where chrSenderEmail = '" & strEmail & _
                                                 "' and lngClientContactId is null;")
750
760                    If rsInq.RecordCount > 0 Then
770                        With rsInq
780                            .MoveFirst
790                            Do
800                                If Not !lngInquiryID = Me.lngInquiryID Then
810                                    .Edit
820                                    !lngClientContactID = ContactID
830                                    !blnClientContactVerified = True
840                                    .Update
850                                End If
860                                    .MoveNext
870                            Loop Until .EOF
880                        End With
890                    End If
900                    rsInq.Close
910                End If
920            End If
930        Else
940            MsgBox "Please select a contact from the list.", vbOK + vbInformation, "Unknown Contact"
950            Me.cboClientContactID = ""
960            Me.cboClientContactID.Dropdown
970            Response = acDataErrContinue
980        End If
990    Set rs = Nothing
1000   Set db = Nothing
1010   End If
 
PROC_EXIT:
9000   PopCallStack
9100   Exit Sub
 
PROC_ERR:
9200   GlobalErrHandler
9300   Resume Next
End Sub

 

[missinglinq]

...Oddly enough, it only closes under certain circumstances - the code provides several different options for saving the new data, and if I choose different options, the form works as its designed...

If you want help in debugging code, we really have to at least see the code! Having multiple options for saving data entered in conjunction with the NotInList event is unusual, to say the least, and a hint as to which options create the problem/which don't would probably help, too!

Linq ;0)>

 

[willknapp]

Something else of note - this form worked fine up until recently. There were no changes made to this procedure recently, and this just started happening.

Here's a quick description of the procedure - it's used to add a new name to a list of client contacts.

If someone type a name that isn't in the list, the system asks if they want to add the person to the list. If the user clicks no, the new data is ignored and the user is told to choose from the drop down list.

I the user clicks yes, they are then instructed to verify the email address (which was stored with the record in an earlier step). If the email isn't correct, the can enter an alternate one.

Once the First Name, Last Name, and Email Address are all verified, I use DAO to add the contact to the ClientContact table, getting the client ID from another control on the form. Finally, a message pops up stating that the person has been added to the list.

The form only closes if the user elects to add a new person and use the default email address. If the choose to enter a different address, or if they cancel adding the email address, the user is still added, and the form remains open.

That's it! And like I said, If I break the code anywhere so I can go through line by line, it works fine. The form only closes if the code is allowed to run unbroken.

 

[willknapp]

Given the strange circumstances under which the issue appeared initially, and continues to appear, I'll be very surprised if this can be solved by debugging, which is why I didn't include the code to begin with - I really just wanted to put a post out there that might catch someone's attention who's had this problem themselves before. I wasn't trying to be deliberately obtuse...

Plus, who really wants to debug over 100 lines of someone else's code?

 

[CJ_London]

It looks like you are using error handling per FMS.

If I recall correctly this can cause your code to stop if not set up properly. Suggest you take out pushes and pops and disable other error handling to work out where the problem lies.

 

[willknapp]

It looks like you are using error handling per FMS.

If I recall correctly this can cause your code to stop if not set up properly. Suggest you take out pushes and pops and disable other error handling to work out where the problem lies.

I've commented out the push/pop commands and I still get the error.

 

[Cronk]

It could be a corruption in the database but first, have you tried setting a break point (key F9) and then stepped through your code line by line by pressing the F8 key?

 

[gemma-the-husky]

are you absolutely sure there is no statement

docmd.close

 

[willknapp]

It could be a corruption in the database but first, have you tried setting a break point (key F9) and then stepped through your code line by line by pressing the F8 key?

Yes - if I insert a break point anywhere in the code (including the very first line or the very last line) and either step through line by line or just let it run, it works just fine.:banghead:

 

[willknapp]

are you absolutely sure there is no statement

docmd.close

I'm pretty sure there isn't - I mean, I've posted the code and it's not in there, right? Did you find it in the code somewhere and you're simply being coy?

And again - am I somehow not being clear? Is there a better way to state that the code operates just fine if I break anywhere and step through the rest?

 

[DrallocD]

To identify the source of the error, I would comment out lines of code one by one until it starts working, I would start with lines 950 and 960 where you are affecting the combobox that you are in the event of.