Bar Graph on Form (rectangle control code)

[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!

 

[Douglas J. Steele]

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?

 

[Gina Whipp]

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

 

[Duane Hookom]

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.

 

[dmasch]

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

 

[Gina Whipp]

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

 

[dmasch]

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

 

[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

 

[Gina Whipp]

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

 

[Duane Hookom]

dmasch,
Is this form continuous or single? This is critical information to providing
a solution.

 

[Gina Whipp]

Duane,

Great question because this will only work if Single form view.

--
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 Whipp]

dmasch,

Really need an answer to Duane's question... are you there? By the way,
all your post did show up, perhaps it's your newsreader?

--
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

 

[Douglas J. Steele]

FWIW, he appears to be connecting via the Microsoft web interface, not a
newsreader.

 

[Gina Whipp]

Doug,

Yep, you are right... but I have not heard from him to get him his sample.
Hopefully, he didn't get lost...

--
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]

Sorry for the delay. Single Form

dmasch,

Really need an answer to Duane's question... are you there? By the way,
all your post did show up, perhaps it's your newsreader?

--
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 Whipp]

Back to work I go then... I was concerned after Duane's question that you
thought this might work for a continuous form.

--
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

 

[Duane Hookom]

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.

 

[dmasch]

Duane,
That worked perfectly! Thanks so much.

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.

 

[Gina Whipp]

Well, guess I can just work on my sample for my website... Glad Duane came
up with a solution!

--
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

 

[Duane Hookom]

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.