The IIF statement, a combo box and Null values 2

[Timinator]

I created a combo box on a form that allows the user to choose to see the records that contain a value or the records that don't contain a value for a specificed field. The form is linked to a query. I used in IIF statement similar to this in the critera area for the field: IIF(Forms![Form]![ComboBox] = 'Contains Value', Not Null, Null). I am not sure if i am even close on this, but I can't seem to get any results. Any info would be great. Thanks. Tim

 

[seaport]

I am not very clear about what you need. It seems that you are trying to change the recordsource of a combox on the runtime. It surely works. However, you may need to requery the combox everytime the combox get focused.

For example:

Private Sub cboCustomer_GotFocus()
cboCustomer.Requery
End Sub

Seaport

 

[ghubbell]

Hi Tim,

Try this in the criteria of your query:

Like (IIF(IsNull(Forms![Form]![ComboBox]),"*",Forms![Form]![ComboBox]))

Should fly! ;-) Gord
ghubbell@total.net

 

[Timinator]

Actually I'm trying to requery the entire form. So for example i have 4 records:
Product Num Product Name Comments
1 Hammer Tool
2 Wrench
3 Computer
4 Rake Claw rake

I want the combo box to query for a null value in the Comments field and only show the records that have comments. The combo box has 2 choices:
*Contains Value
*Contains No Value
I hope that makes more sense. Tim

 

[seaport]

Why not just set the filter first then requery the form.

For example

if combox="Contains Value" then

me.filter="isnull([comments]"
me.filteron=true
me.requery

Else

me.filter="not isnull([comments]"
me.filteron=true
me.requery

end if


Seaport

 

[ghubbell]

Hi Tim, Bang on Seaport! ;-)
You'd do like this in the "After Update" event (in visual basic) of your combobox. You'll also remove the criteria in your query. Gord
ghubbell@total.net

 

[Timinator]

Almost there,

Thats 90% of what i'm looking for guys. Thanks for all of you input so far. The only problem is I have 5 other combo boxes that are linking to the query that change depending on whats in the query, and I can't seem to get the filter (that seaport suggested) to requery the combo boxes.

Thanks.

Tim

 

[Timinator]

.....oh and i forgot to mention i tried: Combobox.Requery...and...Form.Requery


Thanks.

Tim

 

[ghubbell]

Hi Tim,
If You want to requery the form:

Me.Requery should work.

If you want to requery another combo:

Me.[NameOfTheOtherCombo].Requery

Should do it! Gord
ghubbell@total.net