DoCmd.Close errors

prema · December 15th, 2008, 06:46 PM

Hi There,

I am trying to close one form and open another with a notinlist event. My challenge is that after clicking yes to the question this customer is not in the list do you want to add it? I get the error message Close Action Cancelled, if I press end it closes anyway. I have tried taking the quotation marks off of frmVehiclesPayment and putting Me.VehiclesPayment and also Me.frmVehiclesPayment all to no avail. The debugger highlights the line with the DoCmd.Close command in it. Anyone see the problem with this code? or ways that I can get around this bug.

Thanks,
Paul

Here is the code

Private Sub Customer_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & " is not in the Customer List" & CR & CR
Msg = Msg & "Click No, if you may have misspelled this person's name." & CR & CR
Msg = Msg & "Click Yes, if you are ready to add this new customer."
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then

Response = acDataErrContinue

DoCmd.OpenForm "frmCustomerNewLicense", acNormal, , , acFormAdd

DoCmd.Close acForm, "frmVehiclesPayment", acSaveYes

End If

End Sub

mmcdonal · December 16th, 2008, 10:49 AM

It might be that the form you are opening is taking the focus. Generally I close the current form before I open another. So perhaps if you do this:

DoCmd.Close acForm, "frmVehiclesPayment", acSaveYes
DoCmd.OpenForm "frmCustomerNewLicense", acNormal, , , acFormAdd

prema · December 16th, 2008, 12:25 PM

Thanks for your help. By changing the order of the commands it simply prevents frmCustomerNewLicense from opening.

mmcdonal · December 16th, 2008, 01:03 PM

Normally when I do this, I use this code:

Dim sForm As String

sForm = "frmCustomerNewLicense"

DoCmd.Close
DoCmd.OpenForm sForm, acNormal, , , acFormAdd

Is frmVehiclesPayment bound? If so, no acSaveYes is needed.

mmcdonal · December 16th, 2008, 01:06 PM

Also, did you mean:

If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
Response = acDataErrContinue
Else
DoCmd.OpenForm "frmCustomerNewLicense", acNormal, , , acFormAdd
DoCmd.Close acForm, "frmVehiclesPayment", acSaveYes
End If

prema · December 16th, 2008, 02:57 PM

Still no luck.

Everything in the code seems to work until that last line.

mmcdonal · December 16th, 2008, 03:05 PM

Try this, then, since Access doesn't like to get values at runtime:

Dim sDoc1 As String
Dim sDoc2 As String

sDoc1 = "frmCustomerNewLicense"
sDoc2 = "frmVehiclesPayment"

DoCmd.OpenForm sDoc1, acNormal, , , acFormAdd
DoCmd.Close acForm, sDoc2, acSaveYes

And then try switching them around again perhaps.

prema · December 16th, 2008, 03:22 PM

Ouch, same results either way.
Thank you for your help here.

Since it is actually doing what I want it to do, is there a way to just skip the debug message and end the debugger?

kuznickic · February 22nd, 2009, 07:46 AM

Basically you would just need to handler the error with an error event handler. You could look up in VBA help but basically after the close command you would write the error handler and then have it return to the open command and you shouldn't see the message window because access would see the error as normal behavior.

boblarson · February 24th, 2009, 01:05 AM

The problem is that for your NOT IN LIST event you should be opening the form to add to the list in dialog mode so that the rest of the code doesn't happen until you close the form. I'm not sure which form your combo is on that you have the not in list event for, but I'm going to assume that you are wanting the "frmCustomerNewLicense" to open to put the new data into. If so it woiuld be:

Code:

Private Sub Customer_NotInList(NewData As String, Response As Integer)
 
Dim Result
Dim Msg As String
Dim CR As String
 
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
 
    Msg = "'" & NewData & " is not in the Customer List" & CR & CR 
    Msg = Msg & "Click No, if you may have misspelled this person's name." & CR & CR
    Msg = Msg & "Click Yes, if you are ready to add this new customer."
        If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
 
            DoCmd.OpenForm "frmCustomerNewLicense", acNormal, , , acFormAdd, acDialog
 
           Response = acDataErrContinue ' this line goes AFTER the form has added the data
 
          DoCmd.Close acForm, "frmVehiclesPayment", acSaveNo
 
 
         End If
 
End Sub

And the acSaveNo means to not save DESIGN CHANGES to the form. Many people mistakenly believe that using acSaveYes will save their record, but it doesn't. It is for saving design changes to a form or report (for example if you set a filter so it saves persistent).