Best technique for clickable bar display

[Baza]

Hi,



New to forum but have been programming for some time.

Looking at ways best to implement a schedule bar chart (as Excel might display), but in Access. I need to be able to detect actions occuring on the form (ie. clicks, drag and drop etc.).

Have tried embedding an Excel spreadsheet in a form. The advantage of using Excel component is that conditional formatting can generate the bars (eg. 1=RED, 2=GREEN, 3=BLUE).

It displays how I would prefer when disabled but I cannot interact with it unless it is enabled. Then it jumps out the form when clicked and becomes an actual spreadsheet.

I want to be able to drag and drop cells and move the bar positions around. There is a lot of data, so need to scroll it too.

I've also looked at Datasheets and Continuous Forms.

Has anyone had any experience of needing this and a neat way of doing it?

Thanks.

[ranman256]

Access charts just display data. Theres no need to be interactive.

[Baza]

Yes, I don't need an Access chart. I need the ability to display bars on a form that can be moved interactively.

[Micron]

AFAIK, what you want cannot be done in Access. There's no such thing as "dragging and dropping cells" on to Access objects or clicking on objects and dragging them around. Such changes are made in design view, but not with "cells" and your need sounds more like the behaviour one uses in drawing programs such as Visio.

It displays how I would prefer when disabled but I cannot interact with it unless it is enabled. Then it jumps out the form when clicked and becomes an actual spreadsheet.

That's the behaviour of embedded objects from other applications. In Access you can change properties such as length and colour of rectangles based on values, which can resemble chart bars, but still cannot "drag them around". They can respond to clicks although you'd have to code to change their position. You can also interact with Excel charts using mouse events (for example, to message about data related to a chart point) but neither application allows you to simply drag elements around. I'm wondering why you want to do this?

[Baza]

I need to show horizontal bars as time and be able to move them around by dragging them (as shown).

[CJ_London]

you can do this (to a certain extent) using the mouse events of the control. Change myControl to the name of your control

Option Compare Database
Option Explicit

Dim SX As Single
Dim SY As Single
Dim mseDown As Boolean

Private Sub myControl_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

        mseDown = True
        SX = X
        SY = Y

End Sub


Private Sub myControl_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim NX As Single
Dim NY As Single

    ChangeCursor Arrows_NSEW 'to indicate the control can be moved. This is my own function but see this link for alternatives http://bytes.com/topic/access/answer...g-mouse-cursor
    NY = myControl.Top + Y - SY
    NX = myControl.Left + X - SX
    If mseDown = True And NY >= 0 And NY <= InsideHeight - myControl.Height And NX >= 0 And NX <= InsideWidth - myControl.Width Then myControl.Move NX, NY
        
End Sub


Private Sub myControl_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    
    mseDown = False
    
End Sub

This allows both vertical and horizontal movement of the control within the viewable window and also assumes the whole form is within the viewable window (i.e. no scrollbars), if you only want to allow horizontal movement, just remove the NY from myControl.move (and modify changecursor to show Arrows_EW).

Note that line controls do not have events - use a box control or unassociated label with height set to 1 or 2 points and the back color set to black or whatever colour your want.

Note also that having dragged controls to where you want them, the new position will not be saved unless you save the form (and this is not possible in .mde/accde or runtime). I would suggest you would need an underlying table to store control positions so you can retrieve the coordinates and position them correctly the next time you open the form

If you want your form scrollable in the sense that as you drag a control to the left or the right the underlying form will scroll then you have a much bigger task

[Baza]

Thanks Ajax, that certainly kicks things off.

Need some kind of "snap to grid" ability and a grid background to work with next. Any thoughts on that?

Yes, the form needs to be scrollable as X = Time (by day), Y=Widgets (about 200).

I've found an ActiveX grid that might be able to do some of the scroll/zoom functions but I'm surprised that the GUI can't do this.

Rgds.

[CJ_London]

Need some kind of "snap to grid" ability

if you know your grid dimensions in twips, in your mousup event you could amend the left/top to the nearest grid point

so if grid dimensions are 200 twips whatever your left/top values are round up or down to the nearest 200 twips - I'm sure you can work out the maths

be aware that a form can have a maximum of something like 700 controls (this includes controls you have deleted). Maximum width/height is around 55cm so a gridline every cm could use 110 of these

and a grid background to work with next. Any thoughts on that?

you would need to use line controls which suggest you would position and size with code in the form open event. You could reduce the number of controls by using boxes instead - set to the appropriate width which would halve the number of controls required but make the navigation maths more complex - but has the benefit of being able to shade alternate rows/columns but setting the background colour.

Scrollable, as before - if the form is wider/taller than the window, it is not a problem, just use the scrollbars to scroll around the form. But if you want to be able to move a control and trigger scrolling at the same time then I can't help with that - you would need some sort of API which, depending on the position/size of the control being moved will tell the form to scroll. I would google something like 'vba to scroll form' or similar. One of the problems you may hit is triggering cascading mousemove events unintentionally - see this link https://msdn.microsoft.com/en-us/lib...ffice.11).aspx and understand what it says under notes.

[CJ_London]

just had a thought about gridline controls - you could create an image of your gridlines and use it for the form picture - you would need to experiment with using tiled or clipped. but don't know how well this would line up with the actual grid

[Baza]

Thanks again Ajax. Some interesting ideas in there.

My main concern is speed with the amount of data. One of the OCX grids I've seen can be scrolled back and forth across the form and has events that are called when more data needs to be loaded up. Have you tried these?

The visible grid is going to be about 4 months (in days) wide, and about 100 deep. So need rapid zoom in/out and fast scrolling with about 12,000 cells. I think latency will be an issue.

I've also looked at "Continuous Forms". Any thoughts on that?

Rgds.

[CJ_London]

One of the OCX grids I've seen can be scrolled back and forth across the form and has events that are called when more data needs to be loaded up. Have you tried these?

Nope, but interested to know more

4 months means your grid will be about 4mm per day wide. zoom in/out - turn off screen refreshing (docmd.echo false) as you resize if necessary (may help). additional complications with zooming but I'm sure they can be overcome. Google 'Stephen Lebans' he has done quite a lot with manipulating graphics

Doubt continuous forms will do the job - you cannot conditionally position controls for different rows