Rominus Rominus - 1 month ago 6
SQL Question

Form button intermittently returns runtime error 2101

I have a simple form on which the user selects a value from a list in a combo box, then clicks a button to open another form filtered by the selection. In development and testing it works fine, but in Prod users are occasionally receiving runtime error 2101: 'The setting you entered isn't valid for this property'. If the user restarts their machine the error doesn't persist, at least for a while.

This happens when the user clicks the 'Ok' button, which closes the selection form and opens the main interface form. The code for the button is just:

Private Sub btnOK_Click()
DoCmd.OpenForm "CC_Tracker_from_form", acNormal, , , acFormEdit
DoCmd.CLOSE acForm, "frmCoord_Selector"
End Sub


When the user closes the error popup, the selection form remains visible on top of the main interface, which sort of makes sense if the failure is in the
DoCmd.CLOSE
line, since Access would have already opened the main form.

Why would error 2101 trigger only some of the time, when the user performs exactly the same action (even the same selection from the combo box)?

I don't think this error has anything to do with the underlying Record Source for the main form, but just in case here's that code:

SELECT
[Bunch of columns],
IIf(dbo_CC_Tracker.RISK_LVL='Low',Null,dbo_CC_Tracker.CHRA+365) AS CHRA_Next,
IIf(dbo_CC_Tracker.RISK_LVL='High',dbo_CC_Tracker.[ICP/Review]+29,
IIf(dbo_CC_Tracker.RISK_LVL='Medium',dbo_CC_Tracker.[ICP/Review]+89,Null)) AS ICP_Next,
IIf((dbo_CC_Tracker.RISK_LVL='Low' Or dbo_CC_Tracker.RISK_LVL='Medium'),Null,dbo_CC_Tracker.F2F+179) AS F2F_Next,
IIf(dbo_CC_Tracker.RISK_LVL='Low',Null,dbo_CC_Tracker.ICTCont+89) AS ICT_Next,
dbo_CC_Tracker.HTR_Letter +30 AS Final_Follow,
dbo_CC_Tracker.ASSIGNED +59 AS Deadline_1,
dbo_CC_Tracker.ASSIGNED +89 AS Deadline_2
FROM dbo_CC_Tracker
WHERE
(((dbo_CC_Tracker.ASSIGNED_CARE_COORDINATOR)=Forms!frmCoord_Selector!cmbCoords)
And dbo_CC_Tracker.[CLOSE] is null)
Or Forms!frmCoord_Selector!cmbCoords Is Null;

Answer

Based on the idea that the first form may be closing before passing the dropdown selection to the second form I've changed from closing the form, DoCmd.CLOSE acForm, "frmCoord_Selector", to hiding the form, Me.Visible = False which should keep the selection active despite the first form no longer displaying.