IF statement in Control source

[stu_c]

Hi all is it possible to do an IF statement in an control source?

 

[arnelgp]

what are you trying to Test?

 

[Ranman256]

on form load:

if vUser = "b.Wayne" then
  me.controlsource = "query.qsData1"
else
  me.controlsource = "query.qsData2"
endif

 

[The_Doc_Man]

Statically, not at all easy. I don't think it is possible but it surely would be tricky. ControlSources are more like SQL which does not actually have an IF statement capability.

Dynamically, perhaps on Form_Load but theoretically possible on Form_Current. See what Ranman256 suggested.

 

[GPGeorge]

on form load:

if vUser = "b.Wayne" then
  me.controlsource = "query.qsData1"
else
  me.controlsource = "query.qsData2"
endif

Me is used in a VBA function or sub to refer to the form running the VBA function or Sub. It's not going to refer to the control. So, even if you run this as a Sub in the Form's Load event, you'll have to modify it to refer to the control by name.

Also, what are you trying to accomplish with "query.qsData2"?

 

[June7]

@The_Doc_Man, not sure what you meant. It is quite simple to change ControlSource property of control on form (not report) via VBA in runtime.

"query.qsData1" would be setting ControlSource to a field in form RecordSource - RecordSource based on query.

Can use IIf() function in ControlSource, example:

=IIf(SubDate + 365 <= Date(), "Expired", "Current")

 

[Pat Hartman]

You can use functions in a ControlSource but not other code.

=IIf(IsNull(LastName, CompanyName, FirstName & " " & LastName)

That example displays either CompanyName or person name depending on the value of lastName. This control is not updateable.

What are you trying to do? We are much better at solving real problems than at guessing what you want to do.

 

[The_Doc_Man]

@The_Doc_Man, not sure what you meant. It is quite simple to change ControlSource property of control on form (not report) via VBA in runtime.

"query.qsData1" would be setting ControlSource to a field in form RecordSource - RecordSource based on query.

Can use IIf() function in ControlSource, example:

=IIf(SubDate + 365 <= Date(), "Expired", "Current")

Yes - at RUNTIME. Not as a static row source. My comment was that you have to do what the OP wanted with dynamic code that, of necessity, has to run before the Form_Load routine because, by that time, you are starting to open the recordset and populate the bound controls. Starting - but not finished yet, because that finishes in Form_Current. My question is whether [SubDate] is visible and populated at the moment that you execute that IIF, because if SubDate is a field on the same form, then it may be already too late to have any effect.

 

[Pat Hartman]

Doc, we have no idea what the OP wants to run code in a control event for. Ranman assumed he was trying to change the RecordSource. All we know is that he did say CONTROL source, not RecordSource.

 

[The_Doc_Man]

I may have been loose with my words. I'll try this: You can probably put an IIF as a .ControlSource but the question is, if the IIF depends on another control (e.g. IIF( [Z]>0, "[Source1]", "[Source2]"), is Z defined early enough to make a difference in the source with somehow adding a .Refresh or .Requery? I'm concerned about the timing, not whether the syntax would be accepted.