Textbox based on UDF(subform value) Problem

[ShredDude]

I'm not getting this one...

I have a main form with two subforms, and various bound textboxes on the mainform.

I'm having a problem with using a User Defined VBA Function (UDF) as the control source in a text box on my main form. When I initially open the form it works as intended. Upon changing the mainform's recordsource, selecting a new client in this case, my textbox in question results in "#Error".

However if I go to the VBE's Immediate window, I can successfully return the intended value from the UDF, which requires a parameter passed to it from the Main form's Subform2 eg: fUDF(Forms![mainform].[frmSUB2].Form.[Xid])

Subform 1 is bound to the main form's "ClientID" field. When the mainform changes, subform1's data is repopulated with data associated with that new client.

Being new to this, I had trouble finding a way to make subform2 repopulate when the mainform changes, as its data is not exclusively a function of the mainform's dataset, but rather the data selected in subform1. (Cascading SubForm?) I was able to get what I wanted to appear in subform2 by including a Requery statement in subform1's Form_Current Event. eg:

Private Sub Form_Current()
Forms![mainform].[frmSUB2].Requery

End Sub

My issue with the textbox producing the #Error, seems to be a timing issue. I've placed breaks in the code of the UDF in an attempt to see when it is fired off. When I initialy load the form, the break is hit. This exaplains the successful result upon the form's inital load.

Upon any requery of the mainform however, the break isn't hit. My UDF isn't getting fired off!

The textbox's controlsoruce is basically fUDF(Forms![mainform].[frmSUB2].Form.[Xid])

I would've thought this would make my bound textbox do it's thing anytime the Xid value on frmSUB2 changed. But it's not.

I'm wondering if the way I originally structured the relationship between frmSUB1 and frmSUB2 is to blame ?? Is it trying to do all the MainForm's "Bound" things first, and then firing off the form_current event of subform1? Even then, I'd expect my textbox to successfully complete its calculation, only using the previous value from subform2. But I get#Error....

Any ideas from anyone most aprecaited.

Thanks,

Shred

PS The real frustrating thing is I had this whole scenario working when I used two unbound listboxes instead of the subforms described above. That involved much more VBA code of course. I'm still trying to learn my ways in Access and was trying to do what I'm beginning to understand is the "right" way more often than not, that is using Bound controls wherever possible. If my "understanding" of this is off, please set me straight.

 

[ShredDude]

Re: UPDATE to Textbox based on UDF(subform value) Problem

The undesired #Error result seems to only appear when the main form is requeried.

For clairification:

Mainform tied to Client Info
Subform1 displays "Projects" for that client
Subform2 displays "Contracts" for the Project selected in Subform1

Textbox is calculating an amount as a function of a few variables associated with the Contract in SubForm2, and today's date.

If I refresh the mainform to a client with multiple projects, I get the #Error, until I select a new "project" in subform1, then textbox calculates correctly based on the first record in subform2.

If the client I refresh to has only one project, clicking on the project has no effect on the #Error.

so, my UDF works, when it's given the ProjectID from subform2.

It seems that when I requery the mainform, that the textbox is not successfully passing the ProjectID from subform2 to the UDF??? But when I can change the record in subform1 (client has >1 "Project"), the textbox is successful in using the UDF to recalculate.