docmd.close closes Access instead of form

[aadebayo]

I have currently migrating my applications to Access 2007 ADODB from Access 2000. Most of what I have done now works. I am having a minor problem with one of my forms though. When I save the form, and close it using

 DoCmd.Close acForm, Me.Name

, the code closes the access application. The following is the code that runs when I click on the save button

     'check for errors first
    fCancel = False
   ' form_beforeUpdate (fCancel)
   
   ' first check that mandatory fields have been completed
   Dim strField As String

    Call glrEnableButtons(Me, Me)
        If Me.txtTotalRecs > 1 Then
            Call glrTglNavButtons(Me, True)
        Else
            Call glrTglNavButtons(Me, False)
        End If
        
    If IsNull(Me.txtCompanyName) Then
        MsgBox "All yellow fields must be completed", vbExclamation
        strField = "CompanyName"
        
    End If

    If IsNull(Me.txtAddress1) Then
        MsgBox "All yellow fields must be completed", vbExclamation
        strField = "Address1"
    End If

    If IsNull(Me.ReviewRequester_Label) Then
        MsgBox "All yellow fields must be completed", vbExclamation
        strField = "ReviewRequester"
        
    End If
    If IsNull(Me!Score) Then
        Me!Score = Me.txtFinalScore
    End If
    Pass_Fail
     
    If fCancel Then
        Exit Sub
    End If
    Dim strSQL As String
  'close history
    history_update
    If formMode = "Add" Then
       createCompanyRecords
       createReviewRecords
       formMode = ""
    End If
    
    If MsgBox("Do you wish to update the history", vbQuestion + vbYesNo) = vbYes Then
       
    'Update history
     createHistoryRecords
    End If
         
     '   SaveRecord Me
        'save the records into the database
        Call glrchangeformstate(Me, glrcFormModeBrowse)
        Call glrEnableButtons(Me, Me)
    
         Me.cmdDelete.Enabled = True
         Me.cmdHistory.Enabled = False

below are the 3 subs that you are called during the save process, that saves the records

Private Sub createCompanyRecords()
   Dim strSQL As String
   Dim company_id As Integer
   
   company_id = DMax("[companyid]", "tblCompany") + 1
   strSQL = "INSERT INTO tblCompany (companyid, CompanyName,Address1,Address2,Address3,Address4,Postcode,Telephone,Email,Contact)" & _
    "values ('" & company_id & "', '" & Me.txtCompanyName & "' ,'" & Me.txtAddress1 & "' ,'" & Me.txtAddress2 & "' , '" & Me.txtAddress3 & "' ," & _
    "'" & Me.txtAddress4 & "', '" & Me.txtPostcode & "', '" & Me.txtTelephone & "', '" & Me.txtEmail & "'," & _
    "'" & Me.txtContact & "')"
    
    cmdExecute strSQL
End Sub


Private Sub createReviewRecords()
    Dim strSQL As String
    Dim company_id As Integer
    
    company_id = DMax("[companyid]", "tblCompany")
    
    strSQL = "INSERT INTO tblReview (CompanyID,ReviewDate,ReviewerID,Requester,Commitment,Duties,Cooperation,Arrangements," & _
    "Risk,CDM,Signed,Passed,Score) values ('" & company_id & "' ,'" & Me.txtReviewDate & "' ,'" & Me.cboReviewerName & "' ," & _
    "'" & Me.txtReviewRequester & "' ,'" & Me.Quest1 & "', '" & Me.Quest2 & "', '" & Me.Quest3 & "', '" & Me.Quest4 & "'," & _
    "'" & Me.Quest5 & "', '" & Me.txtCDMScore & "', '" & Me.Signed & "', '" & Me.lblFail & "','" & Me.txtFinalScore & "')"
    
    cmdExecute strSQL

End Sub


Private Sub createHistoryRecords()
    Dim strSQL As String
    Dim company_id As Integer
    
    company_id = DMax("[companyid]", "tblCompany")
    If DCount("[CompanyID]", "tblHistory1", "[CompanyID] =" & company_id) > 0 Then
            strSQL = "execute spUpdateHistory '" & txtCompanyName & "'"
            cmdExecute strSQL
            'Update history1
            strSQL = "execute spUpdateHistory1 '" & txtCompanyName & "'"
            Exit Sub
    ElseIf DCount("[CompanyID]", "tblReview", "[CompanyID] =" & company_id) > 0 Then
            strSQL = "execute spInsertHistory1 '" & txtCompanyName & "'"
             Exit Sub
    ElseIf DCount("[CompanyID]", "tblHistory1", "[CompanyID] =" & company_id) > 0 Then
            strSQL = "execute spInsertHistory & company_id & "
    End If
    
    cmdExecute strSQL

End Sub

Please can someone tell me what I am doing wrong?

 

[JamesMcS]

Have you tried putting a break on that line, then putting print me.name in the immediate window, to see what is being closed?

 

[aadebayo]

Yes, when the code is run, the form quits access.

 

[JamesMcS]

But what does it say when you put print me.name? What I mean by that is, click to the left of the offending line in the VBA editor, it should go bold and red. That's a break point, which stops the code. Then, in the immediate window, type 'print me.name'. What comes up?

 

[aadebayo]

The value that prints from me.name is "frmReview", which is the name of the form that I am trying to close.

 

[JamesMcS]

OK so me is looking in the right place. That's a strange one, is there anything in the on close event of the form that might cause access to Quit? It's strange that the whole program closes because database isn't a valid ac- option in docmd.close's arguments, that is a command called Quit.

 

[aadebayo]

Thanks, I have searched every where within the form and could find nothing that warrants the from to quit access. This is really strange, this is why I had to come and seek some advice on this forumn.

 

[JamesMcS]

Does it always close the program no matter what form you're trying to close? Have a go with another one and see if it's that particular form that's causing the problem.

 

[aadebayo]

It is just this form that is causing the problem. Also it does not always quit when that code runs. It only quits if I have a record that has just been created. If I save a record that has just been updated and then close the form, it closes in the correct way.

 

[JamesMcS]

Hmmm someone on here with much more experience will probably tell you why in a flash! Have you tried putting acsaveyes at the end of your docmd statement?

 

[aadebayo]

Thanks, yes I have tried that, but I still get the same result.

 

[JamesMcS]

That is utterly weird! Try putting me.setfocus before docmd.close maybe?

 

[aadebayo]

Thanks very much for your help. It is still not working, but I will see if I can try something else out. If it ever works, I will let you know what I did to make it work.

 

[JamesMcS]

Good luck fella! I'll stay subscribed to see how you get on.

Have a good weekend!

 

[KenHigg]

I would suggest trying this with a new, different form an see what happens. If you still have issues then create a new database and try it.

??

 

[aadebayo]

JamesMCS

Thanks for your help. I have found the code (which is part of the save code) that is causing the problem. When I run the code below

 ' Change default editing to disallow adding new recs.
        frm.AllowEdits = True
        frm.AllowAdditions = False

        'Lock all detail controls by calling glrTglLocked.
        'This sub can be found in basGenericForm.
        Call glrTglLocked(frm, acDetail, True)

        ' Call HandleControls to selectively enable/disable
        ' controls in the header.
        Call glrHandleControls(frm, True, True)
        
        'display navigation buttons if more than one record in set.
        Dim rst As New ADODB.Recordset
        Set rst = frm.RecordsetClone
        If rst.RecordCount > 1 Then
            Call glrTglNavButtons(frm, True)
        Else
            Call glrTglNavButtons(frm, False)
        End If
        
        ' Change form's caption
        frm.Caption = glrcFormCaption & " (Browse Mode)"
        
        ' Shift focus to first control.
         On Error Resume Next
        frm(glrcFirstControl).SetFocus
    
        On Error GoTo ChangeFormStateErr

I have designed a workaround and I am ok. I just left this notice, just in case someone else encounters the same problem.