Can I use a WHERE conditional inside an IF-THEN? (1 Viewer)

[Ingeneeus]

Hello, all.

I think I already know the answer, but I really want someone to tell me I'm wrong

Here is my issue. I've placed a command button on an insanely complicated form named View Orders. The command button, which we'll call CmdClearFlag for our purposes, clears (hides) a warning flag which pops up on said form when certain conditions are met.

Because this form is already really busy, I'd like that button to be invisible unless the warning flag is flying.

I've figured out that I need to put the code in the form's On Current event, and I know to show or hide the button. Here's the complicated part:

The condition that throws the warning flag is whether a field in a given order in the table Orders is set to TRUE or FALSE (True = Flag). The primary key for the Orders table is OrderNumber, and let's call the flag-throwing column Warning.

I think what I want to do is compare the Order Number displayed in the View Orders textbox TxtOrder to the OrderNumber column in the Orders table, and then check to see whether the Warning field in that same row has a -1 in it.

This is what I've sketched out on the back of my napkin:

IF [tables].[orders].[warning] = "-1" WHERE [tables].[orders].[OrderNumber] = [forms].[View Orders].[TxtOrder] THEN
me.CmdClearFlag.visible = True 
Else
me.CmdClearFlag.visible =  False

Of course, the whole thing depends on whether or not I can use the WHERE in an IF-THEN environment. Since there is already a small novella's worth of code in the form's On Current event, I don't want to risk putting something new in there without consultation.

As always, your advice will be appreciated.

 

[Galaxiom]

You can't use a Where condition in VBA.

Also you appear to be expecting to refer to tables the same way that you can refer to forms and reports collections. You can't.

Why not include the Orders Warning field in the form's RecordSource where you can refer to it on the current record in the form?

Otherwise maybe you want a DLookup.

 

[Ingeneeus]

Hi, Galaxiom --
Thanks for your reply; I had a feeling my idea wasn't going to work, but I thought I'd throw it out there.

It occurs to me now that I've been over-complicating this. All I need to do in the On Current event is determine whether the warning flag is visible in the form, and then use that for my IF-THEN to reveal my command button.

IF [Forms].[View Orders].[lblFlag].visible = True Then
 me.CmdClearFlag.visible = True 
  Else
  me.CmdClearFlag.visible =  False

I think I was a little buggy from too much time staring at code yesterday.

 

[MarkK]

If you want the visible state of the command button to be the same as the visible state of the label, you can just assign that value directly without an IF block. Consider the effect of this code . . .

me.CmdClearFlag.visible = Forms.[View Orders].lblFlag.visible

 

[Ingeneeus]

If you want the visible state of the command button to be the same as the visible state of the label, you can just assign that value directly without an IF block. Consider the effect of this code . . .

me.CmdClearFlag.visible = Forms.[View Orders].lblFlag.visible

Thanks, MarkK --
I did not know that (obviously ). That's just much cleaner. Thanks for the tip!

~Gene

 

[Ingeneeus]

OK, update. I'm afraid I couldn't get MarkK's code to work for me. I got an "object not supported" (or something like that) error when I placed the

me.CmdClearFlag.visible = Forms.[View Orders].lblFlag.visible

line in the Form's On Current event. I will guess that I missed something.

I had to go back to the IF-THEN statement. Here's what ultimately worked :

    If Me.lblflag.visible = True Then
    Me.CmdClearDupSkuChk.visible = True
    Else
    Me.CmdClearFlag.visible = False
    End If

Thanks again for all the input!

~Gene