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
Shadi SalehAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
NorieAnalyst Assistant Commented:
In the attached database I don't get an error when going into Design View.
0
Shadi SalehAuthor Commented:
@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.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gustav BrockCIOCommented:
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
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Shadi SalehAuthor Commented:
@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.
0
Gustav BrockCIOCommented:
it is also important to me to know why the code is working for normal exit but not for changing the form view?

Well, it is how it works. Nothing to do about it. You'll just have to accept - resistance is futile.

/gustav
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
it is also important to me to know why the code is working for normal exit but not for changing the form view?
When you move to Design view while using the form in Form view, the value in your form controls (i.e. the "Me.st_ID") no longer exist. You're moving to Design view - ostensibly to make changes - so there's no reason to save them. As Gustav and I have both said, that's normal, expected behavior and you cannot change it.

If all you're interested in is the why, then I believe we've answered your questions. If you're also interested in a work around, you'd have to tell us more about what you're doing.
0
Shadi SalehAuthor Commented:
@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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd set a global variable that you could check when exiting the form. In a standard module, in the General Declarations section:

Public gDevMode As Boolean

When you are in dev mode set that to True:

Public gDevMode As Boolean = True

In your form, check that value:

If Not gDevMode Then
  '/ set your user type values
End If
0
Shadi SalehAuthor Commented:
Well, I think I will just do a work around by storing the form fields in a temp variable or use a flag.

Thanks all.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shadi SalehAuthor Commented:
No other users provided what I am looking for. all comments I already know.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.