Populating a textbox if another contains a word on report load

[epardo87]

Hello there,

I have a dynamic report that works as a sale checklist, so I have the order items listed correctly, the thing is that i have a textbox that for certain items it has to show another sub item the goes with the purchase listed, e.g: A salad will show "Dressing" below to have the employee make sure that the dressing is included. this will gain complexity because it will show a different name of dressing per type of salad, etc. but the thing to solve is that I havent been able to make it work through VBA, I had success on the expression builder, but, due to the complexity of all the different types of dressings, I rather write it on VBA.
This is what I had on the expression builder that worked fine so far, this is within the control source of the "DressingTxt" textbox :

=IIf([Product] Like "Salad*" Or [Product] Like "Wrap*" Or [Product] Like "Panini*","Dressing  [   ]",Null)

In my attempts with VBA, first of all i dont know if I should write it within sub report_Current() or sub report_load(), whats the difference? anyway, this is what I have, I have placed it on both subs without success...

Private Sub Report_Current()
    If InStr(Product.Text, "Salad") Then
        Me.DressingTxt.Text = "Dressing"
    End If
End Sub

Thanks in advance for your time and suggestions

[Micron]

Load event is best for dealing with field/control values. Open is better for things like setting properties on the fly. Using it to set values can raise errors because the form/report is not fully opened until the end of the event. Load event runs after Open event.

You might want to consider setting the report control property to a function that returns the wanted value if you have many of these variations. I figure a Select Case block would be the way to go.

EDIT - can't tell if your sample refers to the main report or sub. Your issue may just be one of improper syntax when referring to the subreport. It depends on whether or not the code is on the sub or the main.

[epardo87]

Load event is best for dealing with field/control values. Open is better for things like setting properties on the fly. Using it to set values can raise errors because the form/report is not fully opened until the end of the event. Load event runs after Open event.

You might want to consider setting the report control property to a function that returns the wanted value if you have many of these variations. I figure a Select Case block would be the way to go.

EDIT - can't tell if your sample refers to the main report or sub. Your issue may just be one of improper syntax when referring to the subreport. It depends on whether or not the code is on the sub or the main.

Thanks for your answer, this is a main report that pulls data from a query where the SO is filtered to show the list of items

SELECT SalesDetail.Product, SalesDetail.Qty, SalesDetail.Notes, Sales.Date, Sales.SO, Sales.SaleType, FROM Sales INNER JOIN SalesDetail ON Sales.SO = SalesDetail.SO WHERE (((Sales.SO)=[Forms]![POS]![SO]));

[June7]

Hard coding in VBA the associated values can get frustrating if you are continually adding items or need to change associated value which means modifying and/or expanding code. Would be best done with a related table.

I don't use parameterized queries. I prefer VBA to construct filter criteria and apply to form or report.

[epardo87]

Load event is best for dealing with field/control values. Open is better for things like setting properties on the fly. Using it to set values can raise errors because the form/report is not fully opened until the end of the event. Load event runs after Open event.

You might want to consider setting the report control property to a function that returns the wanted value if you have many of these variations. I figure a Select Case block would be the way to go.

EDIT - can't tell if your sample refers to the main report or sub. Your issue may just be one of improper syntax when referring to the subreport. It depends on whether or not the code is on the sub or the main.

At the end I managed to create a sub function with the select case in it and worked just fine, thanks!

[Micron]

You're welcome. Glad I could help, even if only a little bit.