Lines on Forms

[shadow9449]

I know that Access lets you plot lines on reports, so you can create your own graphs programatically.

In VB, you can program lines on a form as well.

I cannot fathom why you can't in Access, though.

Has anyone discovered a viable work around to this? I'd like to be able to create graphs (beyond the simple ones provided in MS Graph) on my forms, without having to display it to a report.

SHADOW

 

[llkhoutx]

A form in design view will cause the chart icon to appear in the Insert menu. child that icon. Take a look at it.

Access' VBA is more user friendly, i.e. easier to use that VB.

 

[shadow9449]

Yes, but I want to make my own graphs. I want to create a point and click graph specialized with certain industry-specific symbols.

IT's easy to do, but you have to be able to tell it where to draw the lines, which is what I'm after.
SHADOW

 

[llkhoutx]

There is a line on the ToolBox icon, which which you can specify it's position and length.

 

[shadow9449]

I know how to place a line on a form. But sometimes you need to create one dynamically, so a user can draw a point and click graph on the screen rather than having to input data and have the program draw the graph.

Imagine a grid where the user can click two points and connect the points with a line and keep connecting points that way to plot a graph.

SHADOW

 

[llkhoutx]

I understand what you trying to do. I don't think there's a direct way to do it in Access.

I suggest that you start a new thread.

 

[shadow9449]

I did.

 

[dt01pqt]

Here is a basic solution that works (tested in Ak97). I suggest you draw within an MS Forms frame as a sort of bounding box. If you use the forms DC the line will only be partially visible because of all the child objects associated with pages, etc. In order to fix you have to code a whole lot more. The reason why you use a MS Forms Frame rather than an Access one and its handle is easy to capture and you can get the DC from that. However when you resize the line will disappear so you need to redraw the line on the resize event.

Place this in a module:

Option Compare Database
Option Explicit

Private Declare Function LineTo Lib "gdi32" (ByVal hdc As Long, ByVal X As Long, _
  ByVal Y As Long) As Long
  
Private Declare Function MoveToEx Lib "gdi32" (ByVal hdc As Long, ByVal X As Long, _
  ByVal Y As Long, lpPoint As Any) As Long
  
Private Declare Function apiGetDC Lib "user32" Alias "GetDC" _
    (ByVal hWnd As Long) As Long

Private Declare Function apiGetFocus Lib "user32" _
        Alias "GetFocus" _
         () As Long

Public Function LineX(Ctrl As Control, X As Long, Y As Long, cx As Long, cy As Long)
Dim DC As Long
'device context
Dim hWndCtrl As Long
'bounding control handle
hWndCtrl = fhWnd(Ctrl)
'get control handle
DC = apiGetDC(hWndCtrl)
'get control DC
MoveToEx DC, X, Y, ByVal 0&
'set starting point
LineTo DC, cx, cy
'draw line from starting point to cx, cy
End Function
         
Private Function fhWnd(ctl As Control) As Long
    'use getfocus to retrieve ctrl handle
    On Error Resume Next
    ctl.SetFocus
    If Err Then
        fhWnd = 0
    Else
        fhWnd = apiGetFocus
    End If
    On Error GoTo 0
End Function

Create a form and place a MS Forms2 Frame (AxtiveX) called Frame1. You can find it by clicking on the 'more controls' (hammer and spanner) button at the bottom of the toolbox. Put what ever else you want on there. To draw the line use:

LineX Frame1, [I]x, y, cx, cy[/I]

substituting your own co-ordinates. Don't forget to redraw the line on resize. If you want you could use a pollyline, which would suit a graph better.

Ok this is one thing but getting this to suit the particular needs of the graph is another. I am going to need more information on the data going in the graph. Is it from a number of records or is it specific to one record? How often or under what circumstances is the chart going to update?

I hope this helps tell me if it works in your version of Access.

 

[shadow9449]

Hi, dt:

Thanks for your help. This underscores the need for a simple way to draw lines without using ActiveX controls and the windows API!

Let's go over your solution:

I suggest you draw within an MS Forms frame as a sort of bounding box.

I inserted the MS Forms Frame ActiveX control. What is it?

If you use the forms DC the line will only be partially visible

What is a DC?

Don't forget to redraw the line on resize

Do you mean should the user resize the Access form that contains the frame?

If you want you could use a pollyline, which would suit a graph better.

What is a pollyline?

I hope this helps tell me if it works in your version of Access.

I put some numbers in and put a LineX call in the GotFocus event of the Frame (wasn't much choice of events). I saw a brief flash of a line, and then it disappeared.

Is it from a number of records or is it specific to one record?

Specific to one record. The user will take certain measurements for a patient, and plot the results once a year or so.

How often or under what circumstances is the chart going to update?

Probably the user will just input it once.

As you can see, there is a huge learning curve associated with a feature that is simple to accomplish in Access Reports, and in VB. I know nothing about Windows programming (as you can tell), hence all the dumb questions.

I'm always willing to learn something new, but my beef is that Access should have some way to make all this easy for the user without having to study a whole lotta Windows details.

Thanks for your help, dt. I'll stay tuned for your answers.

SHADOW

 

[dt01pqt]

Thanks for your help. This underscores the need for a simple way to draw lines without using ActiveX controls and the windows API!

I'm always willing to learn something new, but my beef is that Access should have some way to make all this easy for the user without having to study a whole lotta Windows details.

I'm sure they could come up with something but it is not simple considering the original concept, you know how people moan when things change.

I inserted the MS Forms Frame ActiveX control. What is it?

Pretty much the frame control in Visual Basic

What is a DC?

Device Context

Do you mean should the user resize the Access form that contains the frame?

Yes

What is a pollyline?

Did you do join up the dots when you were a kid? You have an array of vector points in a set order and they are joined together with lines. If you do it this way you don't have to draw lots of lines to plot the graph it can be done in one operation.

I put some numbers in and put a LineX call in the GotFocus event of the Frame (wasn't much choice of events). I saw a brief flash of a line, and then it disappeared.

Best not use an event on the frame it is only really there to provide a bounding box. For now if you want to see how it will display use a command button on the form or something.

Specific to one record. The user will take certain measurements for a patient, and plot the results once a year or so.

So you want to show this chart for both data entry and data view and each record will have a different plot? Few, I was hoping this was the case.

If you want in data entry you could have the user actually plot the points on the chart rather than entering them through text fields. Then you can put on you CV that you made a simple CAD program. Obviously it depends on the accuracy you want to achieve.

Probably the user will just input it once.

Seeing that this is medical data I imagine you will not want some users to amend existing records to prevent the falsification of records and criminal negligence?

As you can see, there is a huge learning curve associated with a feature that is simple to accomplish in Access Reports, and in VB. I know nothing about Windows programming (as you can tell), hence all the dumb questions.

I know only what I need to know. It just so happens you have something that interests me. These questions are not dumb. I bet 99% of the people on here don't have a clue what we are talking about because this is beyond access programming really.

Sounds interesting what you are trying to do. If you attach a sample DB with one form and five or so records I will give it a go. Also a description of the chart (i.e. pie, line, spidergraph) Can you save Ak97 because my other machine is not up an running?

Later I might write some sample code in the code repository as to how you can draw basic shapes and set colour, type, and fill at runtime. Check out my other stuff there.

 

[shadow9449]

dt:

Wow...I'm touched that you've gone so far to help explain all this stuff to me. <<Sniff!>> I'm glad you realize that it's outside the scope of the Access user.

I'll read up on Device Contexts (and maybe some other Windows stuff) I vaguely remember the term from a Visual C++ course I took (and hated...I ended up going with VB because it was so much easier to get things done.)

Actually, I don't want to provide a sample of my chart. I created it years ago and hate how I did it...I'll probably redo the entire thing one day if I can ever get the lines drawn.

Conceptually, here's how it's done:

I set up a table with the fields 1_1, 1_2, 1_3, etc for a matrix of 10X20. I then have a matrix of bound text boxes that are named by the same names as the field names. When the user clicks on the text box, depending on certain combinations of toggle buttons, the program enters and stores a symbol (one of 6 different symbols, depending on the type of test conducted)

The reason I hate this scheme is because it's terribly not normalized. I should probably make the table only store the data by using SQL or DAO insertions when the unbound text box is clicked. Here's what a table might look like:

X Y Symbol
1 5 X
2 7 O
9 3 [

And a simple loop on the OnCurrent event will put the symbols in the right place and then connect the nodes with lines.

I'll get back to you with more questions once I've done my homework.

Thanks again,

SHADOW

 

[dt01pqt]

I'll read up on Device Contexts (and maybe some other Windows stuff) I vaguely remember the term from a Visual C++ course I took (and hated...I ended up going with VB because it was so much easier to get things done.)

Actually, I don't want to provide a sample of my chart. I created it years ago and hate how I did it...I'll probably redo the entire thing one day if I can ever get the lines drawn.

I'll get back to you with more questions once I've done my homework.

Fair enough. Yes C++ is quite difficult VB is more logical, however it doesn't in-build an understanding of graphics so many of VB users are oblivious to the actual process involved. I think what you have proven is that access is a good databasing program but customer expectations of a user interface are much greater. They don't just want data recalled they want custom visual aids to help them understand it better. This means you must have graphical freedom without disturbing the original concept of pages and datasheets. I will place a tutorial about drawing in real-time in the code repository.

 

[shadow9449]

dt:

it doesn't in-build an understanding of graphics so many of VB users are oblivious to the actual process involved.

Right. That's how I ended up using VB and Access (and some C for areas that I felt would be better in C) over the past 10 years, and still know nothing about Windows (other than some API calls for stuff like common dialogue boxes)

I think what you have proven is that access is a good databasing program but customer expectations of a user interface are much greater

It's really not THAT bad. You can use all kinds of bitmaps and images if you want, and day to day stuff can be done more than adequately with what you have. It's just the unusual stuff like custom charting that falls short.

I will place a tutorial about drawing in real-time in the code repository

That would be super. I can't just wait and hope that Microsoft will provide a better interface for this, although that would be ideal.

I think we are on the same page here. The way we got into the discussion was because of my request on Pat's wish list. (Speaking of which, I wonder what happened...)

SHADOW

 

[shadow9449]

DT01:

(Or anyone who knows the answer).

After a year and a half, I've gone back to trying to create my custom chart. I'm able to use your code to draw lines on the Forms 2 Frame, and I have 2 questions:

1) Is there any way to set the colour of the frame to anything other than the grey? I've tried covering it with a white box, but then the box covers the lines.

2) I am having difficulty with getting the lines to draw in the correct place. My guess is that it's a matter of knowing what units are being calculated.

For example, let's say I want it to draw a line from a character (we'll call it lbl1) that was programmatically placed at one point to another character (we'll call it lbl2)

What I did was say:

x1 = lbl1.left
y1 = lbl1.top

x2 = lbl2.left
y2 = lbl2.top

LineX x1, y1, x2, y2

The lines draw all over the place. My guess is that getting the .top and .left properties of a control uses one measure (twips, pixels, whatever) and the LineX function is using a different one.

Would you be able to clarify and show me how to convert so I can control where the lines appear?

Thanks

SHADOW