Change Text in Textbox for Report

[soccasnow]

Might be a simple question but could not figure out the solution. I need to update/change the output of the text for a TextBox in Access 2007 based on the selection of a combobox. Example code is below:

If [ComboBox] = "String" Then
TextBox.Value = "New String"
End If



The value from the TextBox is picked up from a query whose table is linked to a read-only table (brought over via SharePoint).

[June7]

Unusual requirement but maybe a couple of possibilities, depends what you mean by update/change. Do you need to save a value to table or just dynamically show this alias on report?

1. textbox ControlSource expression: = Forms!formname!comboboxname - form must remain open when report runs

2. pass the combobox value to report in OpenArgs argument of DoCmd.OpenReport and expression in textbox: = OpenArgs

[soccasnow]

This example sums up what I am trying to accomplish:

ComboBox Field 'Car' = ["Blue, Green, Red, TBD"]
TextBox Field 'Style' = ["Cool, Sleek, Fast"]

If ComboBox Field = "TBD"
TextBox Field = "TBD"
End If

So on the report, the TextBox field is showing with "TBD" as its value (ComboBox Field is not showed). So in reference to your question, it should dynamically show on the report.

I was able to accomplish changing the backcolor and forecolor but changing the text/value seems to be more difficult.

Thanks for your help and aid!

Unusual requirement but maybe a couple of possibilities, depends what you mean by update/change. Do you need to save a value to table or just dynamically show this alias on report?

1. textbox ControlSource expression: = Forms!formname!comboboxname - form must remain open when report runs

2. pass the combobox value to report in OpenArgs argument of DoCmd.OpenReport and expression in textbox: = OpenArgs

[June7]

Then use an IIf expression with one of the suggested options.

=IIf(Forms!formname!Car="TBD","TBD",[Style])

or

=IIf(OpenArgs="TBD","TBD",[Style])

Is the field Car actually in the report RecordSource? If so, then just:

IIf([Car]="TBD","TBD",[Style])

[soccasnow]

Hi June7,

I ended up using a 'Switch' statement in my SQL statement but unfortunately came across another caveat. Seems I could not add multiple conditions for the 'IIF' statement. See below what failed for me:

IIF([Car] = "TBD" AND IsNull([Style]), "TBD", [Style])

The second condition is basically saying if Car is equal to TBD and there isn't already a style, change the style to "TBD", otherwise keep the remaining style. Any thoughts on how to do this?

Thanks and Best,
Soccasnow

[June7]

Multiple conditions is possible. Why doesn't it work - error message, wrong results, nothing happens?

With this logic, if Car <> TBD and Style is null then will still return Style, which is null and therefore box is blank. Is that data combination possible? What all data combinations are possible?

Car is null and Style is null
Car = TBD and Style is null
Car = other text and Style is null
etc

Basically, if Style is null then should return TBD. Could simply be: Nz([Style],"TBD")

[soccasnow]

Found the fix. The error I was getting was around a 'circular reference' - not exactly sure where it was coming from.

Used multiple conditions in my Switch statement within my query that read something like this:

Switch([Car]="TBD" And IsNull([Style]),"TBD",[Style])

...which ended up doing the job.

It was more a matter of checking the 'Car' field against the 'Style' field and then populating them accordingly.

Thanks for your help and speedy reply's! I owe you a beer!

Best,
Soccasnow