Hangs on access runtime, works fine in access

[sstiebinger]

I have an app (CRM) that was working fine and suddenly it hangs when used with the Access runtime (2013 MSI), but when running with regular access (2016 Click-to-run) it works fine...

I've attached a copy of the app (I've stripped the data.)


Attachment 25836
The app consists of 2 files, a front-end and a back-end.

To log into the app:
Username: demo
Pass: demo

Or open the users table on the back-end and create your own user...

The issue is when creating or opening a new opportunity.
When clicking the "new" button, the runtime app hangs; if I force-close the app, I (sometimes) get a message "Can't find the field 'OpportunityID' referred to in your expression".
When opening an existing opportunity, the runtime hangs; no error is ever displayed.

Both of these features work as intended in the 2016 click-to-run app.
These features WERE working just fine in the 2013 Runtime MSI.
I've tried removing all 2013 office updates from the affected users' computers, but it still occurs.

Can anyone please help me?

[CJ_London]

with runtime, you need to ensure that you have covered error handling completely, runtime does not report errors. A quick search through your code and you don't have much error handling - and where you have most of it is 'resume next'

if I force-close the app, I (sometimes) get a message "Can't find the field 'OpportunityID' referred to in your expression".

this should give you a clue - it is an unhandled error.

[sstiebinger]

Ok, I understand that I need to do a better job of error handling, but any clues on what that error might be caused by?
Because no error is thrown at all with MSAccess, just a hang with the runtime.

[sstiebinger]

So I changed the function for Viewing the opp to:

Private Sub ViewOpportunity()    Dim strLinkCriteria As String
    strLinkCriteria = "[OpportunityID]=" & Me![OpportunityID]
    ' DoCmd.OpenForm "frmOpportunityDetail", , , strLinkCriteria
    ' Forms.frmOpportunityDetail.AllowAdditions = False
    MsgBox strLinkCriteria
End Sub

And when I run it on the runtime I get a messagebox with the text

[OpportunityID]=5

So I know the ID is populating...

As soon as I uncomment the DoCmd.OpenForm then the runtime hangs... but the form opens just fine in the C2R...
I'm just stumped on where to go from here.

[ItsMe]

The following looks like a Design Time change. Design Time is not Run Time.

Forms.frmOpportunityDetail.AllowAdditions = False

I believe there is a workaround that allows you to do design time changes during/via Run Time. I believe you can do it if you open the form in Hidden Mode.

[sstiebinger]

I'm not sure I understand...

I want the form to be displayed, not hidden... this form is to view the details of the record that was selected.
The AllowAdditions = False is to prevent creating a new record when it was opened to view an existing record.
Regardless, the Runtime hangs even with the AllowAdditions line commented out, and the new button doesn't have this code.

Something in DoCmd.OpenForm is causing the hang, but I don't know how to find out what.

The frustrating thing is that normally access would show me the error and I can step through/debug the code, but Access won't give me an error at all!
It runs perfectly in Access, but when I use the runtime it fails and gives me no clues as to why...

[ItsMe]

Yeah, I understand. I know you want the user to interact with the form. I was thinking that AllowAdditions was a design time change. I am not positive it is. It probably is not a Design Time change.

Not all VBA that executes in a full version of Access is available during RunTime. So, the code will compile (Compile Time) but will not execute during Run Time via the Run Time engine. This is the message I was trying to convey.

There is a special thing that you can do with the Form object to work with certain properties via the Run Time engine and during Run Time. The trick is to execute the code while in Hidden Mode.

[ssanfu]

Disclaimers:
I do not have A2013, A2016.
I have only played around with Runtime, never actually used it.
Opinions expressed are only mine...


So I looked at your dB. The first thing I would change is to add these two lines at the top of EVERY code module:

Option Compare Database
Option Explicit

If you go to the IDE/Tools/OPTIONS, on the "Editor" tab, check the option "Require Variable Declaration". This will automatically add "Option Explicit" to NEW modules created.

----------------------------------

Looking at your code, there are two forms you have the sub "Sub ViewOpportunity()": "sfrmOpportunityList" and "sfrmAccountDetailOpportunity".
I think the problem might be this line, specifically the Me part:

strLinkCriteria = "[OpportunityID]=" & Me![OpportunityID]

When you use the "Me" syntax, you are saying "Look for a control named "OpportunityID" and return the current value. But you do not have a control named "OpportunityID" on the form.

The full version of Access might know to find a field in the record source instead of a control to get the value, but the run time version might be more literal, looking specifically for a control.

I would add the field (delete the label) to the detail section of forms "sfrmOpportunityList" and "sfrmAccountDetailOpportunity", allowing Access to name the control "OpportunityID".
Next, on form "frmOpportunityDetail", I would also add a control for "OpportunityID".



After testing, (if it works) I would set the visible property to the 3 controls to NO.
On my forms (in design view), I set the BG color of hidden (not visible) controls to a pale yellow..... easier to know which are hidden controls.

[ItsMe]

...When you use the "Me" syntax, you are saying "Look for a control named "OpportunityID" and return the current value. But you do not have a control named "OpportunityID" on the form....

Just an FYI ...
Me![OpportunityID] is looking to the Field within the Forms Recordset and is not looking at a control, regardless of whether or not the control exists. I think this is what you are saying, Steve, and this is how I understand it to be. The Runtime engine does not have an issue with this.

I am wondering if there is not corruption surrounding the form's name. Or maybe the issue is in another object that is somehow tied to the form we are focused on.

What I do to simulate RT environments is change the extension from accdb to accdr.

[sstiebinger]

Thanks for the replies, unfortunately I'll have to do more testing on Monday.
I will definitely try the .accdr to see if I can get more details from Access.

While a corrupt form/etc isn't something I considered, I can see how that might be a possibility. Something that Access can recover from but the runtime is not equipped to deal with.

I do know that I created a new database and imported the structure to create the demo zip that I attached here; which I know has solved some corruption issues in the past.
That being said, I suppose it's possible that there's some deeper corruption that may have carried over through a structure copy.
I'll add creating a new form to my troubleshooting steps for Monday.

[ssanfu]

Just an FYI ...
Me![OpportunityID] is looking to the Field within the Forms Recordset and is not looking at a control, regardless of whether or not the control exists. I think this is what you are saying, Steve, and this is how I understand it to be. The Runtime engine does not have an issue with this.

This is good to know.
In the past (just a couple of times), I've had problems when the PK field was not bound to a control on a form. So now I always add the PK field to a form.

[sstiebinger]

@Steve: The runtime can definitely deal with that line of code, because I was able to trigger a msgbox with strLinkCriteria (see earlier post.)

@ItsMe: I just ran the app as .accdr within Access and was unable to replicate the hang... it definitely appears to be something specifically related to the runtime executable.

I'll re-create the forms on Monday and see if it's possibly a form corruption. Thanks again.

[ItsMe]

This is good to know.
In the past (just a couple of times), I've had problems when the PK field was not bound to a control on a form. So now I always add the PK field to a form.

An interesting thing to test would be if the NewRecord property = True and the Me!MyPK returns a value. I believe it will unless the Form's RecordSource is looking to an SQL server.

[ItsMe]

@Steve: The runtime can definitely deal with that line of code, because I was able to trigger a msgbox with strLinkCriteria (see earlier post.)

@ItsMe: I just ran the app as .accdr within Access and was unable to replicate the hang... it definitely appears to be something specifically related to the runtime executable.

I'll re-create the forms on Monday and see if it's possibly a form corruption. Thanks again.

Edit: Are you using an accde? You need to use the RT version that is later than the version of Access that created the accde.

So use 2010 to create the accde if you can.

[CJ_London]

A lot of posts since my last one.

The only thing I can add is that the error may not be in the code you provided, but in one of the form open events which is erroring and rolling back.

Your code for frmOpportunityDetail says

Private Sub Form_Load()
If Me.NewRecord Then Me.AccountID = OpenArgs
End Sub

but you do not supply an openarg in your openform command - so if your Me![OpportunityID] value does not exist in tblOpportunities you will not be assigning anything.

Further - and please correct me if I'm wrong about this bit but I believe it to be true - the load event means the recordset is defined, but a specific record does not have the focus and therefore newrecord is undefined - so you cannot assign a value since newrecord won't be true at that point.

Suggest this code either needs to be in the current event when a record gets the focus, or change your code to Me.cboAccountName.defaultvalue which it the name of the control for AccountID and can be assigned in the load event - I see you have the usual confusion over names because you are using lookups in your table design which also makes it harder to track down errors.

This may explain the intermittance - sometimes you are trying to open the form with an opportunityID which does not exist in tblOpportunities, generating an error that is presented somewhere else and often as something else.

As I mentioned before - proper error handling is very important. Extensive use or Resume Next merely shoves it under the carpet for it to creep out somewhere else and possibly in an apparently unrelated bit of code. See this link

https://msdn.microsoft.com/en-us/library/5hsw66as.aspx

and in particular these paragraphs

If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler cannot handle the error. Control returns to the calling procedure.
If the calling procedure has an enabled error handler, it is activated to handle the error. If the calling procedure's error handler is also active, control passes back through previous calling procedures until an enabled, but inactive, error handler is found. If no such error handler is found, the error is fatal at the point at which it actually occurred.

Each time the error handler passes control back to a calling procedure, that procedure becomes the current procedure. Once an error is handled by an error handler in any procedure, execution resumes in the current procedure at the point designated by the Resume statement.

Note: I see ItsMe has picked up the point I am making in post #13 as the potential cause of the error