Dependant Combo box and Null....

[andyparksy]

Hi guys.

first post (please be gentle ) but have a little problem with some combo boxes that I can't find an answer for.

I have set up some dependant combo boxes, Combo1(Areas) and Combo2(a list of properties), so that Combo 2 only shows the desired fields based on entry in Combo1, and working fine.

I needed to be able to change Combo1 so set up an after update procedure to clear Combo2 to Null and to do a requery.

All was fine until I have tried to enter another text box ("info1") that has a link to Combo2. I have another table that shows some different information about each property and wanted to show this on the form but whenever I try to include it(or even just include the required field within the query that the form is based upon) and change Combo1 I get a Null error ("tried to assign the Null value to a variable that is not a variant data type"). I am guessing it is struggling to return something for Info1 because the procedure has set Combo2 to null

Any ideas would be very very welcome as really hit a dead end as far as my very limited knowledge goes!

Cheers

 

[jzwp22]

Welcome to AWF!

If the additional information about a property is in a different table than that on which the main form is based, it sounds like a subform based on that different table (or an appropriate query based on that table) would be a better way to display the data. You would use the after update event of the 2nd combo box to update the subform's record source specific to the propery that was selected.

We'll probably need some additional information on your table structure to know for sure if this is the right approach.

 

[andyparksy]

Thanks

Didn't want to go down the route of a subform as this is just the finishing touches but still not sure why I was getting my error. By changing different parts I tracked it down to the after update procedure I had running on the first combo box that set the second box to null.

Seems odd, as I had thought when a new form was opened the box would be null then, and was not getting the error, only when I set the box to null through the procedure.

Not the best fix but tried changing the procedure to set the second box to "" instead of null (was really only a presentational adjustment I was looking for) which now does not throw up the error and will prob be sufficient as I don't think it is going to interfere with any of the filters I have set up

Thanks for the input though - hate to "cheat" my way to a solution but not sure why it would treat setting a value to null differently than simply opening a blank form.

 

[jzwp22]

Glad you got it worked out; good luck with your project.

 

[speedyjhg]

I'm still new to VBA but I don't think "Null" is what you wanted.

My understanding is that when forms are opened values are nothing or empty.

Here's a reference if you're still looking for a different solution.
http://allenbrowne.com/vba-NothingEmpty.html

 

[boblarson]

I'm still new to VBA but I don't think "Null" is what you wanted.

My understanding is that when forms are opened values are nothing or empty.

Here's a reference if you're still looking for a different solution.
http://allenbrowne.com/vba-NothingEmpty.html

Values are null, when a form is opened, unless they are bound to a field which then it may have a value or it could be null or a zero-length string depending on what is stored there.