Subform triggers error: "This expression is typed incorrectly..."

[DeRochier]

I'm experiencing the error, "This expression is typed incorrectly, or it is too complex to be evaluated." after I add a subform.

I have a text box on the main form that is pulling a category ID from one subform: Control Source =[fsubBudgetDetails01].[Form]![cmbCatID]

The text box, txtCatID, displays the correct value dynamically as I click on line items on the subform fsubBudgetDetails01. But when I add a second subform that I want to display child fields based on txtCatID it returns the error "This expression is typed incorrectly... " The form renders but the second subform is blank.

Second subform name: fsubMonthlyActuals01
Link Master Fields: txtCatID
Link Child Fields: CatID

The field CatID is in the fsubMonthlyActuals01 data set, which is a aggregate query (which shouldn't cause this issue).

I've been building Access forms for over 20 years and this error has me baffled.

 

[dhookom]

I always use code in the On Current of the subform to set the value of an unbound control in the main form.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[DeRochier]

Hi, Duane,

I set the event procedure for On Current to:

'Set the value of cmbBudgetID unbound combo box

Me.cmbBudgetID.Value = 1

That works but whenever I open the form I still get the "This expression is typed incorrectly..." error.

Even more odd, this form had been working correctly for several years. I started tinkering with the design and that's when the error started popping up. My back-ups of the old database still work correctly. I thought perhaps the current database had become corrupt so I started recreating it from a fresh Access instance, re-building the forms from scratch. I was surprised that the error showed up in the fresh instance.

This error doesn't make sense to me. Since the same forms in the backup versions work, I may try to resurrect one of the backup versions.

 

[DeRochier]

I figured out the problem, though I don't understand it.

I had changed the main control of the form to an unbound combo box: cmbBudgetID. The child form fsubBudgetDetails01 used the cmbBudgetID as the Master Field and it worked fine. However, when I referenced the Category ID from the child form (Control Source =[fsubBudgetDetails01].[Form]![cmbCatID]) and tried to use it as a Master Field for a second subform, fsubMonthlyActuals01, it produced the error.

When I create a subform for the Budget ID and then create a reference to the Budget ID on the main form (Source =[fsubBudgets].[Form]![txtBudgetID]) BOTH subforms work as intended without error. Clicking on a named budge shows the specific budge amounts in the Budget Details subform, and clicking on a line item in that table then shows the history of expenses specific to that category.

Problem solved and I learned something in the process.