Too Many Controls...Workaround Ideas?

CHAOSinACT

Registered User.
Local time
Tomorrow, 04:12
Joined
Mar 18, 2009
Messages
235
I'm working on plant management software for the company database... Its a very robust database that does a great deal...this is the next hurdle and probably the hardest, if only due to memory management.

We have a bunch of machines and we want to track where and when they are working into the future - the jobs they are working on need to be displayed in a ms project like timeline interface and the job numbers need to be "drag and dropped" into the timeline and be editable.

I have drag and drop working thanks to an add in module, and the dialog to grab from is fine. my original idea for timeline was plant across dates with lots of controls for each day in the grid, see attached images for grid and job picker.

Problem is 25 machines x 70 days is 1750. Way over the number of controls aloud on a page. Even worse, the layout grid i'm positioning in counts toward controls, so no way.

I'm looking at flexgrid as an option though I have never used it and don't know how it works or if it's what i want. I'm also looking at using direct draw graphics using a wonderful class module by Stephen Lebans, God Bless Him, Its a life saver ( http://www.lebans.com/imageclass.htm ) ...But hey, if anyone has ANY ideas, please I would love to hear them.
 

Attachments

  • calendar spread.jpg
    calendar spread.jpg
    87.3 KB · Views: 175
  • jobnumber selector.JPG
    jobnumber selector.JPG
    58.7 KB · Views: 165
Update:

I tried a continuous form....it works awesome except I need unbound controls in there which apparently won't work with VBA since all rows end up the same when the control is unbound....Arggggh! Anyone know a way to make that work, I can't find any :(
 
Do the calculations by adding derived fields in the query instead of the unbound controls.
 
While it would be a hassle I could see how I might be able to make a query that loads all the data re: timeline.

What I don't see is how I could add data too it, especially with drag and drop...i need to fire a function when the data is dropped on the cell...all unbound controls (or otherwise, i think?) are non-unique names that can't really do that...to say the least about getting the cells to highlight on a mouse over event...They're all the same replicated control, which share the same properties....grrrr.....from a memory point of view i really like continuous forms but so limited to program for...

Has anyone tried ms Flex Grid plugin? can it do this kind of thing or same problems?
 
Possible but need more information…

Two Forms, 1 main and one subform.

The main form carries the controls for the timeline at the top of the screen and the Plant data at the left of the screen.

The subform is 70 vertical lines with 25 horizontal lines, 95 controls in total.
Over those 95 controls is 1 transparent command button.
The command button mouse down event uses the X and Y arguments to calculate which intersection of the 95 controls is being clicked on.

That’s the easy part. The difficult part is to know what you want to display at the intersection of the 95 controls.


Direct draw, using Stephen Lebans image class, should also be possible and may end up being the only way. Certainly it would be a bit of code but nothing I see as insurmountable. One thing that needs to be considered is if you would ever need to produce a Report based on the Form, but that too is doable.

http://www.access-programmers.co.uk/forums/showthread.php?t=214369

It’s doable but, as usual, the devil is in the detail.

Chris.
 
Thanks for the reply Chris...This is kinda where I'm going, except using the picture control instead of the command button.

The subform usage hadn't occurred to me though I'm still not sure how you mean? basically a strip of horiz and and a strip of vert controls? that won't give the look of the grid though will it? and I could probably use the existing main form controls if i'm not using continuous forms in the first place, don't think sub forms work with them anyway. If only we could mix continuous and non continuous forms...
 
As I said, the devil is in the detail.

Think about it, a form has a limit of ~755 controls. If we have a form and a subform then each form can have ~755 controls.

And why would it not look like a grid? The controls would be line controls and lines could look like a grid.

The sort of thing you are talking about will not simply fall out of a box and then be plugged into Access for use. It will require a lot of work but, given the detail and some imagination, it should be doable.

Chris.
 
you really need to rethink this problem in terms of a database

eg, doing it your way gives you problems if the number of machines changes

think of it in terms of displaying a subset (or all the machines) - therefore a maximum (at the moment) of 25 rows.

each row then needs to have 70 slots to identify the wherabouts of each machine daily.

you don't even necessarily have to show all 70 at ones - maybe you could just show 14 at a time, and then have a button that redisplays based on a change of date. you probably need to scroll left and right to see the other data anyway - so it is a amtter of taste whether you make your users scroll left and right, or give them a different way of accessing the data.

so this way, all you need to design is a continuous form, with a single row of 14 controls.

you need a function to reposition the data that feeds the controls.

and the database/data does all the work.

----
you can change this around, and make the rows based on dates- as you suggest, although I think this is less usual.

once you have it working this way, it then becomes easy to filter the data - select just certain machines, or certain dates, based on all kinds of criteria
 
you really need to rethink this problem in terms of a database

eg, doing it your way gives you problems if the number of machines changes

think of it in terms of displaying a subset (or all the machines) - therefore a maximum (at the moment) of 25 rows.

each row then needs to have 70 slots to identify the wherabouts of each machine daily.

you don't even necessarily have to show all 70 at ones - maybe you could just show 14 at a time, and then have a button that redisplays based on a change of date. you probably need to scroll left and right to see the other data anyway - so it is a amtter of taste whether you make your users scroll left and right, or give them a different way of accessing the data.

so this way, all you need to design is a continuous form, with a single row of 14 controls.

you need a function to reposition the data that feeds the controls.

and the database/data does all the work.

----
you can change this around, and make the rows based on dates- as you suggest, although I think this is less usual.

once you have it working this way, it then becomes easy to filter the data - select just certain machines, or certain dates, based on all kinds of criteria

The problem is management wants to see 2-3 months in a go minimum...the scrolling is happening anyway for longer term stuff...And coding for continuous forms so limited hard to see it working, with drag and drop i need it to respond and tell me exactly where it is...I'm still trying to wrap my head around how that would work exactly. but I would love it to to deal with the possible addition of new machines...having said that I just added enough rows to deal with the next 2 years and am trying to use one of the flexgrids at the moment...no idea how that will go, but seems promising.

I'll try to think how I could do it all with a single record source that I can add too and subtract from, bring up interactive dialogs when dropped on, etc...just doesn't seem possible, even if i could add the data raw the interaction to input and edit would be hard...
 
I still don’t have an accurate definition of the problem but attached is another look and feel demo. Select a project and drag and drop the bars.

It’s done with Label controls and so may not do the job due to the number limitations but I am reasonably confident it could be reproduce with the Image Class of Stephen Lebans.

Chris.
 

Attachments

I still don’t have an accurate definition of the problem but attached is another look and feel demo. Select a project and drag and drop the bars.

It’s done with Label controls and so may not do the job due to the number limitations but I am reasonably confident it could be reproduce with the Image Class of Stephen Lebans.

Chris.

That's the best damn thing I ever saw. I'll be looking at that extensively for inspiration!!! thank you sooooooo much, looks great. Needs a lot of work but has the framework of what i need and is a great proof of concept.
 
Well perhaps we have made a start.

In my signature there is a link to my SkyDrive site. If you go there you can select thumbnail view and that will show images which may be of some interest. The basics of drag and drop start with “Drag Drop” and progress in level of complexity.

“Drag Polygons” is different in that it uses Access controls to position/resize and Stephen Lebans Image Class to do the draw. In that demo there is a Report which uses the current selection on the Form.

I do not have a physical printer attached to my computer at the moment but in Report preview mode that Report is excessively pixelated. So I can not test if that Report would still be excessively pixelated when sent to hard copy. So you can do me a favour and physically print a copy of that Report to see if it would be acceptable.

That is the reason I mentioned Reports in post #5. Basically speaking, management does not know what they want until they see it. We may think they will not ask for it but they don’t even know what they want.

So we need to look forward to a time when they ask for something they say they will never need. That need reflects back on what we do now. If Stephen Lebans Image Class produces acceptable quality on hard copy then that’s it. If it doesn’t then we will need to draw it on the Report using the Access draw methods. Again that should be doable but we will need to save sufficient information to do the Report draw using the Access draw methods even if it is not required at the moment.


Therefore this is my question at the moment; does Stephen Lebans Image Class produce acceptable quality on hard copy when looking at curved lines?

Chris.
 
Well perhaps we have made a start.

In my signature there is a link to my SkyDrive site. If you go there you can select thumbnail view and that will show images which may be of some interest. The basics of drag and drop start with “Drag Drop” and progress in level of complexity.

“Drag Polygons” is different in that it uses Access controls to position/resize and Stephen Lebans Image Class to do the draw. In that demo there is a Report which uses the current selection on the Form.

I do not have a physical printer attached to my computer at the moment but in Report preview mode that Report is excessively pixelated. So I can not test if that Report would still be excessively pixelated when sent to hard copy. So you can do me a favour and physically print a copy of that Report to see if it would be acceptable.

That is the reason I mentioned Reports in post #5. Basically speaking, management does not know what they want until they see it. We may think they will not ask for it but they don’t even know what they want.

So we need to look forward to a time when they ask for something they say they will never need. That need reflects back on what we do now. If Stephen Lebans Image Class produces acceptable quality on hard copy then that’s it. If it doesn’t then we will need to draw it on the Report using the Access draw methods. Again that should be doable but we will need to save sufficient information to do the Report draw using the Access draw methods even if it is not required at the moment.


Therefore this is my question at the moment; does Stephen Lebans Image Class produce acceptable quality on hard copy when looking at curved lines?

Chris.

" Basically speaking, management does not know what they want until they see it. We may think they will not ask for it but they don’t even know what they want. "

Yes that's my whole world....
Picture class IS little pixelated but only on curves and very little need for curves in the project.

having said that...I've been going over everything, looking at how you did the re-size drag drop thing...that's brilliant but need a lot of adaption - Still by a wide margin the closest starting point I've seen to what I want to do... I'll spend this weekend (I'm in Australia so friday afternoon here...) going over how that works and seeing if the job could be done that way.... the picture control is more flexible but a harder job I would think, and time constraints are a real issue here.

I'll let you know on monday how it all went :)

Thanks to everyone for all the ideas and inspiration.
 
More proof of concept.

Main Form and one subform.

The Main Form carries the timeline using how many controls you may wish.

The subform carries one image control. The grid is drawn using a cutdown version of Stephen Lebans clsPictureBox Class to draw the grid. There are 700 Label controls for the bars to drag drop and resize.

It displays 70 Days for 50 Plants but that does not mean 3500 controls are needed.
If one Plant contiguously spans more than one day it only takes one control to do it.
With 50 Plants, the Plants have an average of 14 contiguous time spans per Plant.

The attached demo is in Access 2003 (Access 2000 file format.)
It was done at a screen resolution of 1280 x 1024.
It auto starts with frmForecasting. (Shift down to bypass.)


Chris.
 

Attachments

I think I'm getting somewhere as a programmer, I really do...But you've impressed me greatly here. I just haven't done any of this kind of interface, and it's brilliant and inspiring. I'm also going to take the effort to make myself use classes more as I know that's my weakness being self-taught.

That last proof of concept was brilliant and overcomes lots of limitations... between everything we've talked about I think I have everything I need to do this! What a relief.

I'll put up what I finish with in the end to show you how it all works out, thank you for you're time and effort.
 
I'm also going to take the effort to make myself use classes more as I know that's my weakness being self-taught.

Certainly classes are a powerful tool but don't just use them for the sake of using them. It is not uncommon to come across classes that don't really have a point.
 
No problem, it was fun.

Here’s another demo which may go closer to the answer.

I removed the vertical moving and resizing because it would be too easy for someone to place a bar on an incorrect plant. Besides, it had a bug in it. :D

The bars are now created from the main form by clicking on a plant label. They are coloured red to remind the user they have not been assigned a day range. That colour could be changed later, perhaps via a right click of the mouse.

Chris.
 

Attachments

Gidday Chris!

All is going well, though I am torturing myself over a point and was wondering if you had any input...

Attached is some or your code I've modified (I tried pasting it here but it removes all my indentation, yuck)...I'm trying to get the jobs to move "around" each other so they can't occupy the same point in space-time. I can get it to work no problem, though I suspect it needs some ironing out....(records are saving / loading and all that too)...

Anyway the bottom line is it REALLY slows down the mouse to check this much stuff...I can to it with one job conflict but 2 or more on a line get ridiculous. Even with only one job to conflict, it is noticeable.

My question is...I ended up using an array to to save the X conflict values (see Private Sub MoveHorizontally, array is called : arrConflictRanges)...I'm wondering if anyone knows what ms access performance is like with arrays. If I find a way around the arrays would that improve performance? Or am I kidding myself trying to run this kind of thing in real time anyway?

Thanks as always!

ChaosInACT
 

Attachments

Put pasted code between code tags to maintain indentation. It also uses a fixed width font and will distinguish two quotes from a double quote.
 
ah, thanks.


Code:
Private Sub MoveHorizontally(ByVal sngX As Single)
    Dim lngNewX As Long
    Dim testNewX As Long
    Dim found As Byte
    found = 0
    Erase arrConflictRanges()
    Dim x As Byte
    Dim NumberOfConflicts As Byte
    'determine number of conflicts(extra jobs on row)
    NumberOfConflicts = IsMoreThanOneJob(objThisControl.Tag) - 1
    lngNewX = objThisControl.Left + sngX - sngOldX

    If NumberOfConflicts <> 0 Then
        ReDim arrConflictRanges(NumberOfConflicts, 2)
        'load known conflicts
        GetConflictRanges objThisControl.Name, objThisControl.Tag, arrConflictRanges(), NumberOfConflicts
        For x = 1 To NumberOfConflicts
            Debug.Print "oldCursorValue:" & objThisControl.Left + sngX - sngOldX
            If found = 0 Then
                'check conflicts against now
                testNewX = IsBetweenRange(arrConflictRanges(x, 1), arrConflictRanges(x, 2), objThisControl.Left + sngX - sngOldX, objThisControl.Left + objThisControl.Width + sngX - sngOldX, objThisControl.Width)
                If testNewX <> lngNewX Then
                    found = 1
                    lngNewX = testNewX
                Else
                    found = 0
                End If
            End If
            Debug.Print "NewCursorValue:" & lngNewX

        Next x
                With objThisControl
                    'lngNewX = .Left + sngX - sngOldX
                        'if withint left and right boundaries proceed
                        If lngNewX >= lngMinLeft And lngNewX <= lngMaxRight - .Width Then
                           .Left = lngNewX
                        'if not check...too far left set to x left
                        ElseIf lngNewX < lngMinLeft Then
                           .Left = lngMinLeft
                        'if not check...too far right set to x right
                        ElseIf lngNewX > lngMaxRight - .Width Then
                           .Left = lngMaxRight - .Width
                        End If

                End With

    Else
        With objThisControl
            'lngNewX = .Left + sngX - sngOldX
                'if withint left and right boundaries proceed
                If lngNewX >= lngMinLeft And lngNewX <= lngMaxRight - .Width Then
                   .Left = lngNewX
                'if not check...too far left set to x left
                ElseIf lngNewX < lngMinLeft Then
                   .Left = lngMinLeft
                'if not check...too far right set to x right
                ElseIf lngNewX > lngMaxRight - .Width Then
                   .Left = lngMaxRight - .Width
                End If
    
        
        End With
    End If
    


End Sub

Private Function IsMoreThanOneJob(MachineType As String) As Integer
    Dim UseTable As String
    UseTable = "tblPlantAssignments"

    With CurrentDb.OpenRecordset(" Select *" & _
                                 " From " & UseTable & _
                                 " Where MachineTypeFK = '" & MachineType & "'")
        .MoveLast
        'Debug.Print .RecordCount
        IsMoreThanOneJob = .RecordCount
                  
    End With
End Function

Private Sub GetConflictRanges(ByVal BarName As String, ByVal MachineName As String, ByRef CurrentArray() As Long, ArrayLength As Byte)
    Dim UseTable As String
    UseTable = "tblPlantAssignments"
    Dim x As Integer
    
    With CurrentDb.OpenRecordset("SELECT * " & _
                                 "From " & UseTable & _
                                " WHERE (MachineTypeFK = '" & MachineName & "') AND (ControlHandle <> '" & BarName & "')")
        .MoveFirst
        For x = 1 To ArrayLength
            CurrentArray(x, 1) = StartDateLeft(!StartDate)
            CurrentArray(x, 2) = FinshDateRight(!FinishDate)
            Debug.Print "array  start result:" & CurrentArray(x, 1)
            Debug.Print "array finish result:" & CurrentArray(x, 2)
            .MoveNext
        Next x
    End With
End Sub

Private Function StartDateLeft(Start As Date) As Long
    Dim Width As Long
    Dim GridLeft As Long
    Dim ctl As Control
    Dim x As Integer
    Dim SeekDate As Date
    
    
    SeekDate = Day(Start) & "-" & Month(Start) & "-" & Year(Start)
    
    x = 1
    For x = 1 To conNumDays
        If SeekDate = Forms!frmSampleForecasting("DaysOFWeek_" & x).Caption & "-" & Forms!frmSampleForecasting("DaysOFWeek_" & x).Tag Then
            Set ctl = Forms!frmSampleForecasting("DaysOFWeek_" & x)
        End If
    
    Next x
    If Not IsNull(ctl) Then
        GridLeft = Forms!frmSampleForecasting!ctlDayGridSubform.Left
        Width = Forms!frmSampleForecasting!DaysOFWeek_1.Width
        'Debug.Print "StartDateLeft:" & ctl.Left + Width + GridLeft
        StartDateLeft = ctl.Left - GridLeft
    Else
        StartDateLeft = 0
    End If
    
End Function

Private Function FinshDateRight(Finish As Date) As Long
    Dim Width As Long
    Dim GridLeft As Long
    Dim ctl As Control
    Dim x As Integer
    Dim SeekDate As Date

    
    SeekDate = Day(Finish) & "-" & Month(Finish) & "-" & Year(Finish)
    
    x = 1
    For x = 1 To conNumDays
        If SeekDate = Forms!frmSampleForecasting("DaysOFWeek_" & x).Caption & "-" & Forms!frmSampleForecasting("DaysOFWeek_" & x).Tag Then
            Set ctl = Forms!frmSampleForecasting("DaysOFWeek_" & x)
        End If
    
    Next x
    If Not IsNull(ctl) Then
        GridLeft = Forms!frmSampleForecasting!ctlDayGridSubform.Left
        Width = Forms!frmSampleForecasting!DaysOFWeek_1.Width
        FinshDateRight = ctl.Left - GridLeft ' + Width

    Else
        FinshDateRight = 0
    End If
End Function

Private Function IsBetweenRange(ByVal ConflictLeft As Long, _
                                    ByVal ConflictRight As Long, _
                                    ByVal InitialValueLeft As Long, _
                                    ByVal InitialValueRight As Long, _
                                    ByVal BarLeft As Long) As Long
    'debug.Print "Initial Value:" & InitialValue
    If (InitialValueLeft >= ConflictLeft And InitialValueLeft <= ConflictRight) Or (InitialValueRight >= ConflictLeft And InitialValueRight <= ConflictRight) Then
        If Abs(ConflictLeft - InitialValueLeft) < Abs(ConflictRight - InitialValueRight) Then
            IsBetweenRange = ConflictLeft - BarLeft ' + Forms!frmSampleForecasting!DaysOFWeek_1.Width
        Else
            IsBetweenRange = ConflictRight + Forms!frmSampleForecasting!DaysOFWeek_1.Width
        End If
        
    Else
        If initalvalueleft <= ConflictLeft And InitialValueRight >= ConflictRight Then
            If Abs(ConflictLeft - InitialValueLeft) < Abs(ConflictRight - InitialValueRight) Then
                IsBetweenRange = ConflictLeft - BarLeft ' + Forms!frmSampleForecasting!DaysOFWeek_1.Width
            Else
                IsBetweenRange = ConflictRight + Forms!frmSampleForecasting!DaysOFWeek_1.Width
            End If
        End If
        IsBetweenRange = InitialValueLeft
    End If
        
        
    

End Function

You're a gentleman and a scholar :)
 

Users who are viewing this thread

Back
Top Bottom