wiklendt
i recommend chocolate
- Local time
- Today, 19:50
- Joined
- Mar 10, 2008
- Messages
- 1,746
Hi,
i invite evenyone to tell me this is not possible, but i think this will present an interesting challenge to the more dedicated gurus... ;-P
i know super/sub- scripting is 'impossible' in text fields for tables. i know there are workarounds for labels in reports and forms. but i need something different than the suggestions i've read here.
what i want
What i want to do is a new approach (for Access) that i saw for an excel spreadsheet functionality to parse a cell and change the formatting appropriately (source: http://www.eng-tips.com/viewthread.cfm?qid=138208). This means the cell (which in access will be a field) is pre-written with formatting instructions. These instructions are as follows:
[] anything between brackets is subscript
{} anything between braces is superscript
// (and my own addition for my purposes) anything between forward slash is italics.
everything else is normal.
for example, in our lab, we have work with genes called:
blaOXA-30-likec, where the formatting would be like this (if you parse it mentally using the rules stated above):
/bla/[OXA-30]-like{/c/}
it is not important for the names to be formatted in the tables or even forms, but for the reports i'd like to be able to have these such names formatted as stated in the instrucitons.
what i've done so far
i have created a table with the following fields ('target' is gene of interest):
TargetID [PK; autonumber]
Target [text] (e.g., "blaOXA-30-likec")
Formatting [text] (this has the same text as "Target", but with typed in instructions (e.g., "/bla/[OXA-30]-like{/c/}"))
Product [text] (gene product upon translation of DNA)
ResistantTo [text] (resistance confered to bacteria once product is present)
i have created a simple tabular report with the fields (based on the table, admittedly, i should probably make the source a query?):
TargetID [Hidden]
Formatting
Product
ResistantTo
and i wish, on the "On Format" event of the Details section, to parse the "Formatting" field and arrange the formatting as specified in the instructions.
The code i have copy-pasted is this, although it needs de-excelling and recoded for access (the original code did not have included for italics, i added that myself by copy-pasting one of the "Do" commands and changing the superscript to italic):
can anyone help?
my first question, i suppose would be whether adding any of the brackets or braces or slashes will cause issues in the code in the first place?
and whether anyone can see a way to do this, because i'm too green to see a path at the moment...
(edit: here attached is the report as is currently printing (VBA without On Format invoked, because obviously the code is excel-specific and errors in Access, unsurprisingly)
i invite evenyone to tell me this is not possible, but i think this will present an interesting challenge to the more dedicated gurus... ;-P
i know super/sub- scripting is 'impossible' in text fields for tables. i know there are workarounds for labels in reports and forms. but i need something different than the suggestions i've read here.
what i want
What i want to do is a new approach (for Access) that i saw for an excel spreadsheet functionality to parse a cell and change the formatting appropriately (source: http://www.eng-tips.com/viewthread.cfm?qid=138208). This means the cell (which in access will be a field) is pre-written with formatting instructions. These instructions are as follows:
[] anything between brackets is subscript
{} anything between braces is superscript
// (and my own addition for my purposes) anything between forward slash is italics.
everything else is normal.
for example, in our lab, we have work with genes called:
blaOXA-30-likec, where the formatting would be like this (if you parse it mentally using the rules stated above):
/bla/[OXA-30]-like{/c/}
it is not important for the names to be formatted in the tables or even forms, but for the reports i'd like to be able to have these such names formatted as stated in the instrucitons.
what i've done so far
i have created a table with the following fields ('target' is gene of interest):
TargetID [PK; autonumber]
Target [text] (e.g., "blaOXA-30-likec")
Formatting [text] (this has the same text as "Target", but with typed in instructions (e.g., "/bla/[OXA-30]-like{/c/}"))
Product [text] (gene product upon translation of DNA)
ResistantTo [text] (resistance confered to bacteria once product is present)
i have created a simple tabular report with the fields (based on the table, admittedly, i should probably make the source a query?):
TargetID [Hidden]
Formatting
Product
ResistantTo
and i wish, on the "On Format" event of the Details section, to parse the "Formatting" field and arrange the formatting as specified in the instructions.
The code i have copy-pasted is this, although it needs de-excelling and recoded for access (the original code did not have included for italics, i added that myself by copy-pasting one of the "Do" commands and changing the superscript to italic):
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim NumSub
Dim NumSuper
Dim SubL
Dim SubR
Dim SuperL
Dim SuperR
Dim CheckSub
Dim CounterSub
Dim CheckSuper
Dim CounterSuper
Dim Cell
'
CheckSub = True
CounterSub = 0
CheckSuper = True
CounterSuper = 0
Cell = Formatting
'
NumSub = Len(Cell) - Len(Application.WorksheetFunction.Substitute(Cell, "[", ""))
NumSuper = Len(Cell) - Len(Application.WorksheetFunction.Substitute(Cell, "{", ""))
' format the subscript text
Do
Do While CounterSub <= 1000
SubL = Application.WorksheetFunction.Find("[", ActiveCell, 1)
SubR = Application.WorksheetFunction.Find("]", ActiveCell, 1)
ActiveCell.Characters(SubL, 1).Delete
ActiveCell.Characters(SubR - 1, 1).Delete
ActiveCell.Characters(SubL, SubR - SubL - 1).Font.Subscript = True
CounterSub = CounterSub + 1
If CounterSub = NumSub Then
CheckSub = False
Exit Do
End If
Loop
Loop Until CheckSub = False
' format the superscript text
Do
Do While CounterSuper <= 1000
SuperL = Application.WorksheetFunction.Find("{", ActiveCell, 1)
SuperR = Application.WorksheetFunction.Find("}", ActiveCell, 1)
ActiveCell.Characters(SuperL, 1).Delete
ActiveCell.Characters(SuperR - 1, 1).Delete
ActiveCell.Characters(SuperL, SuperR - SuperL - 1).Font.Superscript = True
CounterSuper = CounterSuper + 1
If CounterSuper = NumSuper Then
CheckSuper = False
Exit Do
End If
Loop
Loop Until CheckSuper = False
' format the italic text
Do
Do While CounterSuper <= 1000
SuperL = Application.WorksheetFunction.Find("{", ActiveCell, 1)
SuperR = Application.WorksheetFunction.Find("}", ActiveCell, 1)
ActiveCell.Characters(SuperL, 1).Delete
ActiveCell.Characters(SuperR - 1, 1).Delete
ActiveCell.Characters(SuperL, SuperR - SuperL - 1).Font.Italic = True
CounterSuper = CounterSuper + 1
If CounterSuper = NumSuper Then
CheckSuper = False
Exit Do
End If
Loop
Loop Until CheckSuper = False
End Submy first question, i suppose would be whether adding any of the brackets or braces or slashes will cause issues in the code in the first place?
and whether anyone can see a way to do this, because i'm too green to see a path at the moment...
(edit: here attached is the report as is currently printing (VBA without On Format invoked, because obviously the code is excel-specific and errors in Access, unsurprisingly)
Last edited:
