Receiving #Type! error in Form

[Pianopizza5]

Hello,



I have a continuous form and one of the text boxes uses a formula as the Control Source. When the Allow Additions property of the form to "No," the error 2185 (can't reference a property...) is triggered anytime someone tries to search for something that is not in the record source. (This is because the unbound control loses its focus even though it has the active control.)

To get around this, I set the Allow Additions to "Yes." The problem now is that in the "new record," the "#Type!" error appears in the field that has the formula. I tried setting a default value and using Nz so there wouldn't be any issues with null values.

Any thoughts?


SearchDefValueErr.accdb.zip

[Welshgasman]

I think I have already answered this in another thread?, so this is a crossposted and I am going to ignore this.

[moke123]

Put in error handling to trap error 2185

[Pianopizza5]

I am so sorry! I have been searching high and low for the ideas, must’ve missed it. Do you know where the thread is?

[Welshgasman]

Search on your name.

[June7]

Where did you try Nz()?

Your procedure requires String inputs but you are passing Nulls.

All arguments of IIf() must be able to calculate or returns error (except in SQL, where if the first argument is met the second is not evaluated).

This works:

=IIf(IsNull([txtSearch]),[txtFoodItem],HighTest(Nz([txtFoodItem],""),Nz([txtSearch],"")))



View all your threads at your profile.

[Pianopizza5]

Good point. I tried that in the Form - Event - On Error earlier, but this didn’t work. Do I have it in the wrong place?

Obviously, having Allow Additions set to “No” is optimal, but I’m ok with having a blank row at the bottom.

[June7]

Did you see post #6?

[Pianopizza5]

That’s beautiful. I tried this earlier, but obviously had the formula set up incorrectly. Ah, I only did the Nz on the txtFoodItem, which I see now is wrong. Everything works correctly. Thank you all very much.