Error 2585: Closing a form from within a Sub

[GK in the UK]

Is there a way to close a form from within a sub that has a Cancel parameter?

I want to Close a form within the Exit event of a textbox.

I get Error 2585, This action can't be carried out while processing a form or report event.

I'm trying to close with DoCmd.Close acForm, Me.Name

I don't need to save anything. I preceded the call to my Close sub with a Me.Undo

Anticipating the 'Why do I want to do this?':

My form has a textbox for input of an Account key which is mandatory, and it's the first field on the form.

It's a real pain if I open the form and change my mind, I can't close it until I've entered a valid Account key and moved to the next field.

So - I removed the requirement to have a valid Account key in the sub which deals with the lookup. Then, if there is no Account key, I call Cancel = true in the Exit event of the Account key field Exit event..

The effect of this is that if the Account key isn't valid, I pop up a message box, within the Exit event of the Account key field, and it has a Retry/Cancel option.
On press of Cancel, I want to Close the form.

 

[June7]

BeforeUpdate event is normally used for data validation.

 

[GK in the UK]

BeforeUpdate of the control doesn't fire, because nothing changed. It started out empty, and I haven't keyed anything in.

I have validation in BeforeUpdate of the form for other stuff.

I open the form for a new record, and change my mind. I want it closed, gone, unconditionally. No saving of a record.

But, IF I wanted to continue, I MUST enter a valid account key, because I need to lookup the account record to get the defaults for various fields on the form.

The Account key field is the first field, because it makes perfect sense for it to be the first thing that's selected. It works, as long as we are going to continue. We CANNOT proceed unless we enter a valid Account key.

So, in the Exit event of the Account key field, I say, IF Account Key is empty, ask the user if they want to try again, or, if they want to close the form.

And Access, seemingly, won't let me close the form. I get Error 2585 and it doesn't close. Is there a way ?

 

[June7]

If there has been no data input, form should be able to close. Do you have code populating fields when form opens?

Provide all relevant code. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[GK in the UK]

Exit sub of Account key field (LookupAccount is a generic search routine):

Private Sub txtCustSuppRef_Exit(Cancel As Integer)
' txtCustSuppRef:       text box in which we search for CustSuppRef
' lngCustSuppID:        module variable to hold CustSuppID / thCustSuppFK
' is set to 0 if new record, set to thCustSuppFK if existing record
' lookup the record by reference to what the user
' keyed into the search/lookup field txtCustSuppRef

Dim intResponse As Integer
Dim bNoErrors As Boolean

intResponse = 0

' CHECK bCredit
LookupAccount IIf(bCredit, "supp", "cust"), False, txtCustSuppRef, lngCustSuppID, Cancel

If lngCustSuppID = 0 Then
    intResponse = MsgBox _
        ("Please enter a valid account code, or Cancel to close the form", _
            vbRetryCancel, "Enter Account")
    Cancel = True   ' remain in the field
End If

' txtCustSuppRef returns fully qualified e.g. 'ACME01'
' lngCustSuppID returns lngCustSuppID which is also thCustSuppFK  e.g. 1234

If Not Cancel Then
    If lngCustSuppID <> Nz(Me.thCustSuppFk, 0) Then
        ' assign new account values
        Me.thCustSuppFk = lngCustSuppID
        Me.thCustSuppRef = txtCustSuppRef
        If Not Me.NewRecord Then
            ' clear the delivery address reference
            lngDelAddrID = 0
            ' clear any previous delivery address
            LookupDelAddressRecord (lngDelAddrID)
        Else
            LookupCustSuppRecord (lngCustSuppID)
        End If
        SetButtonStatus
    End If
End If

If intResponse = vbCancel Then
    Cancel = True       ' cancel the Exit routine
    Me.Undo
    bAllowClose = True
    SaveAndClose_Click Me, bEditMode
End If

End Sub     ' txtCustSuppRef_Exit

Code for SaveAndClose routine in a separate module (Generic save, close and cleanup routine for several forms):

Public Sub SaveAndClose_Click(frm As Form, bEditMode As Boolean)
On Error GoTo ErrorHandler

Dim bNoErrors As Boolean

If frm.txtLineChanged Then
    ' txtLineChanged flags lines for changes, force
    ' the header dirty so we call BeforeUpdate. we
    ' can edit lines without dirtying the header record
    ' but we must save the header to stamp the edit time
    frm.thDate = frm.thDate
End If

SaveFormRecord frm, bNoErrors   ' nothing will happen if clean

If bNoErrors Then
    ' no validation errors, ok to close
    If bEditMode Then
        ' delete record from tblLocks
        ReleaseHeader (Nz(frm.TransHeaderID, 0))
    End If
    DoCmd.Close acForm, frm.Name
End If

exit_SaveAndClose_Click:
    Exit Sub
    
ErrorHandler:
    bNoErrors = False
    ' see save errors that we can ignore:
    ' http://www.justskins.com/forums/not-prompted-for-saving-105202.html
    ' 2001 - cancelled the previous operation
    ' 2501 if we cancel DoCmd.RunCommand acCmdSaveRecord
    ' 2169 if we cancel Me.Dirty = False
' Added 2585
' this action can't be carried out while processing a form event
    Select Case Err.Number
        Case 2001, 2101, 2115, 2501, 2169, 3314
            ' we expect this if we cancelled the
            ' save in Form_Unload, do nothing
            Resume exit_SaveAndClose_Click
        Case Else
            ' report unexpected error
            MsgBox "SaveAndClose_Click: " & Err.Number & "--" & Err.Description
            Resume exit_SaveAndClose_Click
    End Select

End Sub

 

[June7]

What does LookupAccount sub do? Since it is not a function returning a value to calling procedure, it must do something. You have several procedure calls like this. Where is bCredit set? This is complicated and will probably need the db to do debugging.

 

[GK in the UK]

Thanks June7.

bCredit isn't relevant, it just determines what type of Account record to lookup.

LookupAccount does return values. It returns the values of txtCustSuppRef, lngCustSuppID, Cancel.

It's a search routine across several fields. If I enter a partial Account, key, partial name, or partial PostCode, it pops up a list of all matching records and I can pick one, Or, I can click the cancel button in the pop-up form and lngCustSuppID returns 0.

So textbox txtCustSuppRef passes whatever is in the field in, and it returns with a valid account key, or 0 if the user pressed Cancel.
If it returns with lngCustSuppID as 0, I pop up the message box, which asks if the user wants to try again or close the form.

I've by-passed some stuff to simplify things.

When I try to close the form:
I call Me.Undo to be certain that there's nothing to save, so BeforeUpdate won't be called and the save won't get cancelled.
I set bAllowClose to true so Form_Unload doesn't cancel the close.
Then I call DoCmd.Close acForm, Me.Name

All this code is in the Exit sub (It's not calling anything external)
Then I get my error and the form does not close.

I put a command button on the form with the close code.
If I press the button, it closes.
If I call the button from my Exit sub, I get the error.

DB would take me ages to strip down and sort out some test data. If you're saying it ought to be possible, I'll keep researching.

 

[June7]

I can only wish you luck or hope someone else pops in here because I have never seen this issue, never used Exit event, and cannot determine resolution from posted code.

 

[The_Doc_Man]

The Exit event doesn't fire in form A if focus is moving to some control on form B. It only fires if you are changing focus within the same form. Might that have an effect on what you are doing?

TextBox.Exit event (Access)

Office VBA reference topic

docs.microsoft.com

 

[GK in the UK]

Hi DocMan

The issue isn't with a 'lack' of firing of the Exit event, it fires every time, if I used the BeforeUpdate event it fires only when the data changes.

The field is empty on first open of a form with a new record. I want it to be *not* empty, BeforeUpdate wouldn't fire if we key through an empty field. I need to lookup an associated record to set form defaults so form BeforeUpdate is just too late.

Alternatively, I want to give the option to just close, no save of anything.

So we don't get bogged down with my code which might be tricky to follow, and to satisfy myself that it wasn't a code issue, I've put together a form which shows what I'm after. I want to Close the form on request but Access is preventing it. I can trap the error message of course but that doesn't alter the fact that the form does not close.

I wonder if there is some lower level code that will do this.

 

[bob fitz]

Why not deal with validation in the Form's BeforeUpdate event. See Attached

 

[GK in the UK]

Thanks for that Bob, but it didn't stop me from keying through a blank field.

The form has been working sort of satisfactorily for a while. But what happens (and it will happen in production use), user opens the form to enter a new document, cursor lands in first field. First field is coded to not allow exit unless we enter a valid Account key (because without that the form has no purpose). But user decides to quit, wrong form opened, whatever. What I've had to do, so far, is to enter a valid Account key, go to the NEXT field, which is Reference (free form text with no validation), then press Close. It's a pain.

Clicking the 'Close' button on the form in the Account key field didn't work because my Exit code fired and said, no Account key, no continue.

So I decided to see if I could address the issue. My first thought was to simply pop up a Message Box if the field is empty, and offer Retry/Close. That's when I discovered, Close isn't possible. It seems that Access will absolutely not allow Close from within a control sub. Control has to return to the sub so I'm back to having to enter an Account key. Unless someone knows a way out ? Clicking the Close button on the form would be ok.

Remember, this is for a new record, so there's no database issue with just quitting. No record has been saved. I just want it to go away.

 

[Minty]

Why not force selecting the account key before opening the form from wherever the form is opened from?
And put that value into the form once opened?

 

[bob fitz]

Nearly got the effect you want using "SendKeys" but in any case I've since learnt that "SendKeys" no longer works in Windows 10.

Best suggestion I can make is to move the focus to the Close button, change its Caption and its Backcolor.
Then the user only has to press the Enter key to close the form. See attachment:

 

[GK in the UK]

Bob, I like that solution. I was fixated on Closing the form from the Sub and it didn't occur to me to just SetFocus out of the field. OK, it's an extra click and the user *could* go back to the form and try to save with no Account key but form BeforeUpdate is going to stop them, and why would they try that ?

At the end of the day it's about efficiency and usability and I think you've covered it. It stops an accidental Click-Through which is really what I was after, and it's more intuitive than having to select an arbitrary Account key to then Close. Pretty sure this is going to work in my form. Thank you.

 

[bob fitz]

Bob, I like that solution. I was fixated on Closing the form from the Sub and it didn't occur to me to just SetFocus out of the field. OK, it's an extra click and the user *could* go back to the form and try to save with no Account key but form BeforeUpdate is going to stop them, and why would they try that ?

At the end of the day it's about efficiency and usability and I think you've covered it. It stops an accidental Click-Through which is really what I was after, and it's more intuitive than having to select an arbitrary Account key to then Close. Pretty sure this is going to work in my form. Thank you.

You're welcome. Glad you find the proposal of use