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")

 

[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.

 

[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.