Please see my comments interspersed with your questions below.
On Sat, 31 Jul 2004 18:14:30 +0100, Colin McGuire wrote:
[color=blue]
> Thanks for your quick help. Here are more questions because I still can't
> get this simple thing going.
>[color=green]
>> 2) You're getting your error because you're using the wrong event.
>> Why are you attempting this in the Page event?[/color]
>
> What other event should it be/should I use? (I am not trying to be being
> cute, I just don't know) Every row that is printed I want to determine Yes,
> No, or Maybe from a number. This subroutine is called (see my "test" below)
> every time a new row in the table is used for the report.[/color]
I specifically answered this in my reply, quoted below ....
* 3) Where have you placed the unbound control [Text51]?
* In the Detail Section?
* **** Code the Detail Format event: ****
* [Text51] =Choose([Decision],"Yes","No","Maybe")
If the control is placed in the Detail section, you place the code in
the Detail Format event.
[color=blue]
> The reason I want to do it in VBA rather than using things like this
> ("[Text51] =Choose([Decision],"Yes","No","Maybe")") is that I know VBA and
> although it doesn't look too cryptic,[/color]
I don't understand this above comment.
The statement:
[Text51] =Choose([Decision],"Yes","No","Maybe")
placed in an event code IS VBA, which you say you know.
[color=blue]
> I still want to avoid the hassle of
> finding out how to use the syntax/query builder etc[/color]
What hassle? Why the query builder?
If you wish to get an unbound control to display a value, you write
the expression directly into it's control source.
I gave you previously 2 expressions, either one of which will work.
You write it, exactly as I gave it to you, directly on the control's
control source property line, either:
=Choose([Decision],"Yes","No","Maybe")
or
=IIf([Decision]=1,"Yes",IIf([Decision]=2,"No","Maybe"))
[color=blue][color=green]
>> 3) Where have you placed the unbound control [Text51]?
>> In the Detail Section?[/color]
> Yes.
>
> When I type in the following (you suggested using the Value property),
> "anything" does not appear on the report but a message box is displayed with
> "test" in it for every row in the table meaning the it is called correctly.[/color]
No I didn't suggest using the value property.
I suggested you write:
=Choose([Decision],"Yes","No","Maybe")
as the controls Source property of the unbound control.
The Choose function then returns a value to the control, either "Yes",
"No", or "Maybe", depending upon the value in the [Decision] field.
[color=blue]
> Private Sub Report_Page()
> MsgBox "test"
> Text51.Value = "anything"[/color]
You're problem, using the Page event, is that you can't change a
control's value in the Page event. It's too late. You have to use the
Detail Format (or Print) event. I use the Format event.[color=blue]
>
> So no further ahead.
>
> When you sum it all up, I want to set what appears in the textbox on a
> report using VBA at runtime. The Yes, No, Maybe bit is a bit of a red
> herring.[/color]
I'll give it to you once more.
If you wish an unbound control, placed in the report's detail section,
to print specific words in place of numbers, either one of these two
things will work.
1) As the CONTROLSOURCE of an unbound control placed in the Report's
Detail section, write:
=Choose([Decision],"Yes","No","Maybe")
or, using code ...
2) Use code, placed in the DETAIL FORMAT event:
[Text51] = Choose([Decision],"Yes","No","Maybe")
[color=blue]
>
> Thank you again for this and future help.
> Colin
>
> "fredg" <fgutkind@example.invalid> wrote in message
> news:ucid9f1e1gpc.1cv7420nfgudy.dlg@40tude.net...[color=green]
>> On 31 Jul 2004 00:03:31 -0700, Colin McGuire wrote:
>>[color=darkred]
>>> Thanks for the ways of doing this.
>>>
>>> I want to do it like this though.
>>>
>>> I want to put an unbound control like a textbox on the report and then
>>> set the "Yes", "No", "Maybe" using VBA. Can this be done?
>>>
>>> Here is what I have that isn't working rather than the whole thing.
>>>
>>> 1. I put a new textbox on the report (called Text51)
>>> 2. I have the following in the "Report_Page" event.
>>>
>>> Private Sub Report_Page()
>>> Text51.Text="something" 'will be one of Yes,No,Maybe with some VBA
>>> End Sub 'once I get this little bit working.
>>>
>>> Text51 doesn't seem to have a Text or Value property and VBA is giving
>>> me an error.
>>>
>>>
ducojansen@aol.com (Ed Marzan) wrote in message[/color][/color]
> news:<cc123933.0407301108.3a38cbed@posting.google. com>...[color=green][color=darkred]
>>>> Someone out there is going to give you a nested IIF statement that is
>>>> going to work really nicely. In case they don't try this.
>>>>
>>>> Create a function in a module like this:
>>>>
>>>> Public Function YNM(i As Integer) As String
>>>> 'YesNoMaybe
>>>> Dim strResponse As String
>>>>
>>>> Select Case i
>>>> Case 1
>>>> strResponse = "Yes"
>>>>
>>>> Case 2
>>>> strResponse = "No"
>>>>
>>>> Case 3
>>>> strResponse = "Maybe"
>>>>
>>>> End Select
>>>>
>>>> YNM = strResponse
>>>>
>>>> End Function
>>>>
>>>> Then create a query with a calculated field
>>>>
>>>> DecisionText: YNM([Decision])
>>>>
>>>> The SQL for this would be
>>>>
>>>> SELECT tblMyTable.Decision, YNM([Decision]) AS DecisionText
>>>> FROM tblMyTable;
>>>>
>>>> That ought to do it! Any questions post again.
>>>>
>>>> If you don't like my solution try looking up Nested IIF's in a query
>>>> on Google.
>>>>
>>>>
colinandkaren@lycos.co.uk (Colin McGuire) wrote in message[/color][/color]
> news:<ab6cea37.0407300608.2e84a94d@posting.google. com>...[color=green][color=darkred]
>>>>> Hi, I have an report in Microsoft Access and it displays everything in
>>>>> the table. One column called "DECISION" in the table has either 1,2,
>>>>> or 3 in it. On my report it displays 1, 2, or 3. I want to appear in
>>>>> the report is Yes, No, or Maybe. What do I need to do to change what
>>>>> appears in the report/what term do I need to search out in Google?
>>>>> Thank you
>>>>> Colin[/color]
>>
>> 1) If it is a text control (unbound or otherwise) it does have a Value
>> property.
>> It's what ever value is returned by the control's control source, i,e,
>> = "This is it's value.", or a value in a bound field.
>>
>> 2) You're getting your error because you're using the wrong event.
>> Why are you attempting this in the Page event?
>>
>> 3) Where have you placed the unbound control [Text51]?
>> In the Detail Section?
>> Code the Detail Format event:
>> [Text51] =Choose([Decision],"Yes","No","Maybe")
>>
>> I don't see any advantage to using code to write the value as opposed
>> to simply placing the expression directly into the control's control
>> source, as long as the [Decision] field is a field in the report's
>> record source.
>> --
>> Fred
>> Please only reply to this newsgroup.
>> I do not reply to personal email.[/color][/color]
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
