Parsing each character to individual boxes

[Alp]

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data string length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

 

[PC Datasheet]

Contact me at my email address below if you want help with this.

 

[fredg]

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data string length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[John Spencer (MVP)]

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following UNTESTED code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name plus a
1-up number. Then call the UNTESTED code below for each control that has to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data string length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out as I
also thought (and using actually).

John I had that function, a very similar one, in mind as well but wanted to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following UNTESTED code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name plus a
1-up number. Then call the UNTESTED code below for each control that has to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data string length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

Hi Steve,

Sent an email to you.

Alp

Contact me at my email address below if you want help with this.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Duane Hookom]

Here is a more interesting method that doesn't use multiple text boxes. The
code uses the Me.Print and Me.Line methods to do all the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const lngLeft As Long = 720 '1/2 inch
Const lngTop As Long = 360 '1/4 inch
Const lngWidth As Long = 250 'width of each box
Const lngHeight As Long = 400 'height of each box
Const lngBoxCount As Long = 20 'number of boxes

Dim lngI As Long 'for looping
Dim strLastName As String 'store name
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Courier New"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP

Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out as I
also thought (and using actually).

John I had that function, a very similar one, in mind as well but wanted
to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following UNTESTED code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name
plus a
1-up number. Then call the UNTESTED code below for each control that has to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data string length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

Hi John,

Actually the sub returns error.
If I use PopulateBoxes ("FullName"), then it gets stuck at the strParse =
Me(strControlName) & vbNullString with: "Run-time error '2465' Microsoft
Access can't find the field 'Me.FullName' referred to in your expression"

Any further suggestions?

Alp

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following UNTESTED code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name plus a
1-up number. Then call the UNTESTED code below for each control that has to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data string length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

Hi Duane,

It is indeed quite interesting! Tried to find a way to center the text
displayed but couldn't find a solution. Furthermore, the whole 6 pages of
the report needs to be done in a similar fashion but varying lengths and
locations. At some instances I also need to overlay some characters like
",", ".", "(" or ")" which would kind of complicate it a bit further.

This is besides the fact that I have to get acquainted with the "twips" v.s.
cm

Any hope?

Alp

Here is a more interesting method that doesn't use multiple text boxes. The
code uses the Me.Print and Me.Line methods to do all the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const lngLeft As Long = 720 '1/2 inch
Const lngTop As Long = 360 '1/4 inch
Const lngWidth As Long = 250 'width of each box
Const lngHeight As Long = 400 'height of each box
Const lngBoxCount As Long = 20 'number of boxes

Dim lngI As Long 'for looping
Dim strLastName As String 'store name
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Courier New"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP

Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out as I
also thought (and using actually).

John I had that function, a very similar one, in mind as well but wanted
to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following UNTESTED code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name
plus a
1-up number. Then call the UNTESTED code below for each control that

has
to be

parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data

string
length

is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

Hi Duane,

Without your consent (sorry about that, just forgot) I have converted your
suggestion into a function to be placed under the OnFormat event of detail
section as:
'Source provided by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As Long,
BoxHeight As Long, BoxNum As Long, whichField As String)
Dim lngLeft As Long ' = 2270 '720 '1/2 inch
Dim lngTop As Long ' = 75 '360 '1/4 inch
Dim lngWidth As Long ' = 285 'width of each box
Dim lngHeight As Long ' = 338 'height of each box
Dim lngBoxCount As Long ' = 28 'number of boxes
Dim strField As String

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

It seems to work when called as: Call BoxIt(2270, 75, 285, 338, 28,
Me.FullName) under the OnFormat event. The further question is: how can I
get rid of having to enter "Me." so the function could be of better use both
for me as well as whoever might need it? I also thought of setting the font
name and size in the call, maybe I will include that as well.

Alp

Here is a more interesting method that doesn't use multiple text boxes. The
code uses the Me.Print and Me.Line methods to do all the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const lngLeft As Long = 720 '1/2 inch
Const lngTop As Long = 360 '1/4 inch
Const lngWidth As Long = 250 'width of each box
Const lngHeight As Long = 400 'height of each box
Const lngBoxCount As Long = 20 'number of boxes

Dim lngI As Long 'for looping
Dim strLastName As String 'store name
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Courier New"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP

Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out as I
also thought (and using actually).

John I had that function, a very similar one, in mind as well but wanted
to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following UNTESTED code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name
plus a
1-up number. Then call the UNTESTED code below for each control that

has
to be

parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data

string
length

is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[John Spencer (MVP)]

Not really.

Did you put the field FullName on the report in a control? It has to be there,
but it can be invisible (visible property of control set to No).

Hi John,

Actually the sub returns error.
If I use PopulateBoxes ("FullName"), then it gets stuck at the strParse =
Me(strControlName) & vbNullString with: "Run-time error '2465' Microsoft
Access can't find the field 'Me.FullName' referred to in your expression"

Any further suggestions?

Alp

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following UNTESTED code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name plus a
1-up number. Then call the UNTESTED code below for each control that has to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number of boxes
are pre-set for each report item thus parsing stops when data string length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Duane Hookom]

Nice work.
You could send the current report to the function IE:
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, BoxNum As Long, _
whichField As String, whichReport as Report)
Then replace all "Me." with "whichReport."

--
Duane Hookom
MS Access MVP

Hi Duane,

Without your consent (sorry about that, just forgot) I have converted your
suggestion into a function to be placed under the OnFormat event of detail
section as:
'Source provided by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As Long,
BoxHeight As Long, BoxNum As Long, whichField As String)
Dim lngLeft As Long ' = 2270 '720 '1/2 inch
Dim lngTop As Long ' = 75 '360 '1/4 inch
Dim lngWidth As Long ' = 285 'width of each box
Dim lngHeight As Long ' = 338 'height of each box
Dim lngBoxCount As Long ' = 28 'number of boxes
Dim strField As String

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

It seems to work when called as: Call BoxIt(2270, 75, 285, 338, 28,
Me.FullName) under the OnFormat event. The further question is: how can I
get rid of having to enter "Me." so the function could be of better use
both
for me as well as whoever might need it? I also thought of setting the
font
name and size in the call, maybe I will include that as well.

Alp

Here is a more interesting method that doesn't use multiple text boxes. The
code uses the Me.Print and Me.Line methods to do all the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const lngLeft As Long = 720 '1/2 inch
Const lngTop As Long = 360 '1/4 inch
Const lngWidth As Long = 250 'width of each box
Const lngHeight As Long = 400 'height of each box
Const lngBoxCount As Long = 20 'number of boxes

Dim lngI As Long 'for looping
Dim strLastName As String 'store name
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Courier New"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP

Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out as I
also thought (and using actually).

John I had that function, a very similar one, in mind as well but
wanted
to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box"
controls.
Then you could use a sub routine something like the following
UNTESTED
code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name
plus
a
1-up number. Then call the UNTESTED code below for each control that has
to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data
(almos
all of them) needs to be displayed in a seperate box. The number
of
boxes
are pre-set for each report item thus parsing stops when data string
length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than
Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

I did, of course. I also realized I am calling the function at the wrong
place (OnOpen), now it is under the detail's OnFormat and works properly.

Sorry for the false alarm and thanks once again.

Alp

Not really.

Did you put the field FullName on the report in a control? It has to be there,
but it can be invisible (visible property of control set to No).

Hi John,

Actually the sub returns error.
If I use PopulateBoxes ("FullName"), then it gets stuck at the strParse =
Me(strControlName) & vbNullString with: "Run-time error '2465' Microsoft
Access can't find the field 'Me.FullName' referred to in your expression"

Any further suggestions?

Alp

With some creative naming you could use vba code to populate the "box" controls.
Then you could use a sub routine something like the following

UNTESTED
code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name

plus
a

1-up number. Then call the UNTESTED code below for each control that

has
to be

parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data (almos
all of them) needs to be displayed in a seperate box. The number

of
boxes

are pre-set for each report item thus parsing stops when data

string
length

is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

Thank you for the compliment.
I was referring to the "Me." in calling the function, not in the code
itself, as in
Call BoxIt(2270, 75, 285, 338, 28, Me.FullName) <<
But I found out that FullName also does work. Now the question is (as I
mentioned in the other thread) how to either modify the alignment or the
placement of the character since as it is now an Airal "I" gets lost because
it actually is printed on the left side line.
One more: what should I use to set the line color since I would need it to
be gary rather than the default black?

Alp

Nice work.
You could send the current report to the function IE:
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, BoxNum As Long, _
whichField As String, whichReport as Report)
Then replace all "Me." with "whichReport."

--
Duane Hookom
MS Access MVP

Hi Duane,

Without your consent (sorry about that, just forgot) I have converted your
suggestion into a function to be placed under the OnFormat event of detail
section as:
'Source provided by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As Long,
BoxHeight As Long, BoxNum As Long, whichField As String)
Dim lngLeft As Long ' = 2270 '720 '1/2 inch
Dim lngTop As Long ' = 75 '360 '1/4 inch
Dim lngWidth As Long ' = 285 'width of each box
Dim lngHeight As Long ' = 338 'height of each box
Dim lngBoxCount As Long ' = 28 'number of boxes
Dim strField As String

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

It seems to work when called as: Call BoxIt(2270, 75, 285, 338, 28,
Me.FullName) under the OnFormat event. The further question is: how can I
get rid of having to enter "Me." so the function could be of better use
both
for me as well as whoever might need it? I also thought of setting the
font
name and size in the call, maybe I will include that as well.

Alp

Here is a more interesting method that doesn't use multiple text boxes. The
code uses the Me.Print and Me.Line methods to do all the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const lngLeft As Long = 720 '1/2 inch
Const lngTop As Long = 360 '1/4 inch
Const lngWidth As Long = 250 'width of each box
Const lngHeight As Long = 400 'height of each box
Const lngBoxCount As Long = 20 'number of boxes

Dim lngI As Long 'for looping
Dim strLastName As String 'store name
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Courier New"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP


Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out

as
I

also thought (and using actually).

John I had that function, a very similar one, in mind as well but
wanted
to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box"
controls.
Then you could use a sub routine something like the following
UNTESTED
code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name
plus
a
1-up number. Then call the UNTESTED code below for each control

that
has

to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data
(almos
all of them) needs to be displayed in a seperate box. The number
of
boxes
are pre-set for each report item thus parsing stops when data string
length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than
Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[John Spencer (MVP)]

You are very welcome. And I'm glad it is working.

I've copied Duane's code and your modification to it. I will be testing it to
see how well it works. I have an upcoming project where I may need to do
something similar and if that code works I may use it vice setting up all the
controls on a report to do the "boxes".

I did, of course. I also realized I am calling the function at the wrong
place (OnOpen), now it is under the detail's OnFormat and works properly.

Sorry for the false alarm and thanks once again.

Alp

Not really.

Did you put the field FullName on the report in a control? It has to be there,
but it can be invisible (visible property of control set to No).

Hi John,

Actually the sub returns error.
If I use PopulateBoxes ("FullName"), then it gets stuck at the strParse =
Me(strControlName) & vbNullString with: "Run-time error '2465' Microsoft
Access can't find the field 'Me.FullName' referred to in your expression"

Any further suggestions?

Alp

With some creative naming you could use vba code to populate the "box"
controls.
Then you could use a sub routine something like the following UNTESTED
code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name plus
a
1-up number. Then call the UNTESTED code below for each control that has
to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data
(almos
all of them) needs to be displayed in a seperate box. The number of
boxes
are pre-set for each report item thus parsing stops when data string
length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

I have also made some additions to the code you've suggested just to
accomodate the need that some areas needed to be filled right aligned,
leaving some empty boxes from the starting box. I'm yet to figure out how I
can convert it into an external function (not within the report's own code).
The change I made is:

Sub PopulateBoxes2Fit(strControlName As String, boxCount As Integer)
'Code provided by John Spencer at microsoft.public.access.reports NG on
22/01/2005
Dim iLong As Integer
Dim pos As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString
If Len(strParse) < (boxCount) Then
pos = ((boxCount) - Len(strParse)) + 1
Else
pos = 1
End If
For iLong = 1 To Len(strParse)

Me(strControlName & pos) = Mid(strParse, iLong, 1)
pos = pos + 1
Next iLong

End Sub

And I call it under detail's format event as : Call
PopulateBoxes2Fit("mTaxNo", 13)

As I had indicated to Duane, I'm desperately trying to find out how I can
place the characters at the center of the generated boxes. No results yet.
:-(

BTW I have also made two more versions of the Duane's code; one to work with
cm input and the other for inches. I will most probably be using the cm
version since it makes things easier when yo can exactly know the
measurements from the report's design view. The only difference is i.e.
lngLeft = BoxLeft becomes lngLeft = (BoxLeft * 567). I've also added
whichReport as suggested by Duane. Code now works from a module and needs to
be called from the report. Just in case, it now is:

Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As Long,
BoxHeight As Long, BoxNum As Long, whichField As String, whichReport As
Report)
'The function below is called under a report detail section's Format event
as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed
Dim strReport As Report 'the report name where this function is used

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField
Set strReport = whichReport

Dim lngI As Long 'for looping
strReport.FontSize = 10
strReport.FontName = "Arial"
For lngI = 1 To lngBoxCount
strReport.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
strReport.CurrentX = lngLeft + (lngI - 1) * lngWidth
strReport.CurrentY = lngTop + 20
strReport.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

Alp

You are very welcome. And I'm glad it is working.

I've copied Duane's code and your modification to it. I will be testing it to
see how well it works. I have an upcoming project where I may need to do
something similar and if that code works I may use it vice setting up all the
controls on a report to do the "boxes".

I did, of course. I also realized I am calling the function at the wrong
place (OnOpen), now it is under the detail's OnFormat and works properly.

Sorry for the false alarm and thanks once again.

Alp

Not really.

Did you put the field FullName on the report in a control? It has to

be
there,

but it can be invisible (visible property of control set to No).

Alp wrote:

Hi John,

Actually the sub returns error.
If I use PopulateBoxes ("FullName"), then it gets stuck at the

strParse
=

Me(strControlName) & vbNullString with: "Run-time error '2465' Microsoft
Access can't find the field 'Me.FullName' referred to in your expression"

Any further suggestions?

Alp

With some creative naming you could use vba code to populate the "box"
controls.
Then you could use a sub routine something like the following UNTESTED
code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same

name
plus

a
1-up number. Then call the UNTESTED code below for each control

that
has

to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data
(almos
all of them) needs to be displayed in a seperate box. The

number
of

boxes
are pre-set for each report item thus parsing stops when data string
length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[John Spencer (MVP)]

UHM??? Can't you just set the control's text alignment to Center?

You should be able to select them all at once and do this.

Or am I missing something.

I have also made some additions to the code you've suggested just to
accomodate the need that some areas needed to be filled right aligned,
leaving some empty boxes from the starting box. I'm yet to figure out how I
can convert it into an external function (not within the report's own code).
The change I made is:

Sub PopulateBoxes2Fit(strControlName As String, boxCount As Integer)
'Code provided by John Spencer at microsoft.public.access.reports NG on
22/01/2005
Dim iLong As Integer
Dim pos As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString
If Len(strParse) < (boxCount) Then
pos = ((boxCount) - Len(strParse)) + 1
Else
pos = 1
End If
For iLong = 1 To Len(strParse)

Me(strControlName & pos) = Mid(strParse, iLong, 1)
pos = pos + 1
Next iLong

End Sub

And I call it under detail's format event as : Call
PopulateBoxes2Fit("mTaxNo", 13)

As I had indicated to Duane, I'm desperately trying to find out how I can
place the characters at the center of the generated boxes. No results yet.
:-(

BTW I have also made two more versions of the Duane's code; one to work with
cm input and the other for inches. I will most probably be using the cm
version since it makes things easier when yo can exactly know the
measurements from the report's design view. The only difference is i.e.
lngLeft = BoxLeft becomes lngLeft = (BoxLeft * 567). I've also added
whichReport as suggested by Duane. Code now works from a module and needs to
be called from the report. Just in case, it now is:

Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As Long,
BoxHeight As Long, BoxNum As Long, whichField As String, whichReport As
Report)
'The function below is called under a report detail section's Format event
as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed
Dim strReport As Report 'the report name where this function is used

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField
Set strReport = whichReport

Dim lngI As Long 'for looping
strReport.FontSize = 10
strReport.FontName = "Arial"
For lngI = 1 To lngBoxCount
strReport.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
strReport.CurrentX = lngLeft + (lngI - 1) * lngWidth
strReport.CurrentY = lngTop + 20
strReport.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

Alp

You are very welcome. And I'm glad it is working.

I've copied Duane's code and your modification to it. I will be testing it to
see how well it works. I have an upcoming project where I may need to do
something similar and if that code works I may use it vice setting up all the
controls on a report to do the "boxes".

I did, of course. I also realized I am calling the function at the wrong
place (OnOpen), now it is under the detail's OnFormat and works properly.

Sorry for the false alarm and thanks once again.

Alp

Not really.

Did you put the field FullName on the report in a control? It has to be
there,
but it can be invisible (visible property of control set to No).

Alp wrote:

Hi John,

Actually the sub returns error.
If I use PopulateBoxes ("FullName"), then it gets stuck at the strParse
=
Me(strControlName) & vbNullString with: "Run-time error '2465' Microsoft
Access can't find the field 'Me.FullName' referred to in your
expression"

Any further suggestions?

Alp

With some creative naming you could use vba code to populate the "box"
controls.
Then you could use a sub routine something like the following
UNTESTED
code.

Put a control on the report to hold the whole string and set its
visible
property to false. Then name all the box controls with the same name
plus
a
1-up number. Then call the UNTESTED code below for each control that
has
to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data
(almos
all of them) needs to be displayed in a seperate box. The number
of
boxes
are pre-set for each report item thus parsing stops when data
string
length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than
Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Duane Hookom]

Stephen Lebans would have a solution for the text width. I think you could
use the TextWidth method. I did a simple test using code in the loop like:
Debug.Print Mid(strLastName & Space(lngBoxCount), lngI, 1), _
TextWidth(Mid(strLastName & Space(lngBoxCount), lngI, 1))
I got values like:
D 288
a 221
v 197
o 221
l 86
i 86
o 221

To get the boxes gray, change your code to something like
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 12632256, B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.ForeColor = vbBlack
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next

--
Duane Hookom
MS Access MVP

Thank you for the compliment.
I was referring to the "Me." in calling the function, not in the code
itself, as in
Call BoxIt(2270, 75, 285, 338, 28, Me.FullName) <<
But I found out that FullName also does work. Now the question is (as I
mentioned in the other thread) how to either modify the alignment or the
placement of the character since as it is now an Airal "I" gets lost
because
it actually is printed on the left side line.
One more: what should I use to set the line color since I would need it to
be gary rather than the default black?

Alp

Nice work.
You could send the current report to the function IE:
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, BoxNum As Long, _
whichField As String, whichReport as Report)
Then replace all "Me." with "whichReport."

--
Duane Hookom
MS Access MVP

Hi Duane,

Without your consent (sorry about that, just forgot) I have converted your
suggestion into a function to be placed under the OnFormat event of detail
section as:
'Source provided by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As
Long,
BoxHeight As Long, BoxNum As Long, whichField As String)
Dim lngLeft As Long ' = 2270 '720 '1/2 inch
Dim lngTop As Long ' = 75 '360 '1/4 inch
Dim lngWidth As Long ' = 285 'width of each box
Dim lngHeight As Long ' = 338 'height of each box
Dim lngBoxCount As Long ' = 28 'number of boxes
Dim strField As String

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

It seems to work when called as: Call BoxIt(2270, 75, 285, 338, 28,
Me.FullName) under the OnFormat event. The further question is: how can I
get rid of having to enter "Me." so the function could be of better use
both
for me as well as whoever might need it? I also thought of setting the
font
name and size in the call, maybe I will include that as well.

Alp

Here is a more interesting method that doesn't use multiple text
boxes.
The
code uses the Me.Print and Me.Line methods to do all the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const lngLeft As Long = 720 '1/2 inch
Const lngTop As Long = 360 '1/4 inch
Const lngWidth As Long = 250 'width of each box
Const lngHeight As Long = 400 'height of each box
Const lngBoxCount As Long = 20 'number of boxes

Dim lngI As Long 'for looping
Dim strLastName As String 'store name
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Courier New"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP


Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out as
I
also thought (and using actually).

John I had that function, a very similar one, in mind as well but
wanted
to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box"
controls.
Then you could use a sub routine something like the following
UNTESTED
code.

Put a control on the report to hold the whole string and set its
visible
property to false. Then name all the box controls with the same name
plus
a
1-up number. Then call the UNTESTED code below for each control that
has
to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data
(almos
all of them) needs to be displayed in a seperate box. The
number
of
boxes
are pre-set for each report item thus parsing stops when data
string
length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than
Me.boxname
=
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Duane Hookom]

I posted some addition information that should allow Alp to determine the
width of the text prior to printing so that you can "pad" twips to the left
of the character.

--
Duane Hookom
MS Access MVP

UHM??? Can't you just set the control's text alignment to Center?

You should be able to select them all at once and do this.

Or am I missing something.

I have also made some additions to the code you've suggested just to
accomodate the need that some areas needed to be filled right aligned,
leaving some empty boxes from the starting box. I'm yet to figure out how
I
can convert it into an external function (not within the report's own
code).
The change I made is:

Sub PopulateBoxes2Fit(strControlName As String, boxCount As Integer)
'Code provided by John Spencer at microsoft.public.access.reports NG on
22/01/2005
Dim iLong As Integer
Dim pos As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString
If Len(strParse) < (boxCount) Then
pos = ((boxCount) - Len(strParse)) + 1
Else
pos = 1
End If
For iLong = 1 To Len(strParse)

Me(strControlName & pos) = Mid(strParse, iLong, 1)
pos = pos + 1
Next iLong

End Sub

And I call it under detail's format event as : Call
PopulateBoxes2Fit("mTaxNo", 13)

As I had indicated to Duane, I'm desperately trying to find out how I can
place the characters at the center of the generated boxes. No results
yet.
:-(

BTW I have also made two more versions of the Duane's code; one to work
with
cm input and the other for inches. I will most probably be using the cm
version since it makes things easier when yo can exactly know the
measurements from the report's design view. The only difference is i.e.
lngLeft = BoxLeft becomes lngLeft = (BoxLeft * 567). I've also added
whichReport as suggested by Duane. Code now works from a module and needs
to
be called from the report. Just in case, it now is:

Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As Long,
BoxHeight As Long, BoxNum As Long, whichField As String, whichReport As
Report)
'The function below is called under a report detail section's Format
event
as:
'Call BoxIt (2270, 75, 285, 338, 28, FullName, r_BE1_v2)
'Source code by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
'All units are in twips, except for BoxNum.
'( 1 inch = 1440 twips, 1 cm = 567 twips)
Dim lngLeft As Long ' starting point of boxes (720 = 1/2 inch)
Dim lngTop As Long ' top point of boxes (360 = 1/4 inch)
Dim lngWidth As Long ' width of each box
Dim lngHeight As Long ' height of each box
Dim lngBoxCount As Long ' number of boxes
Dim strField As String ' the field to be parsed
Dim strReport As Report 'the report name where this function is used

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField
Set strReport = whichReport

Dim lngI As Long 'for looping
strReport.FontSize = 10
strReport.FontName = "Arial"
For lngI = 1 To lngBoxCount
strReport.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
strReport.CurrentX = lngLeft + (lngI - 1) * lngWidth
strReport.CurrentY = lngTop + 20
strReport.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

Alp

You are very welcome. And I'm glad it is working.

I've copied Duane's code and your modification to it. I will be
testing it to
see how well it works. I have an upcoming project where I may need to
do
something similar and if that code works I may use it vice setting up
all the
controls on a report to do the "boxes".

Alp wrote:

I did, of course. I also realized I am calling the function at the
wrong
place (OnOpen), now it is under the detail's OnFormat and works properly.

Sorry for the false alarm and thanks once again.

Alp

Not really.

Did you put the field FullName on the report in a control? It has
to be
there,
but it can be invisible (visible property of control set to No).

Alp wrote:

Hi John,

Actually the sub returns error.
If I use PopulateBoxes ("FullName"), then it gets stuck at the strParse
=
Me(strControlName) & vbNullString with: "Run-time error '2465' Microsoft
Access can't find the field 'Me.FullName' referred to in your
expression"

Any further suggestions?

Alp

With some creative naming you could use vba code to populate
the "box"
controls.
Then you could use a sub routine something like the following
UNTESTED
code.

Put a control on the report to hold the whole string and set
its
visible
property to false. Then name all the box controls with the
same name
plus
a
1-up number. Then call the UNTESTED code below for each
control that
has
to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the
report data
(almos
all of them) needs to be displayed in a seperate box. The number
of
boxes
are pre-set for each report item thus parsing stops when
data
string
length
is reached and might leave some boxes empty. Same as
filling up a
pre-printed form.

What would be the proper approach? Something better than
Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's
own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.

 

[Alp]

After adding the report name (whichReport As Report) I am getting errors. At
least while calling the function from the report. I have tried various ways
(as far as my wit goes) but couldn't find the correct one.
Using: Call BoxIt(2270, 75, 285, 338, 28, FullName, (r_BE1_v2)) returns
Run-time error 424 Object required
Call BoxIt(2270, 75, 285, 338, 28, FullName, r_BE1_v2) returns a compile
error ByRef argument type mismatch
Call BoxIt(2270, 75, 285, 338, 28, FullName, "r_BE1_v2") returns a compile
Type mismatch
Call BoxIt(2270, 75, 285, 338, 28, FullName, ((r_BE1_v2))) returns Run-time
error 424 Object required

I played around with the function itself as well, still at same position...
It seems I just can't pass the report name properly to the function. I can
say I "know" it must be possible.

Alp

Nice work.
You could send the current report to the function IE:
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, _
BoxWidth As Long, BoxHeight As Long, BoxNum As Long, _
whichField As String, whichReport as Report)
Then replace all "Me." with "whichReport."

--
Duane Hookom
MS Access MVP

Hi Duane,

Without your consent (sorry about that, just forgot) I have converted your
suggestion into a function to be placed under the OnFormat event of detail
section as:
'Source provided by Duane Hookom at microsoft.public.access.reports NG on
22/01/2005
'Modified into a function by Alp Bekisoglu on 22/01/2005
Public Function BoxIt(BoxLeft As Long, BoxTop As Long, BoxWidth As Long,
BoxHeight As Long, BoxNum As Long, whichField As String)
Dim lngLeft As Long ' = 2270 '720 '1/2 inch
Dim lngTop As Long ' = 75 '360 '1/4 inch
Dim lngWidth As Long ' = 285 'width of each box
Dim lngHeight As Long ' = 338 'height of each box
Dim lngBoxCount As Long ' = 28 'number of boxes
Dim strField As String

lngLeft = BoxLeft
lngTop = BoxTop
lngWidth = BoxWidth
lngHeight = BoxHeight
lngBoxCount = BoxNum
strField = whichField

Dim lngI As Long 'for looping
Me.FontSize = 10
Me.FontName = "Arial"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strField & Space(lngBoxCount), lngI, 1)
Next
End Function

It seems to work when called as: Call BoxIt(2270, 75, 285, 338, 28,
Me.FullName) under the OnFormat event. The further question is: how can I
get rid of having to enter "Me." so the function could be of better use
both
for me as well as whoever might need it? I also thought of setting the
font
name and size in the call, maybe I will include that as well.

Alp

Here is a more interesting method that doesn't use multiple text boxes. The
code uses the Me.Print and Me.Line methods to do all the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const lngLeft As Long = 720 '1/2 inch
Const lngTop As Long = 360 '1/4 inch
Const lngWidth As Long = 250 'width of each box
Const lngHeight As Long = 400 'height of each box
Const lngBoxCount As Long = 20 'number of boxes

Dim lngI As Long 'for looping
Dim strLastName As String 'store name
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Courier New"
For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), , B
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth
Me.CurrentY = lngTop + 20
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP


Hi John and Fred,

Thanks for the advices. Looks like using the Mid is the only way out

as
I

also thought (and using actually).

John I had that function, a very similar one, in mind as well but
wanted
to
find out if there could be different approach(es).

Thanks again guys.

Alp

With some creative naming you could use vba code to populate the "box"
controls.
Then you could use a sub routine something like the following
UNTESTED
code.

Put a control on the report to hold the whole string and set its visible
property to false. Then name all the box controls with the same name
plus
a
1-up number. Then call the UNTESTED code below for each control

that
has

to be
parsed out - Populate "FieldName"

Sub PopulateBoxes(strControlName As String)
Dim iLong As Integer
Dim strParse As String

strParse = Me(strControlName) & vbNullString

For iLong = 1 To Len(strParse)

Me(strControlName & iLong) = Mid(strParse, iLong, 1)

Next iLong

End Sub


fredg wrote:

On Sat, 22 Jan 2005 04:56:55 +0800, Alp wrote:

Hi Experts,

In need to design a report where each character of the report data
(almos
all of them) needs to be displayed in a seperate box. The number
of
boxes
are pre-set for each report item thus parsing stops when data string
length
is reached and might leave some boxes empty. Same as filling up a
pre-printed form.

What would be the proper approach? Something better than
Me.boxname =
Mid(string, ?, 1) for each box's source.

Thanks in advance.

Alp

To parse each character of a Field (or any string) into it's own
control:
=Mid([FieldName],1,1)
=Mid([FieldName],2,1)
=Mid([FieldName],3,1)
etc.
in the appropriate unbound control.