Combo box starts with blank value

[natesternberg]

I have a form with a combo box whose row source is a query:

SELECT [Lname]+", "+[tblVolunteers].[Fname] AS Name, tblVolunteers.ID FROM tblVolunteers WHERE (((tblVolunteers.Lname)<>"")) ORDER BY tblVolunteers.Lname;

When you open the form, the combo starts out blank: the values are all there, you just have to click the down arrow to see any of them. The problem is, I have a text box later in the form that calculates a value based on the ID column in the combo. And when the combo's blank, that column's missing, so the text box says #Error.

So I guess I have to either a) make the combo not start out blank or b) write the text box expression to handle blanks. I haven't managed to do either, though: any suggestions?

Or am I going about this the wrong way?

 

[Kiwiman]

Howzit

You can like you say
a) specify a default value for the combo box
b) handle blanks for your other text box that is reliant on the combobox

if me.yourcombobox = "" or isnull(me.yourcombobox) then
' enter your code to handle blank
else
' your current expression
end if

 

[missinglinq]

My guess,based on your description, is that you have the calculation for your Textbox in the Control Source for the Textbox.

The Form opens, the Control Source tries to do its calculation, and lacking data, throws up the #Error you're seeing.

If this is true, simply take your calculation out of the Control Source Property and place it in the AfterUpdate event of the Combobox. That way, the calculation isn't attempted until the Combobox has had a selection made.

Linq ;0)>

 

[natesternberg]

You're right, placing the calculation in AfterUpdate worked. I didn't understand the mechanics of the form processing, which you clarified for me. Thanks very much for the assistance.

 

[missinglinq]

Using this method is also useful in situations where you need to

• Have a calculation run
• Sometimes be able to manually over-ride that calculation
• Have the Calculated Field Bound to the underlying Table

While it is seldom appropriate to store Calculated Fields in Tables, those that sometimes need to be manually over-ridden make up one of the exceptions to the rule.

Glad we could help with your problem!

Linq ;0)>

 

[vbaInet]

=IIF(Len(Combobox & "")<> 0, [COLOR=Blue]... calculation here ...[/COLOR], Null)

The above will still work in the Control Source. The After Update method is valid too.