Avatar of wlwebb
wlwebb
Flag for United States of America asked on

Access Form/Subform - Subform Unbound Textbox Control Source calc when main form opens

Hello all
I have a Form with 5 SubForms.

On one of the Subforms I have an unbound textbox that it's control source is to use IIF statements to look at the subform's bound boxes in test them based upon some criteria.

When the Main Form opens, this Subform's unbound box is not updating correctly.  It seems that it isn't requerying all of the bound boxes on that Subform to recalc the unbound textbox.  

I tried on the subform's OnLoad event to requery each boundbox and requery each unbound box.  but it still doesn't update that unbound box correctly.

The Main Form is [frm_Data]
The Subform not updating is [frm_Documents]

The unbound boxes that have to be calced in order are
tbExpirDateRqd1
tbExpiration1Test

Is there a way on the load of that Main form to force it to recalc or requery all of the forms.  The subform works right if I physically select each individual bound box on the sub even if I leave it null.
Microsoft Access

Avatar of undefined
Last Comment
wlwebb

8/22/2022 - Mon
harfang

Please post your “IIf expressions” exactly. For this to work, it must be easy for Access to create the correct dependencies chain. If you hide that information (e.g. within strings), it will be a nightmare to create your own refresh code.

(°v°)
wlwebb

ASKER
The code for the First
=IIf(nz([tbExpirDateRqd1],0)=0,(IIf(nz([W2G_Winner_Docs_1_Type],0)=0,"No Type",IIf(nz([W2G_Winner_Docs_1_Nbr],"")="","No Doc Nbr","Yes"))),(IIf(nz([W2G_Winner_Docs_1_Type],0)=0,"No Type",IIf(nz([W2G_Winner_Docs_1_Nbr],"")="","No Doc Nbr",IIf(nz([W2G_Winner_Docs_1_Expiration],"")="","No Expiration Date","Yes4")))))

Open in new window


And the 2nd
=IIf(nz([tbExpirDateRqd2],0)=0,(IIf(nz([W2G_Winner_Docs_2_Type],0)=0,"No Type",IIf(nz([W2G_Winner_Docs_2_Nbr],"")="","No Doc Nbr","Yes"))),(IIf(nz([W2G_Winner_Docs_2_Type],0)=0,"No Type",IIf(nz([W2G_Winner_Docs_2_Nbr],"")="","No Doc Nbr",IIf(nz([W2G_Winner_Docs_2_Expiration],"")="","No Expiration Date","Yes4")))))

Open in new window

harfang

The expressions are looking at four other objects. I assume that the W2G_* names refer to fields in the data source, and tg* names to text boxes.

You said that “tbExpirDateRqd1” is the first text box to be recalculated, but that name appears in the expression, which would trigger a #Name error. What is the name of the first text box, and what is the content of tbExpirDateRqd1? Is it another expression?

Ideally, if the expression uses only fields, and not other text boxes, it can be included in the query to which the form is bound. This ensures the best possible automatic refresh and display when navigating records.

Incidentally, the following expression should be equivalent to the nested IIfs:
=Switch(
nz([W2G_Winner_Docs_1_Type],0)=0, "No Type",
nz([W2G_Winner_Docs_1_Nbr],"")="", "No Doc Nbr",
nz([tbExpirDateRqd1],0)=0, "Yes",
nz([W2G_Winner_Docs_1_Expiration],"")="", "No Expiration Date",
True, "Yes4")

Open in new window

I would also try to eliminate the Nz() calls, but that's irrelevant at this time.

To simplify: if tbExpirDateRqd1 contains another expression based on fields, it will be ultimately possible to move the expression to the query. In any case, we need to follow the dependencies one step further.

(°v°)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
wlwebb

ASKER
Harfang,
Thanks for reviewing.  have never used the "Switch" function.

Yes there is a 2nd unbound text box with an expression.  I use that text box to determine If the document Type requires an expiration date.  Some documents require a date and some don't.  No way around it.

That 2nd unbound Textbox is [tbExpirDateRqd1] and it's formula is set when the combobox selection of the field [W2G_Winner_Docs_1_Type] is made:

The code within the vb AFterupdate that gets the field from the table if the particular Document requires an expiration date is:
        Me.tbExpirDateRqd1 = Me.W2G_Winner_Docs_1_Type.Column(3)
That combobox's full vb is

Private Sub W2G_Winner_Docs_1_Type_AfterUpdate()

    If (Me.W2G_Winner_Docs_1_Type) = (Me.W2G_Winner_Docs_2_Type) Then
        MsgBox "You can NOT select the same Document Type for both Identification Documents.  Please Select a different ID type.", vbOKOnly
    Else
        Me.tbExpirDateRqd1 = Me.W2G_Winner_Docs_1_Type.Column(3)
        If (Me.W2G_Winner_Docs_1_Type.Column(3)) = 0 Then
            Me.W2G_Winner_Docs_1_Expiration.TabStop = False
            Me.W2G_Winner_Docs_1_Expiration.Visible = False
        Else
            Me.W2G_Winner_Docs_1_Expiration.TabStop = True
            Me.W2G_Winner_Docs_1_Expiration.Visible = True
            
        End If
    End If
    Me.Requery
    
End Sub

Open in new window


PLEASE NOTE:
That 2nd Unbound textbox is opening Null when the form loads.  Even if I'm opening a completed form that TB isn't calculating on its opening... which would effect the calculation of the {tbExpiration1Test]
ASKER CERTIFIED SOLUTION
wlwebb

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
harfang

This is strange: I distinctly remember writing an answer to your last comment. Maybe I closed the tab while in [Preview]? Anyway, I'm sorry your question slipped through.

To continue building the dependencies, enter an expression in tbExpirDateRqd1 instead of writing to in in code:

    = W2G_Winner_Docs_1_Type.Column(3)

While we are at it, create a conditional format for the control W2G_Winner_Docs_1_Expiration, using the expression

    = [W2G_Winner_Docs_1_Type].Column(3) = 0

And make the control disabled when true. In interface design, it is generally considered better to disable unavailable options rather than to hide them. Anyway, this is just for testing, you can revert to hiding the control once it works.

Finally, remove the AfterUpdate event completely (you can comment it out), and use a BeforeUpdate event instead:

Private Sub W2G_Winner_Docs_1_Type_BeforeUpdate(Cancel As Integer)

    If (Me.W2G_Winner_Docs_1_Type) = (Me.W2G_Winner_Docs_2_Type) Then
        MsgBox "You can NOT select the same Document Type for both Identification Documents.  Please Select a different ID type.", vbOKOnly
        Cancel = True
    End If
    
End Sub

Open in new window

With this, we have normally added one more link to the chain. The idea is that Access should be entirely responsible for the synchronization of all the expressions on the form (and in the underlying query).

(°v°)
wlwebb

ASKER
Close question.. no solution
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.