Form doesn't close... sometimes

[PRMiller]

I'm having a rather odd problem. I have a form, called "frmScorecard" that accepts input from a user. On click, it checks to see if a record matching the criteria entered by the user exists.

If a record does not exist, it calls a procedure, then closes itself (the form) and opens a different form, displaying the record that has been created.

If a record matching the criteria does exist, it asks the user if they would like to override the existing record. If they affirm that they do, the form deletes the existing record, then calls the aforementioned procedure, closes itself and opens a different form (same one mentioned in the paragraph above), displaying the new record. Here's the odd part:

When I'm in what I refer to as design mode, or Shift+Enter, the form works as intended. However, when I test the system as the users see it (with all of the options under Tools > Startup disabled), the frmScorecard does NOT close itself IF a record with matching criteria exists. VERY odd.

Here's the code from the Click event:

Private Sub cmdRun_Click()

    Dim intIndex As Integer
    Dim lngExists As Long
    Dim strMess As String
    Dim strSQL As String
    
    Screen.MousePointer = 11

    If Me.cboDateRange.Value = "" Then
        Screen.MousePointer = 0
        MsgBox "Please select an evaluation period.", vbOKOnly + vbCritical, "Error."
        Me.cboDateRange.SetFocus
        Exit Sub
    Else

        lngExists = DCount("[AutoNumber]", "tblPCAScorecard", "[EmpID] = " & gblEmpID & " AND [EvalPeriodStart] = #" & gblEvalPeriodStart & "#")
    
        If lngExists > 0 Then
            strMess = FormattedMsgBox("A scorecard already exists for this Advocate.@If you decide to continue and" & _
                      " create a new scorecard, you will overwrite and delete the existing scorecard for this" & _
                      " Advocate.  If, instead, you would like to edit the existing scorecard, return to the Reports" & _
                      " Menu, select Scorecards, and select Open.@Would you like to create a new scorecard," & _
                      " overwriting the existing one?", vbCritical + vbYesNo, "Warning!")
            
            If strMess = vbYes Then
                strSQL = "DELETE * From tblPCAScorecard WHERE EmpID = " & gblEmpID & " AND EvalPeriodStart = #" & gblEvalPeriodStart & "#;"
  
                Call ExecuteSQL(strSQL)
                
                If gblHasCMS = 0 Then
                    Call CompileScorecardDataMDN
                Else
                    If gblSiteID = "SMG" Then
                        Call SMGQuality
                    End If
                    Call CompileScorecardDataCMS
                End If
  
                intIndex = Nz(DLookup("[AutoNumber]", "tblPCAScorecard", "[EmpID] = " & gblEmpID & " AND [EvalPeriodStart] = #" & Me.txtEvalPeriodStart & "#"), "")
                
                DoCmd.Close
                
                DoCmd.OpenForm "frmPCAScorecard", , , "AutoNumber = " & intIndex
            ElseIf strMess = vbNo Then
                Me.txtEvalPeriodStart.Value = ""
                Me.txtEvalPeriodEnd.Value = ""
                Screen.MousePointer = 0
                Me.cboPCASelection.SetFocus
            End If
        Else
            If gblHasCMS = 0 Then
                Call CompileScorecardDataMDN
            Else
                If gblSiteID = "SMG" Then
                    Call SMGQuality
                End If
                Call CompileScorecardDataCMS
            End If
  
            intIndex = Nz(DLookup("[AutoNumber]", "tblPCAScorecard", "[EmpID] = " & gblEmpID & " AND [EvalPeriodEnd] = #" & Me.txtEvalPeriodEnd & "#"), "0")
  
            DoCmd.Close
            DoCmd.OpenForm "frmPCAScorecard", , , "AutoNumber = " & intIndex
        End If
    End If
    
End Sub

Thoughts?

Thanks,
Paul

 

[Frink]

Hallo,

Have you tried adding parameters to the Close statement to indicate the form you want to close?

- Frink

 

[PRMiller]

Frink, thanks for the reply. I found this problem elsewhere in my app, as well. I tried adding the form name to the Close statement, as you suggested, but that resulted in a Type Mismatch error.

The solution? For forms where the code was simple, like this:

MsgBox "Record Saved"
DoCmd.Close

I moved the message box statement to the line AFTER the close statement, instead of before. Works now. I haven't figured out a way to apply it yet to the form above without doing some time-consuming rewrites, but I'll get there.

!#$&%^@ MicroSoft. What is the DEAL with those people?

 

[PaulF]

Use
DoCmd.Close acForm, Me.Name

PaulF

 

[PRMiller]

!#$&%^@ amateurs named PerkinsSlave. What is the DEAL with those people?


PaulF,

That did it, thanks. For as far as I believe I have come with VBA, it's the little things that keep tripping me up (or, more accurately put, it's my ignorance that keeps doing the trick).

Thanks,
P