Cr in Textbox

[lizray]

I have a textbox on a report and it contains a string =[Title] & " " & IIf(IsNull([FirstName]),"",[FirstName] & " ") & [FamilyName] & Chr(13) & Chr(10) etc
I want to start a new line after [Familyname] and this worked in WinXP environemnt. I have now moved to Win7 and it will not accept the chr(13) function in a textbox. I have tried replacing the chr(10) with
& vbcrlf &
but that wont work either. I am using Access 2003, Can anyone offer a suggestion ?

 

[genomon]

& vbcrlf &
[/quote}

Try removing the final & character.

Beir bua agus beannacht!

 

[dhookom]

You can't ever use vb constants in control sources. They can be used in modules only.

I would try create a new, very simple report that has a control source for a text box with Chr(13) & Chr(10) in it to see if if works in it simplest form.

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

Try using the label control instead of a text box. Build the string in VBA using vbCrLf when needed, then set the .caption property = the string variable. I just did this in Access 2000 on XP and it worked fine. Not sure about 2003 on Win 7.

Beir bua agus beannacht!

 

[lizray]

Thanks guys. I have been away for a while.I tried using VBA to create the string in the Format event, but the variables from the table used as the data source for the report are flagged as undefined. Does this mean that I have to create a textbox for each variable on the report??. or is there some other way around this ??

 

[dhookom]

There should be no reason to use vba to create this expression in a control source. It is correct that if you want to use VBA to reference a field value, it must be bound to a control on the report.

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

I created a single unbound text box containing
=Chr(13) & Chr(10)
as its data source. I get the error message "You may have used an aggregate function, such as Count, in a design grid or in a calculated control or field". This only happens with Win7. Winxp worked ok. Also tried using my own string function which would return vbCrLF...received the dame error

 

[dhookom]

Have you tried to open a module and compile the code?

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

I am still learning and I am not sure what you are asking. I have simply created the textbox and I also created the code in the Format event and suppose it compiles when I save.

 

[dhookom]

While in a code window, you can choose menu options I believe under Debug to compile your code. Also check Tools->References to see if any are missing.

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

I am using the module attached to the report and it compiled without errors...but I still have the problem. On the "format" event for the report I can construct a VB string and then make it the data for the textbox, but then I have another problem - The fields contained in the string are not recognised... presumably because they need to be specified in the report detail. Is there some way around this ?

 

[genomon]

Add the fields to the detail section & set the visible property = false.

Beir bua agus beannacht!

 

[dhookom]

I still don't understand why you would need VBA to do any of this. Can you tell us what you are attempting to do?

You can't reference a field from the report's recordsource in code without binding the field to a control.

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

Essentially I have a text box and I want it to show names/addresses for Stickers. The textbox includes some fields contained in the reports recordsource and I want to insert Cr and Lf so that the text will display as 3 lines. It works ok in winxp, but win7 wont accept the Chr(10) fn which I have included in the string to force a new line. Ihave then tried to produce the text string in VB code on the "format"event for the report. This accepts the vbCrLF as a constent to produce the new line, but now I have a new problem. The vb code in the format event will not recognise the Fields contained in the text string.

 

[dhookom]

Again, I don't think VBA is the answer. Have you tried to create a new report with a single text box with a control source like:

  ="First Line" & Chr(13) & Chr(10) & "Second Line"

If the control and section can grow, you should see
[tt][blue]
First Line
Second Line
[/blue][/tt]
If this works then build on it with additional strings/fields/etc.

As much as I like code, it is usually the last resort.

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

Thanks Dhookom, I have created a new report with a single textbox as per your suggestion, but when I try to save it, there is an error "The function you entered cant be used in this expression"followed by error information shown in my earlier posting. It is objecting to the Chr function. This only happens in a win7 environment... in winxp there is no problem

 

[dhookom]

Whate happens if you open the debug window (press Ctrl+G) and enter

  ? "First Line" & Chr(13) & Chr(10) & "Second Line"

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

Yes that works ok. I have now made it work by creating a textbox named txtNL on the ditail section of the report, making it not visible and including it in the textbox with the string as ...& txtNL & .... . I then define the txtNL data in the activate event for the form as txtNL=vbCrLf. The activate event may not be ideal and I will have to find the best event to use as I want it defined only once for the report, not every time a sticker is formatted