Bar Graph on Form (rectangle control code)

  • Thread starter Thread starter dmasch
  • Start date Start date
D

dmasch

http://msdn.microsoft.com/en-us/library/dd758785.aspx
This website gives a good description of how to use a small amount of coding
to have a rectangle controlled by a field value on a report appear as a bar
graph when you print preview. I would like to do something very similar,
show a bar graph within a form based on a field's percentage (between 0 and
1). In the example they put the code into the OnFormat event property. I
would like my bar graphs to appear upon opening the form. Hope that I can
use very similar code as in the example but what would I put it in... OnLoad,
OnQuery?

Any suggestions would be most appreciated. Thanks in advance!
 
I'm not certain that it's possible to transfer that technique from a report
to a form. There are some significant differences between how forms and
reports get rendered that may prevent it from working.

What have you tried?
 
dmasch,

I have a pie chart on a form that changes based on Customer, Production
Month, Production Year... However, the Bar CHart changes in the
After_Update event of the combo boxes. When the form opens it opens to the
the default of it's RecordSOurce would something like that work for you?
While I can't send it to you (it's embroiled in a larger program) I can
instruct you to it's set-up.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
The code would probably go in the On Current event of the form. However, if
your form is continuous, the results won't be what you want.
 
Gina, Duane and Douglas,
Thank you all for your replies. Gina, it sounds like you have something very
similar setup. Instuctions would be very helpful, yes.
Basically what I am trying to do is to have a rectangle's width be
controlled by a field on the form that is a percentrank from Excel (0-1 or
0-100%). For this to work the form would need to have a max width that is
100% like from the report example (4 inches). Hopefully this is possible.
Thanks,
dmasch
 
dmasch,

Yes, it sounds kinda close to what I have... I have to go out for a bit,
but when I come back I am going to break it out of the database it's in and
then I can either send it to you OR, I have decided to put it on my web
site, you can download it. (I decided to do this yesterday when I came
across your question, so I already started.)

With mine, you can make the form any size you want and since the chart is on
the form you can size it any size to fit on the form. Mine also has a way
to print it to a report.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Gina, Duane and Douglas,
Thanks for your replies. Gina, it seems that you have a similar solution in
your program. Instructions to set-up would be most helpful, thanks.
Basically I want the rectangle's width to be controlled by a % field on the
form (0-1 or 0-100%). Hopefully this is possible.
Thanks for your help,
dmasch
 
Hi Gina, Duane and Douglas,
Sorry if this duplicates, I tried replying twice this morning and it still
has not posted. Gina, it seems you have a similar solution in your program,
if you could send instructions for setup that would be great, thanks.
Basically, I want a rectangle's width controlled by a precentage field
within the form. Hopefully this is possible.
Thanks,
dmasch
 
NEWER USER,

Perhaps you didn't see my reply... I pasted below...

Yes, it sounds kinda close to what I have... I have to go out for a bit,
but when I come back I am going to break it out of the database it's in and
then I can either send it to you OR, I have decided to put it on my web
site, you can download it. (I decided to do this yesterday when I came
across your question, so I already started.)

With mine, you can make the form any size you want and since the chart is on
the form you can size it any size to fit on the form. Mine also has a way
to print it to a report.


--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
dmasch,
Is this form continuous or single? This is critical information to providing
a solution.
 
FWIW, he appears to be connecting via the Microsoft web interface, not a
newsreader.
 
Assuming a field in your form's record source named PctField.

You can add a rectangle control to your form where you want the horizontal
bar. Name it "recPct".

Add another similar rectangle control named rec100Pct that is the width of
100 percent. These can be one on top of the other with the recPct to the
front.

Add code in the On Current event of your form like:
Private Sub Form_Current()
If Not IsNull(Me.PctField) Then
Me.recPct.Width = Me.rec100Pct.Width * Me.PctField
End If
End Sub
As noted, this will not work on a continuous form.
 
Duane,
That worked perfectly! Thanks so much.

Duane Hookom said:
Assuming a field in your form's record source named PctField.

You can add a rectangle control to your form where you want the horizontal
bar. Name it "recPct".

Add another similar rectangle control named rec100Pct that is the width of
100 percent. These can be one on top of the other with the recPct to the
front.

Add code in the On Current event of your form like:
Private Sub Form_Current()
If Not IsNull(Me.PctField) Then
Me.recPct.Width = Me.rec100Pct.Width * Me.PctField
End If
End Sub
As noted, this will not work on a continuous form.
Click to expand...
 
You can get something similar in a continuous form using a text box with the
webdings font. Create a wide text box with these properties:

Control Source: =String([PctField]*20,"g")
Font Name: Webdings

You might need to change the "20" to some other value to get the size correct.
 
Back
Top