By the way, in my opinion, the FlexCellLeft and FlexCellTop properties shouldn't ignore the RowSel and ColSel too. It make sence when the (Row,Col) cell is in the right bottom corner of a range.
Printable View
By the way, in my opinion, the FlexCellLeft and FlexCellTop properties shouldn't ignore the RowSel and ColSel too. It make sence when the (Row,Col) cell is in the right bottom corner of a range.
I would propose for new enums. E.g. FlexCellRangeTop, FlexCellRangeHeight.Quote:
Originally Posted by Nouyana
This will only make sence if someone has used this syntax:Quote:
Originally Posted by Krool
meaning that he works with only one cell. I would not add new enums. You already have 25 of them there.Code:Cell(FlexCellWidth, .Row, .Col, .RowSel, .ColSel)
Your CellAlignment property works with a whole selected range and not just with one cell like the original one. No one even noticed this.
Yes. (Let Only)Quote:
Originally Posted by Nouyana
Of course FillStyle must be FlexFillStyleRepeat.
My mistake here. The MSDN OCT2001 says:Quote:
Originally Posted by Krool
But in fact the original behavour depends on the FillStyle property too.Quote:
Returns or sets a value that determines the horizontal and vertical alignment of data within the current cell.
About BeforeEdit event again:)
How can I get (invoke?) the event with Reason = FlexEditReasonComboCueDblClick or Reason = FlexEditReasonComboCueAltUpDown?
EDITED: I figured out how to get the FlexEditReasonComboCueAltUpDown Reason. The only question is how to DblClick on a ComboCue? The FlexEditReasonComboCueClick comes faster then FlexEditReasonComboCueDblClick
WinXP SP3.
The FlexPictureAlignmentRightCenter alignment doesn't work for CheckBoxesQuote:
Originally Posted by Krool
Attachment 187832
I consider it as a feature, not a bug.
When the CellChecked = FlexDisabledTextAsCheckBox, the CheckBox is not really disabled. It only looks like disabled. Its behavour is the same as FlexTextAsCheckBox. If AllowUserEditing = True, then user can change the text, and a CheckBox changes its state.
Krool, can you, please, give an example of using the FlexDataSource property? Where can I read about creating a custom (private) data source?Quote:
Originally Posted by Krool
FWIW, here's a codebank-entry which shows how to use itQuote:
Originally Posted by Nouyana
(in conjunction with SQLite-recordsets).
https://www.vbforums.com/showthread....te-Recordsets)
In short, you need a Class, which implements the IVBFlexDataSource interface.
(e.g. cMy2DVariantArray.cls ... which then e.g. interacts in the interface-method-callbacks with an internal Variant-array).
Olaf
DblClick is for completeness and for rare cases.Quote:
Originally Posted by Nouyana
Example:
Click on a combo cue and press quickly escape key and click again.
You then have Click, cancel edit, DblClick.
Thank you, Olaf! :thumb:Quote:
Originally Posted by Schmidt
I have a couple of questions more:
1. Do we have any problem with MSBIND.DLL dependency on modern OS?
2. Should I implement only IVBFlexDataSource interface or BindTo, DeleteRow and SaveChanges are necessary too?
Wow! I did it! :bigyello:Quote:
Originally Posted by Krool
Thanks. That was indeed a bug which got fixed.Quote:
Originally Posted by Nouyana
Btw, consider using ColCheckBoxAlignment instead of CellPictureAlignment.
The default value for ColCheckBoxAlignment is 'FlexCheckBoxAlignmentUsePictureAlignment'.
Well, what is disabled is actually just the checkbox "trigger". But the text editing is not restricted on FlexTextAsCheckBox or FlexDisabledTextAsCheckBox.Quote:
Originally Posted by Nouyana
It's a good question if a cell shall be "not editable" (except by code) in such a case. I wait here for some feedback..
I do not know where is a problem here, but it's not look like a "virtual" way. I tried to load 5 million records from a MS Access database (4 fields, 301 megabytes mdb file). I did it, but the program uses more than 1,7 GB of memory. I got comparable results using the DataSource property.Quote:
Originally Posted by Krool
If I load more than 1,5 million records using FlexDataSource, the grid starts to slow down (read only) and it is almost impossible to work with it. If I work using DataSource, I have no such problems. EDITED: The problem is appear only after pressing Ctrl+Down keys, that is, after last records are reached.
Here is my program (need DAO 3.6 reference and VBFlexGrid1 control on the form):
"MMain" module:Code:Private DBName As String
Private oDB As DAO.Database
Private t_RECORDS As DAO.Recordset
Public oDataSource As cFlexDataSource
Sub Main()
DBName = App.Path & "\database.mdb"
Set oDB = DAO.OpenDatabase(DBName)
Set t_RECORDS = oDB.OpenRecordset("t_RECORDS", dbOpenSnapshot)
t_RECORDS.MoveLast
t_RECORDS.MoveFirst
Set oDataSource = New cFlexDataSource
Set oDataSource.RS = t_RECORDS
FMain.Show vbModal
HELL:
Set oDataSource = Nothing
t_RECORDS.Close
Set t_RECORDS = Nothing
oDB.Close
Set oDB = Nothing
End Sub
"FMain" form:
Code:Private Sub Form_Load()
Set VBFlexGrid1.FlexDataSource = oDataSource
End Sub
"cFlexDataSource" class:Code:Implements IVBFlexDataSource
Public RS As DAO.Recordset
Private Function IVBFlexDataSource_GetFieldCount() As Long
IVBFlexDataSource_GetFieldCount = RS.Fields.Count
End Function
Private Function IVBFlexDataSource_GetFieldName(ByVal Field As Long) As String
IVBFlexDataSource_GetFieldName = RS(Field).Name
End Function
Private Function IVBFlexDataSource_GetRecordCount() As Long
IVBFlexDataSource_GetRecordCount = RS.RecordCount
End Function
Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
ByVal Record As Long) As String
If Record < RS.RecordCount Then
With RS
.AbsolutePosition = Record
IVBFlexDataSource_GetData = .Fields(Field)
End With
Else
Exit Function
End If
End Function
Private Sub IVBFlexDataSource_SetData(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewData As String)
If Record < RS.RecordCount Then
With RS
.AbsolutePosition = Record
.Edit
.Fields(Field) = NewData
.Update
End With
End If
End Sub
For creating the database.mdb
Attachment 187841
How to use it? I've been added this routines to my cFlexDataSource class:Quote:
Originally Posted by Krool
But nothing was printed in my Immediate window.Code:Implements IVBFlexDataSource2
Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
IVBFlexDataSource2_GetFlags = FlexDataSourceToolTipText
End Function
Private Function IVBFlexDataSource2_GetToolTipText(ByVal Field As Long, _
ByVal Record As Long) As String
Debug.Print "IVBFlexDataSource2_GetToolTipText"
End Function
Private Sub IVBFlexDataSource2_SetToolTipText(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewValue As String)
Debug.Print "IVBFlexDataSource2_SetToolTipText"
End Sub
You have the ShowInfoTips property set to True ?Quote:
Originally Posted by Nouyana
I've tried both options (True and False) with no result.Quote:
Originally Posted by Krool
IVBFlexDataSource2 can be implemented in addition to a IVBFlexDataSource implementation which is set to .FlexDataSource.Quote:
Originally Posted by Nouyana
So is your IVBFlexDataSource2 impl "together" with IVBFlexDataSource?
Yes. You can see all of my testing code above (here and here) or with the project file in the "FlexDataSource.zip" attachment. The "CreatingDatabase.zip" attachment contains the code for creating the database file I use.Quote:
Originally Posted by Krool
Attachment 187844
Attachment 187845
I don't have this problem when I'm working with a small number of records. This code works fine:Quote:
Originally Posted by Nouyana
Code:Private Const MAX_ROWS = 65000
Implements IVBFlexDataSource
Implements IVBFlexDataSource2
Public RS As DAO.Recordset
'****************************************************************************
' Implements IVBFlexDataSource
'----------------------------------------------------------------------------
Private Function IVBFlexDataSource_GetFieldCount() As Long
IVBFlexDataSource_GetFieldCount = RS.Fields.Count
End Function
Private Function IVBFlexDataSource_GetFieldName(ByVal Field As Long) As String
IVBFlexDataSource_GetFieldName = RS(Field).Name
End Function
Private Function IVBFlexDataSource_GetRecordCount() As Long
IVBFlexDataSource_GetRecordCount = MAX_ROWS
End Function
Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
ByVal Record As Long) As String
Static PrevRecord As Long
If Record < MAX_ROWS Then
With RS
If (Record = PrevRecord + 1&) Then
.MoveNext
ElseIf (Record = PrevRecord - 1&) Then
.MovePrevious
Else
.MoveFirst
.Move Record
End If
IVBFlexDataSource_GetData = .Fields(Field)
End With
Else
Exit Function
End If
PrevRecord = Record
End Function
Private Sub IVBFlexDataSource_SetData(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewData As String)
Static PrevRecord As Long
If Record < MAX_ROWS Then
With RS
If (Record = PrevRecord + 1&) Then
.MoveNext
ElseIf (Record = PrevRecord - 1&) Then
.MovePrevious
Else
.MoveFirst
.Move Record
End If
.Edit
.Fields(Field) = NewData
.Update
End With
End If
PrevRecord = Record
End Sub
'****************************************************************************
' Implements IVBFlexDataSource2
'----------------------------------------------------------------------------
Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
IVBFlexDataSource2_GetFlags = FlexDataSourceToolTipText
End Function
Private Function IVBFlexDataSource2_GetToolTipText(ByVal Field As Long, _
ByVal Record As Long) As String
Debug.Print "IVBFlexDataSource2_GetToolTipText" 'Works fine!
End Function
Private Sub IVBFlexDataSource2_SetToolTipText(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewValue As String)
Debug.Print "IVBFlexDataSource2_SetToolTipText"
End Sub
Private Function IVBFlexDataSource2_GetChecked(ByVal Field As Long, _
ByVal Record As Long) As Integer
'
End Function
Private Sub IVBFlexDataSource2_SetChecked(ByVal Field As Long, _
ByVal Record As Long, _
ByVal NewValue As Integer)
'
End Sub
Probably the cause of this problem is the same as with the ToolTips.Quote:
Originally Posted by Nouyana
Finally, the question is: Should I limit the number of records by code, or "virtual mode" can do all the job for me?
Hi, Krool! Can you add the ItemData functionality to the DropDown ComboBoxes? Just an array of long integers. Something like that:
Code:With VBFlexGrid1
.ColComboMode(COL_COMBODROPDOWN) = FlexComboModeDropDown
.ColComboItems(COL_COMBODROPDOWN) = "Arnold|Bob|Charlie"
.ColComboItemsData(COL_COMBODROPDOWN) = "1|2|3"
End With
The MsgBox can be used in a error handler code in some cases. And if Enabled = False, then VBFlexGrid will hang.Quote:
Originally Posted by Krool
Can you automatically set Cancel to True if a modal window is invoked from the BeforeUserResize event?
@Nouyana, i can see your are actively testing this, do you have the same problem like i have?
I found (post 846) that if you use 2 grids in a same form in a MDI environment, you can hang IDE easily.
Send a test project on post 855.
Do you have the same problem under XP ?
No, I use the OCX version.Quote:
Originally Posted by Calcu
You shoul use this compilation utility. It allows you to work in IDE using OCX, and then compile the EXE-file using source Krool's files. But first you have to solve a few puzzles that MountainMan left in his program for some reasons:)Quote:
Originally Posted by Calcu
Thanks, i will take a look at it.
Meanwhile, i'm trying to use the code used on the uctabmdi control, that has this code:
Just Trying to figure where to get the subclass data array :-)Code:'Stop all subclassing
Private Sub Subclass_StopAll()
Dim i As Long
i = UBound(sc_aSubData()) 'Get the upper bound of the subclass data array
Do While i >= 0 'Iterate through each element
With sc_aSubData(i)
If .hWnd <> 0 Then 'If not previously Subclass_Stop'd
Call Subclass_Stop(.hWnd) 'Subclass_Stop
End If
End With
i = i - 1 'Next element
Loop
End Sub
deleted
I tried your demo. For me IVBFlexDataSource2_GetToolTipText fires always. Also at the end of the table.Quote:
Originally Posted by Nouyana
Regarding the slowleness. If I comment out the code on IVBFlexDataSource_GetData in your cFlexDataSource then the grid is fast, always. So it must be something with the .record moving of the DAO db.
Aside from potential slowdowns in DAO-Rs-navigation/caching strategies -Quote:
Originally Posted by Krool
there's one "other problem" with such "huge-sized" DataSources.
When I run similar Code with huge-sized SQLite-Rs (2.5Mio Records x 4 Columns = 10Mio Cells) - then I have:
- a mem-consumption of the SQLite-Rs of about 100MByte (about 10Byte per Rs-Cell on average)
- after the binding went through, the grid causes an additional mem-consumption of about 1GByte (~100 byte per cell)
So, there's probably a 2D-Array-Redim (for Cell-individual Formatting or something) in the Grid-Ctl -
which might be an indirect cause for "slowdowns everywhere" due to straining VBs mem-allocator (CoTaskMemAlloc) -
regarding "additional allocations, which happen after the Binding of such a huge DataSource took place".
Easiest solution perhaps is, to introduce a "Cell-based OwnerDrawing" instead of Flex-internal drawing,
to avoid these Grid-internal allocations for the formatting-infos.
We use this Cell-OwnerDrawing-mode with the vsFlexGrid, when we bind via vsFlexDataSource to "large log-files" for example.
Olaf
Can I somehow set the CellPicture to a PNG image?
I've just tested it under different OS'es. It seems, that this issue is only under WinXP.Quote:
Originally Posted by Krool
It seems so. Can you share some example of good code for IVBFlexDataSource_GetData sub for huge recordsets? Any of ADO or DAO.Quote:
Originally Posted by Krool
The right solution is to limit the number of entries to some reasonable value (~100 000) using IVBFlexDataSource_GetRecordCount and IVBFlexDataSource_GetData. But I do not know how to do it correctly.Quote:
Originally Posted by Schmidt
I'm just testing the capabilities of the Grid as well as its virtual mode.
This one works fast:
EDITED: The PrevRecord variable should not be a Static. It should be a module-level variable because of using in other subs the same way.Code:Private Function IVBFlexDataSource_GetData(ByVal Field As Long, _
ByVal Record As Long) As String
Static PrevRecord As Long
Dim delta&, i As Long
delta = Abs(Record - PrevRecord)
With RS
If (Record > PrevRecord) Then
For i = 1 To delta
.MoveNext
Next
ElseIf (Record < PrevRecord) Then
For i = 1 To delta
.MovePrevious
Next
End If
IVBFlexDataSource_GetData = .Fields(Field)
End With
PrevRecord = Record
End Function
Krool,
how to limit the number of records in IVBFlexDataSource_GetRecordCount and IVBFlexDataSource_GetData subs correctly? What if there are several hundred million records on the server in one table? We should load them in parts.
You misunderstand the approach somehow, because it's not Krools responsibility to "automatically limit the record-amount" -Quote:
Originally Posted by Nouyana
the Grid just reacts accordingly, to what amount you told him you have, in the Interface-Callback.
If you want "paged mode", then it's up to you, to limit the amount of Records
(which you select from a given Table) - via proper SQL-instructions:
- either via Where-Clause (Select <FldList> From Table Where ID > LastPagedID)
- or better, in conjunction with a Top-Clause (Select Top 10000 <FldList> From Table Where...)
Olaf
I didn't say the "automatically" word. I just asked him to help.Quote:
Originally Posted by Schmidt
Yes, something like this. The problem here will arise at the time of moving through the grig, deleting or editing records. Do you have any working example?Quote:
Originally Posted by Schmidt
The only possibility I see here in order not to make mistakes is to get the value of the Primary Key field from the VBFlexGrid. I don't really like to use this way. It would be better to somehow synchronize rows in Recordset and rows in FlexGrid.
Perhaps the main difficulty is that the grid should be loaded dynamically, that is not at the moment when you reached the last record of the current "page".
Each time you bind a Recordset (via a *generic* behaving binding-class) -Quote:
Originally Posted by Nouyana
the whole thing should "just work" (on that concrete Recordset, when the Binding-Class was implemented properly).
Since that is the case (with any given, potentially "paged" Rs),
you will just have to ensure (with your own paging-related code on the outside of the Grid),
that "the next paged Recordset" you retrieve from the DB, is "properly bound again" (via the Binding-Class).
Special care needs to be taken, in case of Delete- or Add-Operations
(where you need to "re-initiate the Binding", to reflect the now reduced or increased RecordCount properly in the Grid) -
note, that the technique shown in the SQLite-Binding assumes, that we have bound "a disconnected Rs".
I have not used DAO for the last 20 years - and don't have any working "binding-examples".Quote:
Originally Posted by Nouyana
(I primarily use SQLite, for which an example exists).
Olaf
I mean "paged" exampleQuote:
Originally Posted by Schmidt
Based on SQLite-Rs?Quote:
Originally Posted by Nouyana
As said - this would come out as a "quite trivial extension" on top of the "non-paged SQLite-Rs-Demo".
Because only a few "page-navigation-buttons" (or a Pages DropDown-List) would have to be added,
which in their Click-Events ensure: "a new selected, record-limited Rs via SQL", which in turn will be bound again -
and then will behave "in the same way as the non-paged Demo is showing already"
(for the given Rs, currently in the binding-context).
Olaf
So, just do it! Let us see your "trivial" approach.Quote:
Originally Posted by Schmidt
I'd rather "you show me" (preferrably in a new thread of yours, initiated in the main-forum),Quote:
Originally Posted by Nouyana
what you made out of my hints, regarding the:
"extension-code needed, to convert an existing non-paged SQLite-Rs-Demo, into a paged one". ;)
(starting with your initial attempt, in a Zip).
Olaf
Ok. In a couple of days.Quote:
Originally Posted by Schmidt
Yes, a row with 4 columns consumes per cell 64 bytes and 20 bytes for a SafeArray on that row.Quote:
Originally Posted by Schmidt
So, 2,500,000 rows * ((4 * 64) + 20) = 690,000,000 bytes (~658 MB)
I will soon optimize the TCELL structure to consume only 52 bytes. This safes in this example 100 MB.
However, the only solution would be to "omit custom cell formatting". Which can be done relatively easy in the VBFlexGrid. (not redim TCOLS structure)
Question is just in what form. Should a FlexDataSource "automatically" "omit custom cell formatting" ? Because why ownerdraw all when we get at least text and can draw based on columns / row formatting ?
Can we combine both: the automatic and the manual ways? The default would be "omit, until using".Quote:
Originally Posted by Krool
When I once was trying to enhance to vsFlexGrid to hold even more variables per cell I didn't use a matrix for the whole grid, but a linked list holding only the elements which actually used at least one of the extended properties.Quote:
Originally Posted by Krool
Using a dictionary with [row & "*" & col] as the key to access the index of the linked list.
I tested VBFlexGrid in depth a few years ago and found that one of the big differences between it and Fapoint-Spread is performance and capacity. Fapoint-Spread can load millions of rows (dozens of columns per row) of data, but the performance is still very good. However, VBFlexGrid, when loading huge data sets, not only consumes more memory (and causes the computer to freeze), but also performs orders of magnitude slower. IMO, an important reason for this is that VBFlexGrid's data structures (such as TCOLS) are not optimized.Quote:
Originally Posted by Krool
Can you explain, what does it mean: "keep the cell texts non-virtual". For me it looks like literally "NoData":Quote:
Originally Posted by Krool
Attachment 187871
In the cFlexDataSource class:
in the form:Code:Private Function IVBFlexDataSource2_GetFlags() As FlexDataSourceFlags
Dim RetVal As FlexDataSourceFlags
If bKeepCellTextsNonVirtual Then
RetVal = RetVal Or FlexDataSourceNoData
Else
RetVal = RetVal And Not FlexDataSourceNoData
End If
IVBFlexDataSource2_GetFlags = RetVal
End Function
Code:Private Sub chkKeepCellTextsNonVirtual_Click()
oDataSource.KeepCellTextsNonVirtual = CBool(chkKeepCellTextsNonVirtual)
Set VBFlexGrid1.FlexDataSource = oDataSource
End Sub
Update released.
Memory consumption of TCELL struct reduced from 64 bytes to 52 bytes.
This means the normal .TextMatrix must be used to populate the cell texts. GetData/SetData is not used on IVBFlexDataSource.Quote:
Originally Posted by Nouyana
Oh, I understand. I can use virtual checkboxes and virtual ToolTips, but not GetData/SetData. Thank you!Quote:
Originally Posted by Krool
Too much memory consumption when using the FlexDataSourceUnboundFixedColumns flag.
Attachment 187876
Better results with v.1.6.5. The previous GIF was with v.1.6.0.Quote:
Originally Posted by Krool
Attachment 187879
It feels like that "empty column" have strings set? (As can be seen by the >)Quote:
Originally Posted by Nouyana
It's very difficult to further reduce TCELL structure.. too many features ;)
Also introducing a "simple mode" is not very easy to achieve w/o usage of conditional compilation. Beside the fact of the additional overhead in code. (Two structures side by side "if else" which to use)
So I keep things as there are...
Info: the grid can't use a simple 2D array. TCOLS has it's sense..
It's optimized for sorting rows.
Yes, but the only one cell.Quote:
Originally Posted by Krool
By the way, what value it would be better to use when I leave the row? "" or vbNullString or what?
Let it be a conditional compilation. Why not?Quote:
Originally Posted by Krool
Let's count a little. In v.1.6.0 it was 614 MB per 1 fixed column with 1 750 000 rows. In v.1.6.5 the same variable is 496 MB. So it was reduced 614-496=118 MB or 70 bytes per cell. It was not only about TCELL structure. And now we have another reason too, I'm sure. You have changed something else.Quote:
Originally Posted by Krool
PS I'm an economist:)