Applying a simple condition with VBA

[funkmonsteruk]

I have a VBA report which is being used by a variety of users on a who have little or no BO experience (don't have a lot myself) the report itself has three objects Estate, Sedol and a quantity.

I have added a user form to control some basic tasks but i want to allow the user to select a sedol (this will be a list of about 100 numbers) from either a drop down list (possibly a combo box) or enter the sedol into a text box and run the report with there selected sedol being a condition. Any suggestions.......

 

[promero]

You can assign values from VBA to the parameters of a query. I will briefly explain you the canonical way, although there are other ways to do the job.

The SQL code must include the @Script function where you want a parameter. For example, the code
[tt]
TABLE.SELDOL = @Script('Seldol number', 'N', 'SetSeldolParameter')
[/tt]
can be placed in the WHERE clause of the SQL. For free-hand SQL data providers, it's immediate. For universe data providers, the code can, for example, be included in a filter called "Seldol selected by VBA".

The code means that the 'SetSeldolParameter' VBA macro must be executed. Then, the entire @Select expression will be replaced by the value (or values) of the
'Seldol number' numeric ('N') variable.

The macro SetSeldolParameter, to be included in the "This Document" module, must be something like this:
[tt]
Public SetSeldolParameter ()
Dim SeldolNumber as Integer 'or as whatever is correct
'Code to obtain SeldolNumber
Variables('Seldol number').Value = SeldolNumber
'Multiple values (for an IN) can be assigned too:
'Variables('Seldol number').Value = Val1 + ";" + Val2
End Sub
[/tt]
Have a look at the manuals for details.
And ask any question here too! ;-)

 

[promero]

If the seldom number will be selected by the user, the normal way to do this is using "user prompts".

This is done including the @Variable or @Prompt (or both)functions in the SQL. They are similar to @Script, but will ask the user directly, instead of executing a VBA macro. @Prompt gives you more control, allowing to show a custom or from-query list of values to choose from.

In the query panel you can include an @Variable expression by making a condition where the right part is a "user response".

You say you have a form. Note that using Microsoft Forms in VBA is not feasible when the document will be executed in Web Intelligence (the web-oriented business objects), but @Variable and @Prompt must be used instead.

 

[promero]

Finally, you can modify arbitrary parts of the SQL code with @Select. This is an undocumented feature, so it may not work with future versions of BO.

@Script('VarName', 'N', 'ProcName') will be replaced with the value of VarName after ProcName is executed. The 'N' parameter says that the variable is numeric, so no quotatin marks are placed. BO does not check that VarName has a numeric format, so SQL code can be assigned to VarName and will be put in the SQL in place of @Script.

Another way to modifiy the SQL is assigning it to
[tt]Document.DataProviders(Name or index).SQL[/tt]