How to Trap #Error within Calculated Control without VBA

[zmbd]

So, there's the ole "Parent" form and "Child" form.
These work very well and the information pulled is correct; however, we want the elapsed time between entries that show on the sub form.

Not an issue, I've done this a ton of times in reports and on a normal form:
Unbound Textbox named z_ctrl_txt_elap seddays
The control source is (I've stepped this for reading):

=DateDiff("d",
   DLast("[history_date]",
      "qry_history_subform",
      "[History_fk_inventory]=
         Forms![ParentFormName]![SubFormName]![history_fk_inventory] 
         AND [history_pk]<=
            Forms![ParentFormName]![SubFormName]![history_pk]-1"),
   [history_date]))

When the Parent is opened all is well. The dates calcuted are correct and I'm happy; however, occasionally, the subform is used directly (basically when I don't need a filter) and ofcourse Forms![ParentFormName]![SubFormName] tosses an #ERROR at me. Now, I've written a custom function that checks to see if Forms![ParentFormName]![SubFormName] tosses an error and if so then runs the formula without that reference. However, I'd like to do the check directly within the control. I've tried a dozen different checks and the like; however, I've been stumped with this one. "IIF" doesn't work and so forth.

So, is there a way to trap #ERROR in an unbound control?
[{edit:2013.02.1 2} to be clear, VBA is not the answer I need as the form will occationally be opened on workstations where the VBA is disabled]

[Seth Schrock]

Wouldn't an Nz() function work?

[NeoPa]

I would suggest the use of Nz().

[zmbd]

No.
Second thing I tried.
When the form is opened as a "stand alone" instead if as a subform, the function refers to the control via the parent!subform construct which resolves to a #NAME error which results in a #ERROR

I even tried ME![field] construct within the control just to see if it would work in V2010

[Seth Schrock]

Could you make it so that you had textboxes on your subform that contained the values you need? When opened as a subform, the values from the parent form could be passed to the controls on the subform. When opened as a stand alone form, you could make them equal anything you wanted using the Nz() function. So something like

=Nz(Forms!ParentFormName!History_fk_Inventory, Date())

I don't have a database where I can test my idea, so I'm not sure exactly how well this would work (or if it would work).

[zmbd]

I even tried that following:

=IIf(IsError([Forms]![ParentName]![SubFormName]![history_pk]),
    "istrapped",
    (DateDiff("d",
        DLast("[history_date]",
            "qry_history_for_inventory_parent_subform",
            "[History_fk_inventory]=Forms![ParentName]![SubFormName]![history_fk_inventory] 
                AND
                [history_pk]<=Forms![ParentName]![SubFormName]![history_pk]-1"),
            [history_date])))

[Seth Schrock]

Instead of referencing the form, could you use a subquery to get the data? I'm not sure if there is a way to have the WHERE clause of the subquery narrow it down to one record without having to reference a control on your subform though.

[mshmyob]

just subscribing ......

[zmbd]

Duh:

I had done this once; however, I goofed and was checking the subform control and not the parent control.
Bound Control:
Hidden control in header of SubFormName
Name: z_ctrl_txt_chec kforparent
Control Source set to:

=[Forms]![ParentFormName]![Inventory_pk]

I still had the [SubFormName] in the reference, I had changed the control name, just didn't follow thru on the details... DUH.

Unbound Control:
in the details section of SubFormName
Name: z_ctrl_txt_elap seddays
Control Source set to:

=IIf(IsError([z_ctrl_txt_checkforparent]),
    "istrapped",
    (DateDiff("d",
        DLast("[history_date]",
        "qry_history_for_subform",
        "[History_fk_inventory]=Forms![ParentFormName]![SubFormName]![history_fk_inventory] 
            AND
            [history_pk]<=Forms![ParentFormName]![SubFormName]![history_pk]-1"),
        [history_date])))

Seth jogged my mind to take another look at my prior attempt in the IIF and sure enough, when I did my cut and paste (the names on the forms are long) I didn't pull out the subformname reference when I set my hidden control up the first time.

Now I'll go back and take that "istrapped" out of the control and put in something useful... I hope :)

[Seth Schrock]

Thats what I was hoping to do :)

[mshmyob]

I am not at a computer to try this but could you create a user defined error using the CVErr function and then trap it.


cheers,

[zmbd]

mshmyob:
Yes, it is true that CVERR() will allow one to toss a trappable error; however, in this case, how would this be useful for trapping the error caused by the “missing” parent form control?

[mshmyob]

Ok I threw together a mock up and it works just fine.

My mock up consisted of 2 forms - I have an unbound text control on each form. Form 'B' unbound text control uses values from Form 'A' unbound text control.

I have also created a MODULE and created a simple function that checks for the open form and returns a user defined error number if it is not open.

If Form 'A' is not accessible (ie: not open) then my IIF statement executes the 2nd statement.

In your Control source of the text control on Form 'B' (subform) that you are having a problem with put your iif - I have simplified it to the following

=IIf(IsError(CheckForm()),1,2)

My MODULE code

Public Function CheckForm()

    If CurrentProject.AllForms("frmA").IsLoaded Then
        'do nothing
    Else
        CheckForm = CVErr(1000)
    End If
        
End Function

Hope this is of some help.

You do have to create the module but you can continue to just use your data source expression.

cheers,

[zmbd]

mshmyob:
Please read OP.

The bottom paragraph states that I already had VBA capable of trapping the error using a custom function. Indeed, I don't even need to throw an error, it checks to see which forms are currently loaded in the "FORMS" collection under "CURRENTPROJECT ", and then calls for the information from the controls based on that information.

The question is/was, how does one trap the #ERROR within the control without using VBA. I have two workstations that have the VBA disabled (because they are open to the public); thus, I need a work around... which I had already accomplished; however, had my bodged my field references (post #9).

So, once again I ask, how does what you suggested answer the question asked?

[mshmyob]

I already gave you the answer. If you don't want to use the module then just change your data source to something like so:

=IIf([CurrentProject].[AllForms]("frmValue").[IsLoaded],1,2)

Obviously you would change the form name to your main form name and change the true/false to your expressions.

cheers,