Entering formula in text box

[GregHolden]

I am attempting to enter a formula in a text box. The formula is a fairly long If...Then..Else statement (the formula is too long to use IIf.)

I have entered (what I believe to be) a valid If..Then..Else statement by adding the text box and then building the event in the code builder.



The problem I am having is that the text box continues to be Unbound. On the property sheet the [Event Procedure] is present in the "On Click" option. When I click on the the dot box, the Private Subroutine window shows the code.

When I attempt to generate the report, the text box remains empty.

I apologize if this is a dumb question - I am fairly new to Access - but how can I get Access to recognize and run the If Then Else statement each time the report is generated?

Thank you for any assistance you can provide.

Greg

[June7]

By 'code builder' do you mean VBA editor? The code would have to be a function procedure. The function could be behind the report or a general module. Then call the function from the textbox ControlSource. Post the code for analysis.

[GregHolden]

Thank you for looking at this June.
This subroutine is looking at the values in a query to determine what type of a report was generated. I believe the If Then Else statement works fine (I have a similar statement in another place in the database, with no issues on execution) - but I just can't figure out how to have the text box check this statement when the report is generated.

Greg

Private Sub Report_type_formula__Click()
If [Monthly Arrest Query]![TA Felony Report] = 1 Or [Monthly Arrest Query]![Non-TA Felony Report] = 1 Then
[Report type] = "Felony"
Else
If [Monthly Arrest Query]![TA Misdemeanor Report] = 1 Or [Monthly Arrest Query]![Non-TA Misdemeanor Report] = 1 Or [Monthly Arrest Query]![TA Domestic A&B Report] = 1 Or [Monthly Arrest Query]![Non-TA Domestic A&B Report] = 1 Then
[Report type] = "Misdemeanor"
Else
If [Monthly Arrest Query]![TA FR-300] = 1 Or [Monthly Arrest Query]![Non-TA FR-300] = 1 Then
[Report type] = "FR-300"
Else
[Report type] = "Supplement"
End If


End Sub

[June7]

Cannot pull data from a table or query object like that. All those [Monthly Arrest Query] fields would have to be in the form or report RecordSource and then the table/query prefix is not needed unless the RecordSource has fields from different sources with identical name.

Where is the button this click event is associated with - on report or form? What is [Report Type] - a field or just textbox name - and on form or report?

For textbox on report cannot use code to reference the textbox and set its value. That is okay on a form but not report. A textbox on report can call a function and value returned by function will be the value in textbox. As an example, intrinsic Date() function will display the current date in textbox. So a user defined function can be called by textbox:
= ReportType()

Then example UDF (assuming all fields are in the report RecordSource):

Function ReportType()
If [TA Felony Report] = 1 Or [Non-TA Felony Report] = 1 Then
ReportType = "Felony"
ElseIf [TA Misdemeanor Report] = 1 Or [Non-TA Misdemeanor Report] = 1 Or [TA Domestic A&B Report] = 1 Or [Non-TA Domestic A&B Report] = 1 Then
ReportType = "Misdemeanor"
ElseIf [TA FR-300] = 1 Or [Non-TA FR-300] = 1 Then
ReportType = "FR-300"
Else
ReportType = "Supplement"
End If
End Function

Where is the similar code you say is successful and what does it do?

BTW, recommend no spaces or special characters/punctuation (underscore is exception) in names.