Combo Box Record Set Using Nz() in one Critieria

[kjv1611]

Howdy all,

Thanks in advance for any references, suggestions, opinions, etc.

Here's what I'm trying to do and I'm getting nowhere so far.

On a user form, I've set up a row source to only show possible options according to other items selected on the form - been there, done that, pretty common thing - also asked often in the forums.

Here's the part where it gets interesting and I do not have the answer just yet. It seems it would be simple.. Maybe it's too late in the evening.

In the instance where one of the combo boxes is null, I want to not use the critieria based on that control box. In case that makes no sense, I'll setup the example of it here:

1. Control of question = cboMyComboBox
2. Control whose recordsource is to be affected = cboAffected
3. So far I thought I'd try using Nz(cboMyComboBox,*) that didn't work
4. Then I tried Nz(cboMyComboBox,"*), and of course that didn't work - duh, b/c it's based on the string value of the asterisc character.

So, any thoughts or suggestions on this? Is there a way I can modify it so that I can just flat change the record source of the 2nd control, or either modify the record source... OR put something like the Nz condition within the critieria...

Or work in an Or clause... I suppose that is possible, was trying to avoid that one.

Thanks again,

 

[TheAceMan1]

How are ya kjv1611 . . .

[blue]In the instance where one of the combo boxes is null, [purple]I want to not use the critieria based on that control box.[/purple][/blue]

If my read is correct, it would be something like:

[blue]Forms!myForm!myControl OR Forms!myForm!myControl Is Null[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

Yeah, I think that was the same thought I was referring to on the recordsource - using an OR critieria there. I was just trying to avoid that one - probably silly to flat try to avoid, but that was my thought. :0)

I'll give that a go and post back.

 

[kjv1611]

Okay, I found the solution. It is a query with 2 criteria.. so an OR clause.

I do not say "Forms!myForm!myControl Is Null" in the criteria, but rather one of the containing OR clauses references that control, and the other does not. Seems to work fine so far.

I may double check later. And the first critieria is where I do look at that field... but in some ways, it doesn't seem like it should work.

Maybe in a couple days I'll have time to test it a couple of different ways to be sure.

 

[TheAceMan1]

kjv1611 . . .

I think you'll need both as you'll have times the combo is not null.

If ...

[blue][FieldName]=Forms!myForm!myControl OR Forms!myForm!myControl Is Null[/blue]

... were the only criteria it would be perfect. If the combo were null it would [blue]allow all records to show[/blue] or filter with its current value. Since you have other criteria just put the line in parentheses:

[blue][red][b]([/b][/red][FieldName]=Forms!myForm!myControl OR Forms!myForm!myControl Is Null[red][b])[/b][/red] AND/OR ...[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

Yeah, that does sound more like what it'll take. Thanks. Not going to mess with that one anymore this evening. Just got to check in for a minute. I'll try to look at it tomorrow. Thanks for following up. I'll get back to this asap.