Link to home
Start Free TrialLog in
Avatar of Shadi Saleh
Shadi Saleh

asked on

MS Access: Error when form is unloaded by Design View

I have a form that I wrote an unload code for.

The code should do some checks before closing and closing may be cancelled.

Things are OK either I exit the form by the [x] control button or by a custom exit button.

However, when exiting the form by choosing "Design View" the following error occur.

Run-time error '2467'

The expression you entered refers to an object that is closed or doesn't exist.

Fortunately, I could regenerate the error with a tiny database that is attached.

Here is the form code:

Private Type Record
    stID As Long
    stName As String
    stAge As Long
End Type
    
    
Private Sub DoSomthing()
    
    Dim R As Record
    
    R.stID = Me.st_ID    ' The error occurs here and apparently it applies to the next two lines too. 
    R.stName = Me.st_Name
    R.stAge = Me.st_age
    
End Sub


Private Sub Form_Unload(Cancel As Integer)

    Call DoSomthing

End Sub

Open in new window


I could not find something specific to my case on the net.

Your help is appreciated.
Example.accdb
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Not sure there's much you can do about that. Moving from Form view to Design view is something your users will (hopefully) never do, so it's something you'll have to deal with during design.

What's the purpose of loading data into a User Type when unloading a form?
Avatar of Norie
Norie

In the attached database I don't get an error when going into Design View.
Avatar of Shadi Saleh

ASKER

@Norie: Have you tried it many times? if yes, it could be something in the Access settings and/or version. I am using Office 365.

@Scott McDaniel: It correct that users will never do it, but it make the development debugging process annoying, beside I want to make sure this will not affect any other side of the application.

Away from the problem, Why loading the data into a user type? because, the unload process could be cancelled, and then that info could be used if the user moved from the current record.
Use the OnCurrent event:

Private Sub Form_Current()

    Call DoSomthing

End Sub

Open in new window

Then your Record will be set at all times.

/gustav
Well, the code in your example above does not have the chance to set the Cancel argument to True or False, so I generally use something like:

1.  Add a private variable to your forms code in the declaration section (right below Option Explicit) which looks like:
Private AllowClose as Boolean

Open in new window

2.  In the forms OPen event set this value to False
Private Sub Form_Open

    AllowClose = false

End Sub

Open in new window

3.  Then add code to the Unload event:
Private Sub Form_Unload(Cancel as Integer)

    if me.AllowClose = true then
        Cancel = False
    Elseif UserId = "Dale" Then
        Cancel = false
    Else
        Cancel = True
    End If

End Sub

Open in new window

I generally capture the UserID either when the user logs into the application or using the fOSUserName function, which you can download from here.
Why loading the data into a user type? because, the unload process could be cancelled, and then that info could be used if the user moved from the current record.
If that's your goal, then just use the Current Event to gather that data, not the Unload event.
@Dale Fye: Your suggested code does not have a call to a validation sub/function where my problem occurs.

@Gustav Brock and @Scott McDaniel: This is just a code that was written for only exhibition purpose. In fact the original code does not take the values at the unload time; it takes them inside the current exactly as you are suggesting. The real code is just comparing the values.

While I want to solve the issue and get a workaround, it is also important to me to know why the code is working for normal exit but not for changing the form view?

I really appreciate your contributions and looking for finding the answers.
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Scott McDaniel: Thanks, This confirm to me that it is Access behaviour by design. And Yes, I am looking for a work around.

Briefly, the original database should make some validations on the current record before either leaving to a different record or before exiting the application.

The validation checks if the record has been changed or not, and if yes it logs these changes into a logging table, otherwise it just move or exit.

the validation is a function that is called by custom navigation buttons, custom Exit button and by the unload event.

Now, everything is OK except when I try to return to the design view and continue the work, I have to struggle to exit the form.

in the meantime, I disabled the function call in the unload event to work without problems.

I know that this is will not be a user problem, but it is better for me to find a work around so I can testing it more and more without making the debugging process a kind of tutoring.

So, is there a way to detect the exit source? or is there a clever way to  work around it.

I could make another variable of the record type and use it to compare the values instead of the form controls, but if there is a better  simpler way then I am saving a lot of time and avoiding another cycles of develop/test.

Hope this makes the picture clearer.

Thank you all
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No other users provided what I am looking for. all comments I already know.