Form not closing

[Paul Cooke]

hi guys,

well after hours of searching I really cannot figure out why my form is not closing using docmd.

I have form1 which you select a name from this enables an option group where you choose the form type, click a command button and form2 opens.

all the fields on form2 are disabled except the first field (title)

This is the actual code in the lostevent of that field

Private Sub cboProductonEventTitle_LostFocus()
Dim Response As Integer

If IsNull(Me.cboProductonEventTitle) = True Then
    Response = MsgBox("As you do not have a Production or Employer Name or a Production or Event Title, you can not continue entering details on this database form." & _
    vbCrLf & vbCrLf & "If the patient is with you, please complete a paper treatment form." & vbCrLf & vbCrLf & _
    "Otherwise please click 'ok' below to continue.", vbInformation + vbOKOnly, "Cancel Data Entry")
    
    If Response = vbOK Then
    Response = MsgBox("Please put the form in question to one side and email Paul with the details", vbInformation + vbOKOnly, "Cancel Data Entry")
   
        

End If
End If
End Sub

I need something to put at the end to close this form and open the navgation form.

I have tried all types of docmd.close but nothing seems to work I keep getting Run time error 2585 - This action can't be carried out while processing a form or report event.

opening another form is not a problem

I am tottally lossed please could someone help me with this

many thanks

 

[John Big Booty]

I beleive that this error is caused if you try to close a form whilst a subroutine is still running, try inserting Cancel = True prior to running your Docmd.Close. Bear in mind that Docmd.Close will close the form that called this event so you may need to be specific about which form you wish to close.

 

[Paul Cooke]

Thanks for the reply John but sadly it didn't work - revised code shown below

Private Sub cboProductonEventTitle_LostFocus()
Dim Response As Integer
Dim Cancel As Integer

If IsNull(Me.cboProductonEventTitle) = True Then
    Response = MsgBox("As you do not have a Production or Employer Name or a Production or Event Title, you can not continue entering details on this database form." & _
    vbCrLf & vbCrLf & "If the patient is with you, please complete a paper treatment form." & vbCrLf & vbCrLf & _
    "Otherwise please click 'ok' below to continue.", vbInformation + vbOKOnly, "Cancel Data Entry")
    
    If Response = vbOK Then
    MsgBox "Please put the form in question to one side and email Paul with the details", vbInformation + vbOKOnly, "Cancel Data Entry"
    Cancel = True
    DoCmd.Close acForm, "NewTreatment"
    DoCmd.OpenForm "Navigation"
   
        

End If
End If
End Sub

Any further ideas would be a recieved greatly !

 

[John Big Booty]

I can't reproduce the error you are encountering in my sandbox DB, which leads me to suspect that there must be some other code that is being fired in your form NewTreatment.

Also given that your MsgBox's are both vbOKOnly there is no need for your second IF statment in your code so it can be simplified to;

Private Sub cboProductonEventTitle_LostFocus()
Dim Response As Integer
Dim Cancel As Integer

If IsNull(Me.cboProductonEventTitle) Then
    Response = MsgBox("As you do not have a Production or Employer Name or a Production or Event Title, you can not continue entering details on this database form." & _
    vbCrLf & vbCrLf & "If the patient is with you, please complete a paper treatment form." & vbCrLf & vbCrLf & _
    "Otherwise please click 'ok' below to continue.", vbInformation + vbOKOnly, "Cancel Data Entry")
    
    MsgBox "Please put the form in question to one side and email Paul with the details", vbInformation + vbOKOnly, "Cancel Data Entry"
    DoCmd.Close acForm, "NewTreatment"
    DoCmd.OpenForm "Navigation"
   
        

End If
End Sub

Are you able to post a copy of your DB ('03 format if possible)

 

[Paul Cooke]

thanks again John,

I can certainly post a copy but it is in 07 format and I am not sure how or if I can convert it backwards?

 

[Paul Cooke]

07 format copy attached.

thanks for your time on this

 

[John Big Booty]

You can try File, Save As. Although if you've used any features not available in '03 this will not be possible. If this is not possible post you DB as is and I'll take a look at it latter when I'm at a machine with '07 on it.

 

[Paul Cooke]

thanks again John

 

[John Big Booty]

It seems that the DoCmd.Close is not compatible with the Lost Focus event, are you able to move the code to say the Before Update event?

 

[gemma-the-husky]

have you opened a recordset and not closed it within the form, after using it? that might stop it closing.

the other possibilty is that the lost focus event might not be the best one to choose

maybe its also - if the null field you are testing is a required field - then you wont be able to save the record - and that might stop the form closing - especially if you have silent validation checks elsewhere - say in the form's before update event

 

[John Big Booty]

Dave I've found that the DoCmd.Close, will always throw up a RunTime Error 2585 if it is called in the Lost Focus event

 

[Paul Cooke]

thanks for the replies guys.

John, unfortunately It needs to be in the lostfocus (or I am assuming it does?)as it is code that follows on from the previous field. As Below

Private Sub cboProductionEmployerName_AfterUpdate()
'Check to see if a value has been entered into the field
If IsNull(Me.cboProductionEmployerName) = False Then
    
    Me.cboProductonEventTitle.Enabled = True
    Me.cboProductonEventTitle.SetFocus
    Me.txtTreatmentDate.Enabled = True
    Me.txtTreatmentTime.Enabled = True
    Me.cboTreatmentLocationGiven.Enabled = True
    Me.txtTreatmentReason.Enabled = True
    Me.txtTreatmentClinicalObs.Enabled = True
    Me.txtTreatmentGiven.Enabled = True
    Me.txtTreatmentRecommendations.Enabled = True
    Me.cboDisposal.Enabled = True
    Me.cboStaffName.Enabled = True


End If
End Sub

Private Sub cboProductionEmployerName_LostFocus()
If IsNull(Me.cboProductionEmployerName) = True Then
Me.cboProductonEventTitle.Enabled = True
    Me.cboProductonEventTitle.SetFocus
    End If
End Sub

Private Sub cboProductonEventTitle_AfterUpdate()
If IsNull(Me.cboProductonEventTitle) = False Then
    Me.txtTreatmentDate.Enabled = True
    Me.txtTreatmentDate.SetFocus
    Me.txtTreatmentTime.Enabled = True
    Me.cboTreatmentLocationGiven.Enabled = True
    Me.txtTreatmentReason.Enabled = True
    Me.txtTreatmentClinicalObs.Enabled = True
    Me.txtTreatmentGiven.Enabled = True
    Me.txtTreatmentRecommendations.Enabled = True
    Me.cboDisposal.Enabled = True
    Me.cboStaffName.Enabled = True

End If
End Sub


Private Sub cboProductonEventTitle_LostFocus()
Dim Response As Integer
Dim Cancel As Integer

If IsNull(Me.cboProductonEventTitle) = True Then
    Response = MsgBox("As you do not have a Production or Employer Name or a Production or Event Title, you can not continue entering details on this database form." & _
    vbCrLf & vbCrLf & "If the patient is with you, please complete a paper treatment form." & vbCrLf & vbCrLf & _
    "Otherwise please click 'ok' below to continue.", vbInformation + vbOKOnly, "Cancel Data Entry")
    
    If Response = vbOK Then
    Me.Undo
    MsgBox "Please put the form in question to one side and email Paul with the details", vbInformation + vbOKOnly, "Cancel Data Entry"
    
    DoCmd.Close acForm, "NewTreatment"
    DoCmd.OpenForm "Navigation"
   
        
End If
End If
End Sub

As you will see the event should only trigger if there is nothing in the cboProductionEmployerName field.

I'm think maybe I could put a command button on the form "cancel" or something like that although from a users ppint of view it would be one more thing for them to have to do.


Dave:

There is a Recordset on the form but it does seemed to be set to close (if the code below is correct?

Private Sub Form_Close()
'Automatically updates the reference number by 1
If Me.OpenArgs = "AddMode" Then
    lngTrNum = lngTrNum + 1
    Set rs = CurrentDb.OpenRecordset("ReferenceNumbers")
    rs.Edit
    rs.Fields("NextTreatmentNumber").Value = lngTrNum
    rs.Update
    rs.Close
    Set rs = Nothing

End If

End Sub

As always many thanks for your continued help guys

 

[John Big Booty]

The fix is easy, can't believe I didn't think of this earlier Simply remove the close form from the Lost Focus event, and add "CloseFrm" as OpenArgs to the DoCmd.OpenForm, so your code will look like;

Private Sub cboProductonEventTitle_LostFocus()
Dim Response As Integer
Dim Cancel As Integer

If IsNull(Me.cboProductonEventTitle) Then
    Response = MsgBox("As you do not have a Production or Employer Name or a Production or Event Title, you can not continue entering details on this database form." & _
    vbCrLf & vbCrLf & "If the patient is with you, please complete a paper treatment form." & vbCrLf & vbCrLf & _
    "Otherwise please click 'ok' below to continue.", vbInformation + vbOKOnly, "Cancel Data Entry")
    
    MsgBox "Please put the form in question to one side and email Paul with the details", vbInformation + vbOKOnly, "Cancel Data Entry"

    DoCmd.OpenForm "Navigation", , , , , , "CloseFrm"
   
        

End If
End Sub

Now in the On Load event of your form Navigation put;

If OpenArgs = "CloseFrm" Then
     DoCmd.Close acForm, "NewTreatment"
End If

 

[Paul Cooke]

I am actually smiling !! thanks John sometimes the most simplist soultions are the ones we forget !!

 

[John Big Booty]

I am actually smiling !! thanks John sometimes the most simplist soultions are the ones we forget !!

It woke me up in the middle of the night