changing displayed data on the fly in the detail section

[Howard]

I have a report with many columns based on a large crosstab that returns
in its cells either single letters or the strings "Dist", Merit" or
"Pass".
The textboxes in the detail are called text1, text2 etc and I
dynamically position, hide and format them in the Onformat event.

As the columns are small I want to change "Dist" to "D", "Merit" to "M"
and "Pass" to "P" befor I print it.

I've tried putting the code below in the OnPrint event of the detail
section but it won't work. Without setfocus I get an error telling me to
setfocus. With it I get an error saying setfocus is not allowed here.

Any ideas?


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
For x = 1 To nColumns
If Me("text" & Format$(x)) = "Dist" Then
Me("text" & Format$(x)).SetFocus
Me("text" & Format$(x)).Text = "D"
End If
Next x
End Sub


Howard

 

[John Spencer]

If the controls are bound, then you cannot change the value in field.

I would change the underlying query to only return a single letter in
all cases.

Left([Your Table Name].[Your Field Name],1)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Duane Hookom]

In addition to John's advice: You can't set the focus to anything in a
report. Also use of the "Text" property is rarely rarely rarely used in
Access. You generally use the "Value" property which is the default property
of text boxes. You can set the Value property of unbound controls.


--
Duane Hookom
Microsoft Access MVP

If the controls are bound, then you cannot change the value in field.

I would change the underlying query to only return a single letter in
all cases.

Left([Your Table Name].[Your Field Name],1)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a report with many columns based on a large crosstab that returns
in its cells either single letters or the strings "Dist", Merit" or
"Pass".
The textboxes in the detail are called text1, text2 etc and I
dynamically position, hide and format them in the Onformat event.

As the columns are small I want to change "Dist" to "D", "Merit" to "M"
and "Pass" to "P" befor I print it.

I've tried putting the code below in the OnPrint event of the detail
section but it won't work. Without setfocus I get an error telling me to
setfocus. With it I get an error saying setfocus is not allowed here.

Any ideas?


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
For x = 1 To nColumns
If Me("text" & Format$(x)) = "Dist" Then
Me("text" & Format$(x)).SetFocus
Me("text" & Format$(x)).Text = "D"
End If
Next x
End Sub


Howard

 

[Howard]

In addition to John's advice: You can't set the focus to anything in a
report. Also use of the "Text" property is rarely rarely rarely used in
Access. You generally use the "Value" property which is the default property
of text boxes. You can set the Value property of unbound controls.

Thanks to both of you but I cannot change the underlying query as that
is used for several other things as well.

No, the text boxes are not bound boxes, that's done dynamically in the
reportOpen event. Part of the code is below.

(maybe I'll have to run another query first, just to strip the long fields)


Dim dbs As DAO.Database
Set dbs = CurrentDb

Set rs = dbs.OpenRecordset(RecordSource)

nColumns = rs.Fields.Count ' Count query's fields.
nColumns = nColumns - 3 ' First 3 fields are student information

For x = 1 To nColumns ' for number of data columns.
Me("text" & Format$(x)).Width = DataWidth ' set text box width
Me("text" & Format$(x)).Height = DataHeight ' set text box height

'set horizontal position of each data box
Me("text" & Format$(x)).Left = ((x - 1) * DataWidth) +
LeftHandDataEdge

'set control source to the next data field
'- missing out 3 student related stuff
Me("text" & Format$(x)).ControlSource = rs(x + 2).Name

Next x

For x = nColumns + 1 To MaxDataBoxes 'hide remaining boxes
Me("text" & Format$(x)).Visible = False
Next x

 

[Duane Hookom]

I don't care much for that crosstab report solution. I think the Crosstab
demo at http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4 is
much easier, flexible, and efficient.

However you could try something like:
Me("text" & Format$(x)).ControlSource = "=Left([" & rs(x + 2).Name & "],1)"

 

[Howard]

I don't care much for that crosstab report solution. I think the Crosstab
demo at http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4 is
much easier, flexible, and efficient.

However you could try something like:
Me("text" & Format$(x)).ControlSource = "=Left([" & rs(x + 2).Name & "],1)"

Wow, that was quick, especially as I'm in the UK with a different time zone.

Brilliant as usual Duane. Thanks so much. I'll study the queries in that db.
regards

Howard

 

[Duane Hookom]

I think there is an explaination table in the demo some place that explains
the solution since it is sometimes difficult to get your head around it.
--
Duane Hookom
Microsoft Access MVP

I don't care much for that crosstab report solution. I think the Crosstab
demo at http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4 is
much easier, flexible, and efficient.

However you could try something like:
Me("text" & Format$(x)).ControlSource = "=Left([" & rs(x + 2).Name & "],1)"

Wow, that was quick, especially as I'm in the UK with a different time zone.

Brilliant as usual Duane. Thanks so much. I'll study the queries in that db.
regards

Howard