Formatting a report field based on a table field...

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I have a table that contains multiple fields that are in pairs. One field
containing a piece of data to be printed on a report and another field that
contains a single character to controld the field formatting.

Example-

top msg 1 = "Joe Brown" / top msg 1 atr = "B" ' this means that I need
the field on the report to print bold
top msg 2 = "Mary Jane" / top msg 2 atr = "U" ' this means that I need
the field on the report to print underline
top msg 3 = "Joe Brown" / top msg 3 atr = "N" ' this means that I need
the field on the report to print normal
top msg 4 = "Mary Jane" / top msg 4 atr = "I" ' this means that I need
the field on the report to print italic

What I would like to do is set up a loop (I will have a known number of
fields to loop through) and upon formatting each page of the report I want
to run the code that changes the formatting of the report field. The fields
are all named the same except for a numeric value in the field name. I would
rather reference a field on the recordset than on the report itself. Here is
what I have done so far:

Select Case Me.[top msg 1 atr].Value
Case N
Me.top_msg_1.FontBold = False
Me.top_msg_1.FontItalic = False
Me.top_msg_1.FontUnderline = False
Case B
Me.top_msg_1.FontBold = True
Case I
Me.top_msg_1.FontItalic = True
Case U
Me.top_msg_1.FontUnderline = True
End Select

This code works for each field of the report, but requires me to place the
attribute field on the report as well. I just made the attribute field
hidden so that it does not print on the report. With this type of coding, I
will have to copy the select case for every field that I am formatting. I
only have about 60 fields on the report to do this way, but I know there
MUST be a way to do it in a loop.

If anyone can help, I would much appreciate it!!!
 
If your fields are named as indicated you can use a different reference.

In the OnPrint of the Details section (or whichever section contains the controls)


Dim intCount as Integer

For IntCount = 1 to 4 'or whatever is the maximum value of the field
Me("Top Msg " & intcount).fontBold = False
Me("Top Msg " & intcount).FontItalic=False
...
Select Case Me("Top Msg " & intCount & " atr")
Case "N"
'Do nothing
Case "B"
Me("Top Msg " & intcount).FontBold= True
Case "I"
...
End Select
Next intCount
 
Thanks!

OK, that works IF both the field to be formatted and the attribute field are
on the report. Is there a way to reference the current record to get the
attribute values so that I don't need to include the attribute fields on the
report. FYI: each page of the report is one record from a table, but the
sort order of the report does not match the order of the records in the
table.


John Spencer (MVP) said:
If your fields are named as indicated you can use a different reference.

In the OnPrint of the Details section (or whichever section contains the controls)


Dim intCount as Integer

For IntCount = 1 to 4 'or whatever is the maximum value of the field
Me("Top Msg " & intcount).fontBold = False
Me("Top Msg " & intcount).FontItalic=False
...
Select Case Me("Top Msg " & intCount & " atr")
Case "N"
'Do nothing
Case "B"
Me("Top Msg " & intcount).FontBold= True
Case "I"
...
End Select
Next intCount
I have a table that contains multiple fields that are in pairs. One field
containing a piece of data to be printed on a report and another field that
contains a single character to controld the field formatting.

Example-

top msg 1 = "Joe Brown" / top msg 1 atr = "B" ' this means that I need
the field on the report to print bold
top msg 2 = "Mary Jane" / top msg 2 atr = "U" ' this means that I need
the field on the report to print underline
top msg 3 = "Joe Brown" / top msg 3 atr = "N" ' this means that I need
the field on the report to print normal
top msg 4 = "Mary Jane" / top msg 4 atr = "I" ' this means that I need
the field on the report to print italic

What I would like to do is set up a loop (I will have a known number of
fields to loop through) and upon formatting each page of the report I want
to run the code that changes the formatting of the report field. The fields
are all named the same except for a numeric value in the field name. I would
rather reference a field on the recordset than on the report itself. Here is
what I have done so far:

Select Case Me.[top msg 1 atr].Value
Case N
Me.top_msg_1.FontBold = False
Me.top_msg_1.FontItalic = False
Me.top_msg_1.FontUnderline = False
Case B
Me.top_msg_1.FontBold = True
Case I
Me.top_msg_1.FontItalic = True
Case U
Me.top_msg_1.FontUnderline = True
End Select

This code works for each field of the report, but requires me to place the
attribute field on the report as well. I just made the attribute field
hidden so that it does not print on the report. With this type of coding, I
will have to copy the select case for every field that I am formatting. I
only have about 60 fields on the report to do this way, but I know there
MUST be a way to do it in a loop.

If anyone can help, I would much appreciate it!!!
Click to expand...
Click to expand...
 
At the cost of some speed you might be able to use DLOOKUP in the loop

SELECT CASE DLookup("Top Msg " & intCount & " atr", "YourTableName",
"build your where criteria to select the correct row value"

I think that for speed and ease of maintenance, you would be better off with the
attribute on the form and just set the control's visible property to False.
Thanks!

OK, that works IF both the field to be formatted and the attribute field are
on the report. Is there a way to reference the current record to get the
attribute values so that I don't need to include the attribute fields on the
report. FYI: each page of the report is one record from a table, but the
sort order of the report does not match the order of the records in the
table.

John Spencer (MVP) said:
If your fields are named as indicated you can use a different reference.

In the OnPrint of the Details section (or whichever section contains the controls)


Dim intCount as Integer

For IntCount = 1 to 4 'or whatever is the maximum value of the field
Me("Top Msg " & intcount).fontBold = False
Me("Top Msg " & intcount).FontItalic=False
...
Select Case Me("Top Msg " & intCount & " atr")
Case "N"
'Do nothing
Case "B"
Me("Top Msg " & intcount).FontBold= True
Case "I"
...
End Select
Next intCount
I have a table that contains multiple fields that are in pairs. One field
containing a piece of data to be printed on a report and another field that
contains a single character to controld the field formatting.

Example-

top msg 1 = "Joe Brown" / top msg 1 atr = "B" ' this means that I need
the field on the report to print bold
top msg 2 = "Mary Jane" / top msg 2 atr = "U" ' this means that I need
the field on the report to print underline
top msg 3 = "Joe Brown" / top msg 3 atr = "N" ' this means that I need
the field on the report to print normal
top msg 4 = "Mary Jane" / top msg 4 atr = "I" ' this means that I need
the field on the report to print italic

What I would like to do is set up a loop (I will have a known number of
fields to loop through) and upon formatting each page of the report I want
to run the code that changes the formatting of the report field. The fields
are all named the same except for a numeric value in the field name. I would
rather reference a field on the recordset than on the report itself. Here is
what I have done so far:

Select Case Me.[top msg 1 atr].Value
Case N
Me.top_msg_1.FontBold = False
Me.top_msg_1.FontItalic = False
Me.top_msg_1.FontUnderline = False
Case B
Me.top_msg_1.FontBold = True
Case I
Me.top_msg_1.FontItalic = True
Case U
Me.top_msg_1.FontUnderline = True
End Select

This code works for each field of the report, but requires me to place the
attribute field on the report as well. I just made the attribute field
hidden so that it does not print on the report. With this type of coding, I
will have to copy the select case for every field that I am formatting. I
only have about 60 fields on the report to do this way, but I know there
MUST be a way to do it in a loop.

If anyone can help, I would much appreciate it!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top