Parsing each character to individual boxes

[Duane Hookom]

My suggestion wasn't to send the report name. It was to send the report
object. If you want to send the report name then you would need code in your
function like:
Dim rpt As Report
Set rpt = Reports(strReportName)

--
Duane Hookom
MS Access MVP

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.

 

[Duane Hookom]

Here is code that centers the text in the drawn boxes:

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 = 300 '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
Dim lngCharWidth As Long 'text width
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Arial"

For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 12632256, B
'find the text width of the next character
lngCharWidth = TextWidth(Mid(strLastName & Space(lngBoxCount), lngI,
1))
'find the left position that will center the character _
in the box
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth + (lngWidth -
lngCharWidth) / 2
Me.CurrentY = lngTop + 20
Me.ForeColor = vbBlack
Debug.Print Mid(strLastName & Space(lngBoxCount), lngI, 1), _
TextWidth(Mid(strLastName & Space(lngBoxCount), lngI, 1))
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP

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.

 

[Alp]

Hi Duane,

Sorry for the late response. Well, I have tried both (most probably I'm
making a mistake somewhere) but got similar error. Somehow I end up getting
an error and now trying to resolve that.

Thanks though. If I cn't resolve it I'll holler for your help again.

Alp

My suggestion wasn't to send the report name. It was to send the report
object. If you want to send the report name then you would need code in your
function like:
Dim rpt As Report
Set rpt = Reports(strReportName)

--
Duane Hookom
MS Access MVP

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.

 

[Alp]

Thanks for the code. Now the characters are not partially dissapearing.

Alp

Here is code that centers the text in the drawn boxes:

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 = 300 '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
Dim lngCharWidth As Long 'text width
strLastName = Me.txtLastName
Me.FontSize = 20
Me.FontName = "Arial"

For lngI = 1 To lngBoxCount
Me.Line (lngLeft + (lngI - 1) * lngWidth, lngTop)- _
Step(lngWidth, lngHeight), 12632256, B
'find the text width of the next character
lngCharWidth = TextWidth(Mid(strLastName & Space(lngBoxCount), lngI,
1))
'find the left position that will center the character _
in the box
Me.CurrentX = lngLeft + (lngI - 1) * lngWidth + (lngWidth -
lngCharWidth) / 2
Me.CurrentY = lngTop + 20
Me.ForeColor = vbBlack
Debug.Print Mid(strLastName & Space(lngBoxCount), lngI, 1), _
TextWidth(Mid(strLastName & Space(lngBoxCount), lngI, 1))
Me.Print Mid(strLastName & Space(lngBoxCount), lngI, 1)
Next
End Sub

--
Duane Hookom
MS Access MVP

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.

 

[Alp]

Hi John,

Well, I was refering to Duane's code for the centering. The code you have
suggested does not have that problem since it is parsing to pre-defined,
formatted textboxes.

BTW, I couldn't manage to get the code out of the report itself to a module.
Errors, as usual. And I had to make another version to strip off the "/"
from date data so it parses properly. Somehow I thought the "/" or "-" were
not stored as the data itself but were rather an input mask.

Alp

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.

 

[John Spencer (MVP)]

Well, funny that you should mention it.

Public Sub PopulateBoxes2Fit(ControlAny As Control, boxCount As Integer)
'This code populates a series of controls on a form or report based on
'the value of another "Master" control. It expects to see a set of
'controls numbered from 1 to N that have the same name as the master
'control, but with a number appended to the end.
'For example with a master control and eight text controls, it would
'change SPENCER to [P] [E] [N] [C] [E] [R] [ ]

Dim iLoop As Integer
Dim strParse As String
Dim FormOrReport As Object

On Error GoTo ERROR_Exists

Set FormOrReport = ControlAny.Parent

strParse = ControlAny.Value & vbNullString
If Len(strParse) > (boxCount) Then
strParse = Left(strParse, boxCount)
End If

'Fill boxes with characters
For iLoop = 1 To Len(strParse)
FormOrReport(ControlAny.Name & iLoop) = Mid(strParse, iLoop, 1)
Next iLoop

'Clear remaining boxes
For iLoop = Len(strParse) + 1 To boxCount
FormOrReport(ControlAny.Name & iLoop) = Null
Next iLoop

EXIT_Sub:
On Error Resume Next
Set FormOrReport = Nothing
Exit Sub

ERROR_Exists:
MsgBox Err.Number & ": " & Err.Description, , "PopulateBoxes2Fit"
Resume EXIT_Sub
End Sub


As far as the problem with the slashes or whatever in dates, you could solve
that by having controls for the slashes. You could either make the controls
invisible and still populate them or you could set the border around the control
to tranparent.

Hi John,

Well, I was refering to Duane's code for the centering. The code you have
suggested does not have that problem since it is parsing to pre-defined,
formatted textboxes.

BTW, I couldn't manage to get the code out of the report itself to a module.
Errors, as usual. And I had to make another version to strip off the "/"
from date data so it parses properly. Somehow I thought the "/" or "-" were
not stored as the data itself but were rather an input mask.

Alp

S N I P

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.