Report design - is this a tall order?!

[Trish]

Hi, I am running Windows XP Pro and Office 2003. I have created a database
and am trying to make it as user-friendly as possible for a colleague who has
no knowledge of Access. In my course database I have a field that says
"number of places". The maximum number of places is 20. However, some
courses will only have 8 places. What I want to show on the form is 20
boxes. If there are 20 places available on the course, I want them all to
be empty. If the class will only allow 8 (because of limited facilities,
for example), I want to somehow shade or block-off the remaining 12. Hope
you know what I mean!! This has no bearing on any bookings that might
follow. I just somehow need to highlight the original number of places on
each course. Looking forward to some suggestions! Thanks in advance.

 

[tina]

well, i came up with something you may be able to use. to test my solution,
i created a form and added 20 boxes (Rectangle controls) to it, with
BackColor property of each set to [the numeric code for] white. *in order to
use the following code*, i named each box as Box1, Box2, Box3, ... Box20.

i added an unbound textbox (called txtCount), so i'd have a place to
designate "number of places", and a command button (Command8) to test the
"shade/unshade boxes" code. to shade the "blocked" boxes, i set the
BackColor property to gray.

Private Sub Command20_Click()

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.ControlType = acRectangle Then
If ExtractNumbers(Right(ctrl.Name, 2)) > Me!txtCount Then
ctrl.BackColor = 9868950 'medium gray
Else
ctrl.BackColor = 16777215 'white
End If
End If
Next

End Sub

Private Function ExtractNumbers(strFieldValue As String) As Byte
' *** THIS FUNCTION EXTRACTS THE NUMBERS
' FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER
' AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = CByte(strHold)

End Function

the ExtractNumbers() function is courtesy of a previous post by MVP Ken
Snell on 4/5/05 - which i found by searching Google Groups. i made one minor
adjustment to the function, for the purpose of this solution specifically.

hth

 

[Trish]

Thanks Tina, will test it out next week.

well, i came up with something you may be able to use. to test my solution,
i created a form and added 20 boxes (Rectangle controls) to it, with
BackColor property of each set to [the numeric code for] white. *in order to
use the following code*, i named each box as Box1, Box2, Box3, ... Box20.

i added an unbound textbox (called txtCount), so i'd have a place to
designate "number of places", and a command button (Command8) to test the
"shade/unshade boxes" code. to shade the "blocked" boxes, i set the
BackColor property to gray.

Private Sub Command20_Click()

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.ControlType = acRectangle Then
If ExtractNumbers(Right(ctrl.Name, 2)) > Me!txtCount Then
ctrl.BackColor = 9868950 'medium gray
Else
ctrl.BackColor = 16777215 'white
End If
End If
Next

End Sub

Private Function ExtractNumbers(strFieldValue As String) As Byte
' *** THIS FUNCTION EXTRACTS THE NUMBERS
' FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER
' AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = CByte(strHold)

End Function

the ExtractNumbers() function is courtesy of a previous post by MVP Ken
Snell on 4/5/05 - which i found by searching Google Groups. i made one minor
adjustment to the function, for the purpose of this solution specifically.

hth

Hi, I am running Windows XP Pro and Office 2003. I have created a database
and am trying to make it as user-friendly as possible for a colleague who has
no knowledge of Access. In my course database I have a field that says
"number of places". The maximum number of places is 20. However, some
courses will only have 8 places. What I want to show on the form is 20
boxes. If there are 20 places available on the course, I want them all to
be empty. If the class will only allow 8 (because of limited facilities,
for example), I want to somehow shade or block-off the remaining 12. Hope
you know what I mean!! This has no bearing on any bookings that might
follow. I just somehow need to highlight the original number of places on
each course. Looking forward to some suggestions! Thanks in advance.

 

[Trish]

Hi Tina, I've been away for a few days and only just got back to this. I
note that I said I wanted to do this in a form - actually I meant to say in a
report (sorry!). I know nothing of visual basic, although always willing to
try. Is your response still relevant? I was delighted to receive your
response! Thanks.

well, i came up with something you may be able to use. to test my solution,
i created a form and added 20 boxes (Rectangle controls) to it, with
BackColor property of each set to [the numeric code for] white. *in order to
use the following code*, i named each box as Box1, Box2, Box3, ... Box20.

i added an unbound textbox (called txtCount), so i'd have a place to
designate "number of places", and a command button (Command8) to test the
"shade/unshade boxes" code. to shade the "blocked" boxes, i set the
BackColor property to gray.

Private Sub Command20_Click()

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.ControlType = acRectangle Then
If ExtractNumbers(Right(ctrl.Name, 2)) > Me!txtCount Then
ctrl.BackColor = 9868950 'medium gray
Else
ctrl.BackColor = 16777215 'white
End If
End If
Next

End Sub

Private Function ExtractNumbers(strFieldValue As String) As Byte
' *** THIS FUNCTION EXTRACTS THE NUMBERS
' FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER
' AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = CByte(strHold)

End Function

the ExtractNumbers() function is courtesy of a previous post by MVP Ken
Snell on 4/5/05 - which i found by searching Google Groups. i made one minor
adjustment to the function, for the purpose of this solution specifically.

hth

Hi, I am running Windows XP Pro and Office 2003. I have created a database
and am trying to make it as user-friendly as possible for a colleague who has
no knowledge of Access. In my course database I have a field that says
"number of places". The maximum number of places is 20. However, some
courses will only have 8 places. What I want to show on the form is 20
boxes. If there are 20 places available on the course, I want them all to
be empty. If the class will only allow 8 (because of limited facilities,
for example), I want to somehow shade or block-off the remaining 12. Hope
you know what I mean!! This has no bearing on any bookings that might
follow. I just somehow need to highlight the original number of places on
each course. Looking forward to some suggestions! Thanks in advance.

 

[tina]

well, i tested the code, running it from a report's Open event, rather than
from the form's command button Click event. it ran fine. i had to move the
ExtractNumbers function into the report's module, as well. the only issue is
where you're getting the "number of boxes to un-shade". in the form, i used
an unbound textbox to manually type in the number, and referred to that
control in the code. you can see the reference as Me!txtCount, in the line
of code

If ExtractNumbers(Right(ctrl.Name, 2)) > Me!txtCount Then

in the report, i simply referred back to the same control in the open form,
as

If ExtractNumbers(Right(ctrl.Name, 2)) > Forms!MyForm!txtCount Then

how are you going to determine the number of boxes you want to "un-shade"
each time the report runs?
also note, i tested this on an unbound report. if your report is bound to a
table or query, and you want to set the "shaded/unshaded" boxes for *each*
record, then 1) the code should probably run from the specific report
section's Format event, and 2) i'm not sure it will work - you can only try
it out.

hth

Hi Tina, I've been away for a few days and only just got back to this. I
note that I said I wanted to do this in a form - actually I meant to say in a
report (sorry!). I know nothing of visual basic, although always willing to
try. Is your response still relevant? I was delighted to receive your
response! Thanks.

well, i came up with something you may be able to use. to test my solution,
i created a form and added 20 boxes (Rectangle controls) to it, with
BackColor property of each set to [the numeric code for] white. *in order to
use the following code*, i named each box as Box1, Box2, Box3, ... Box20.

i added an unbound textbox (called txtCount), so i'd have a place to
designate "number of places", and a command button (Command8) to test the
"shade/unshade boxes" code. to shade the "blocked" boxes, i set the
BackColor property to gray.

Private Sub Command20_Click()

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.ControlType = acRectangle Then
If ExtractNumbers(Right(ctrl.Name, 2)) > Me!txtCount Then
ctrl.BackColor = 9868950 'medium gray
Else
ctrl.BackColor = 16777215 'white
End If
End If
Next

End Sub

Private Function ExtractNumbers(strFieldValue As String) As Byte
' *** THIS FUNCTION EXTRACTS THE NUMBERS
' FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER
' AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = CByte(strHold)

End Function

the ExtractNumbers() function is courtesy of a previous post by MVP Ken
Snell on 4/5/05 - which i found by searching Google Groups. i made one minor
adjustment to the function, for the purpose of this solution specifically.

hth

Hi, I am running Windows XP Pro and Office 2003. I have created a database
and am trying to make it as user-friendly as possible for a colleague

who
has

no knowledge of Access. In my course database I have a field that says
"number of places". The maximum number of places is 20. However, some
courses will only have 8 places. What I want to show on the form is 20
boxes. If there are 20 places available on the course, I want them

all
to

be empty. If the class will only allow 8 (because of limited facilities,
for example), I want to somehow shade or block-off the remaining 12. Hope
you know what I mean!! This has no bearing on any bookings that might
follow. I just somehow need to highlight the original number of

places
on

each course. Looking forward to some suggestions! Thanks in advance.

 

[Trish]

Thanks Tina, I will have a go. The number of places won't change each time
the report is run, because we don't use that field to record the changing
number of enrolments made. We simply need to know what our starting point
is, eg only six places for a piano class, as there are only six pianos; only
12 places for a computer class, because there are only 12 pcs; 20 people can
attend a dance class because of the room size. The shading is not
important, as long as the printed version shows the maximum allowable places
for a given course. I can see I will have to learn visual basic one of these
days. Thanks.

well, i tested the code, running it from a report's Open event, rather than
from the form's command button Click event. it ran fine. i had to move the
ExtractNumbers function into the report's module, as well. the only issue is
where you're getting the "number of boxes to un-shade". in the form, i used
an unbound textbox to manually type in the number, and referred to that
control in the code. you can see the reference as Me!txtCount, in the line
of code

If ExtractNumbers(Right(ctrl.Name, 2)) > Me!txtCount Then

in the report, i simply referred back to the same control in the open form,
as

If ExtractNumbers(Right(ctrl.Name, 2)) > Forms!MyForm!txtCount Then

how are you going to determine the number of boxes you want to "un-shade"
each time the report runs?
also note, i tested this on an unbound report. if your report is bound to a
table or query, and you want to set the "shaded/unshaded" boxes for *each*
record, then 1) the code should probably run from the specific report
section's Format event, and 2) i'm not sure it will work - you can only try
it out.

hth

Hi Tina, I've been away for a few days and only just got back to this. I
note that I said I wanted to do this in a form - actually I meant to say in a
report (sorry!). I know nothing of visual basic, although always willing to
try. Is your response still relevant? I was delighted to receive your
response! Thanks.

well, i came up with something you may be able to use. to test my solution,
i created a form and added 20 boxes (Rectangle controls) to it, with
BackColor property of each set to [the numeric code for] white. *in order to
use the following code*, i named each box as Box1, Box2, Box3, ... Box20.

i added an unbound textbox (called txtCount), so i'd have a place to
designate "number of places", and a command button (Command8) to test the
"shade/unshade boxes" code. to shade the "blocked" boxes, i set the
BackColor property to gray.

Private Sub Command20_Click()

Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.ControlType = acRectangle Then
If ExtractNumbers(Right(ctrl.Name, 2)) > Me!txtCount Then
ctrl.BackColor = 9868950 'medium gray
Else
ctrl.BackColor = 16777215 'white
End If
End If
Next

End Sub

Private Function ExtractNumbers(strFieldValue As String) As Byte
' *** THIS FUNCTION EXTRACTS THE NUMBERS
' FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER
' AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = CByte(strHold)

End Function

the ExtractNumbers() function is courtesy of a previous post by MVP Ken
Snell on 4/5/05 - which i found by searching Google Groups. i made one minor
adjustment to the function, for the purpose of this solution specifically.

hth


Hi, I am running Windows XP Pro and Office 2003. I have created a
database
and am trying to make it as user-friendly as possible for a colleague who
has
no knowledge of Access. In my course database I have a field that says
"number of places". The maximum number of places is 20. However, some
courses will only have 8 places. What I want to show on the form is 20
boxes. If there are 20 places available on the course, I want them all
to
be empty. If the class will only allow 8 (because of limited facilities,
for example), I want to somehow shade or block-off the remaining 12.
Hope
you know what I mean!! This has no bearing on any bookings that might
follow. I just somehow need to highlight the original number of places
on
each course. Looking forward to some suggestions! Thanks in advance.