VBFlexGrid Control (Replacement of the MSFlexGrid control)

Quote:

---

Originally Posted by Nouyana

Ok. Only two questions left. What is the difference between:
1. FlexWordBreakEndEllipsis and FlexWordBreakWordEllipsis.
2. FlexSingleLineEndEllipsis and FlexSingleLineWordEllipsis

Attachment 190455

---

The difference is that the one use DT_END_ELLIPSIS and the other DT_WORD_ELLIPSIS in the DrawText API.

Quote:

---

Originally Posted by Krool

On second thought. The gridline is drawn "between" cells. So 1 physical cell does not draw the entire gridline for itself. Only half, the other half is drawn by the left and top neighbor cell.
So, I guess your request does not really "fit" for VBFlexGrid and I am afraid to reject your request until a good idea approaches.

I checked with the vsFlexGrid and the CellBorder function is not respected by anything. (focus rect, combo button etc.) so it's just a "rectangle drawn over".

---

Understood. I hope you won't mind me tossing ideas from time-to-time to see if it resonates...

One thought given the order of things was to consider this in a manner similar to the FlexComboCue*. I haven't dug into your code, but I assume that you are rendering the DropDown and Button images after the grids and cell BGs are established. Could this be something like a FlexCellCueBorder, where at the same time of rendering the Combo images a border could be drawn around the grid using a defined GridColorAlt?

Quote:

---

Originally Posted by Krool

Nouyana,

thanks however for bringing this up. It seems there is a bug.
If ComboItems is "" then a "blank" item is inserted.

So I will change the behavior in next release that if ComboItems is vbNullString (StrPtr() = 0) then really no item is inserted. However, if ComboItems is "" then a "blank" item shall still be inserted.

---

Is it still in your roadmap?

Quote:

---

Originally Posted by Nouyana

Is it still in your roadmap?

---

That's already implemented.

Quote:

---

Originally Posted by Krool

That's already implemented.

---

Yes, I can see now that ComboListCount = 0 when ComboItems is set to vbNullString. But what I noticed is an "empty" drop-down list which is still there, unlike the standard controls. Is it by design?

Code:

---

Private Sub VBFlexGrid1_ComboDropDown()
  With VBFlexGrid1
      Debug.Print .ComboListCount    ' Returns 0.
  End With
End Sub

Private Sub Command1_Click()
  With VBFlexGrid1
      .CellComboCue = FlexComboCueDropDown
      .ComboMode = FlexComboModeDropDown
'      .ComboItems = vbNullString    ' I can see an "empty" drop-down list
  End With
  With cmbCombo1    ' VB ComboBox
      .AddItem "Item"
      .RemoveItem 0                  ' There is no drop-down list.
  End With
  With cmbComboBoxW1 ' CCR ComboBox
      .AddItem "Item"
      .RemoveItem 0                  ' There is no drop-down list.
  End With
End Sub

---

Quote:

---

Originally Posted by Krool

20-Oct-2023
- Included enum FlexAlignmentGeneralTop/FlexAlignmentGeneralCenter/FlexAlignmentGeneralBottom.
The enum FlexAlignmentGeneral keeps valid and is the default column alignment value.

---

Am I understand right that there is no any difference between the FlexAlignmentGeneralCenter and FlexAlignmentGeneral constants?

And, by the way, Krool, may be you didn't know it because it's a bit undocumented. So, just as advice.

Code:

---

Private Enum MyEnum
  [_enmFirst] = -1 ' You may keep the (-1) value here.
                    ' This is just an invisible constant.
  enm1 = 0
  enm2 = 1
  enm3 = 2        ' You may easy add a new constant here, but
  [_enmLast] = 2  ' don't forget to update the last (invisible) constant.
End Enum

Public Property Let MyProperty(ByVal Value As MyEnum)
  Select Case Value
  Case [_enmFirst] To [_enmLast] ' You may use it instead of full enumeration.
  Case Else
      Err.Raise Number:=30005, Description:="Invalid Alignment value"
  End Select
End Property

---

Quote:

---

Originally Posted by Nouyana

Yes, I can see now that ComboListCount = 0 when ComboItems is set to vbNullString. But what I noticed is an "empty" drop-down list which is still there, unlike the standard controls. Is it by design?

Code:

---

Private Sub VBFlexGrid1_ComboDropDown()
  With VBFlexGrid1
      Debug.Print .ComboListCount    ' Returns 0.
  End With
End Sub

Private Sub Command1_Click()
  With VBFlexGrid1
      .CellComboCue = FlexComboCueDropDown
      .ComboMode = FlexComboModeDropDown
'      .ComboItems = vbNullString    ' I can see an "empty" drop-down list
  End With
  With cmbCombo1    ' VB ComboBox
      .AddItem "Item"
      .RemoveItem 0                  ' There is no drop-down list.
  End With
  With cmbComboBoxW1 ' CCR ComboBox
      .AddItem "Item"
      .RemoveItem 0                  ' There is no drop-down list.
  End With
End Sub

---

---

I don't understand. In all cases the drop-down list has no items. What's exactly the issue ?
What you mean by "no drop-down list" ? Of course there is a list shown when you push the dropdown button. Also in VB ComboBox.

Quote:

---

Originally Posted by Krool

Of course there is a list shown when you push the dropdown button. Also in VB ComboBox.

---

Only in the VBCCR ComboBox

Code:

---

Private Sub cmdAddItems_Click()
  With VBFlexGrid1
      .CellComboCue = FlexComboCueDropDown
      .ComboMode = FlexComboModeDropDown
      .ComboItems = "Arnold|Bob|Charlie|David"
  End With
  With cmbCombo1    ' VB ComboBox, Style = 2 (Dropdown list)
      .AddItem "Arnold"
      .AddItem "Bob"
  End With
  With cmbComboBoxW1 ' CCR ComboBox, Style = 2 (Dropdown list)
      .AddItem "Arnold"
      .AddItem "Bob"
  End With
End Sub

Private Sub cmdRemoveItems_Click()
  VBFlexGrid1.ComboItems = vbNullString
  cmbCombo1.Clear
  cmbComboBoxW1.Clear
End Sub

---

Attachment 190487

I don't understand really how it works, but sometimes I can see the drop-down list in both comboboxes and sometimes I can't...

Here I use the RemoveItem instead of Clear.

Code:

---

Private Sub cmdAddItems_Click()
  With VBFlexGrid1
      .CellComboCue = FlexComboCueDropDown
      .ComboMode = FlexComboModeDropDown
      .ComboItems = "Arnold|Bob|Charlie|David"
  End With
  With cmbCombo1    ' VB ComboBox, Style = 2 (Dropdown list)
      .AddItem "Arnold"
  End With
  With cmbComboBoxW1 ' CCR ComboBox, Style = 2 (Dropdown list)
      .AddItem "Arnold"
  End With
End Sub

Private Sub cmdRemoveItems_Click()
  VBFlexGrid1.ComboItems = vbNullString
  cmbCombo1.RemoveItem 0
  cmbComboBoxW1.RemoveItem 0
End Sub

---

Attachment 190488

Quote:

---

Originally Posted by Krool

Set CellChecked to FlexTextAsCheckBox.



Process the EditKeyPress event and set KeyChar to 0 fur unwanted chars.

---

Code:

---

Private Sub Form_Load()
    Set Me.VBFlexGrid1.DataSource = GetRs("SELECT  * FROM tblDataTaye;")
    With Me.VBFlexGrid1
      .CellChecked = FlexTextAsCheckBox
       
    End With
   
    Set Me.VSFlexGrid1.DataSource = GetRs("SELECT  * FROM tblDataTaye;")

End Sub

---

Attachment 190489

Hello Krool! I've set the ". CellChecked = FlexTextAsCheckBox", but you can't make the column of a Boolean field appear as the corresponding checkbox. The above is my code, please guide me on how to modify the code so that these Boolean fields (Boolean, Enabled, Allow, Delete) can be displayed according to the corresponding checkboxes of the field values. If you're convenient, add a demonstration of how to use the Boolean field display checkbox to this VBFlexGridDemo. Thank you very much!

Attachment 190490

Quote:

---

Originally Posted by smileyoufu

Code:

---

Private Sub Form_Load()
    Set Me.VBFlexGrid1.DataSource = GetRs("SELECT  * FROM tblDataTaye;")
    With Me.VBFlexGrid1
      .CellChecked = FlexTextAsCheckBox
       
    End With
   
    Set Me.VSFlexGrid1.DataSource = GetRs("SELECT  * FROM tblDataTaye;")

End Sub

---

Attachment 190489

Hello Krool! I've set the ". CellChecked = FlexTextAsCheckBox", but you can't make the column of a Boolean field appear as the corresponding checkbox. The above is my code, please guide me on how to modify the code so that these Boolean fields (Boolean, Enabled, Allow, Delete) can be displayed according to the corresponding checkboxes of the field values. If you're convenient, add a demonstration of how to use the Boolean field display checkbox to this VBFlexGridDemo. Thank you very much!

Attachment 190490

---

Code:

---

Private Sub Form_Load()
    Set Me.VBFlexGrid1.DataSource = GetRs("SELECT  * FROM tblDataTaye;")
    With Me.VBFlexGrid1
        .FillStyle = FlexFillStyleRepeat
        .Cell(FlexCellChecked, .FixedRows, 2, .Rows - 1, 5) = FlexTextAsCheckBox
    End With

    Set Me.VSFlexGrid1.DataSource = GetRs("SELECT  * FROM tblDataTaye;")
End Sub

---

Nouyana,
I can't replicate the "no drop-down list" thing. However, I fixed now a bug that the drop-down list is not clickable anymore when it's empty. So that the list will not be closed anymore and mouse capture is released.
The same behavior as a ComboBox..

Quote:

---

Originally Posted by Krool

I fixed now a bug that the drop-down list is not clickable anymore when it's empty.

---

Looks better. Thank you, Krool.

Update released.

Included the DragRow/DragCol function used to rearrange rows/cols by dragging the specified row/col with the mouse to a new position.
To note is that this is NOT an OLE drag/drop operation and the function will enter a modal message loop to just return the new moved position. (like vsFlexGrid)

The demo project is updated to use this new internal function.
Example code:

Code:

---

Private Sub VBFlexGrid1_BeforeMouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single, Cancel As Boolean)
With VBFlexGrid1
If Button = vbLeftButton And (Shift And vbAltMask) = vbAltMask Then
    .HitTest X, Y
    If .HitResult = FlexHitResultCell Then
        If .HitCol < .FixedCols And .HitRow >= .FixedRows Then
            VBFlexGrid1.RowPosition(.HitRow) = VBFlexGrid1.DragRow(.HitRow)
            Cancel = True
        ElseIf .HitCol >= .FixedCols And .HitRow < .FixedRows Then
            VBFlexGrid1.ColPosition(.HitCol) = VBFlexGrid1.DragCol(.HitCol)
            Cancel = True
        End If
    End If
End If
End With
End Sub

---

The OCX VBFLXGRD17 was also updated. The internal type lib version is now 1.6.

Code:

---

Object={2DA70529-3366-414A-B408-46083BCD481B}#1.6#0; VBFLXGRD17.OCX

---

Quote:

---

Originally Posted by Krool

Included the DragRow/DragCol function

---

Great job, Krool! You make things easier!

I don't know why, but I can't unzip your new attachments. did you change the archiver? I use "7-zip" 24.01 (2024-01-31). I had to download files from Github.

Maybe something is wrong with this forum. The "Reply with quote" button doesn't work for me today too...

That seems to be a forum issue about the invalid zip file..

EDIT: zip file working again

Hi Krool,
One question, with the combbox insert in a cell, dropdown is ok, leave the cell, it seems the combbox item already transfered to the cell, but when I use msgbox to read the cell text, it shows nothing! any idea or method about it?
Thanks.
John

PS: Sorry I use a timer to read the cell text and the above issue occured. Solved the problem.

JT870,
please provide some code. Otherwise it's hard to imagine ..
Maybe you MsgBox the .EditText which of course is reset once editing is finished.

Hi again!

When one column is a combobox column, and the list inside is wider than the column... it's possible to adjust the column before assigning one value?

i mean:

Before assigning a value:

Attachment 190579

Selecting it:

Attachment 190580

Selected:

Attachment 190581

It's possible to autoadjust the width of the column "before" selecting anyhing ?

Quote:

---

Originally Posted by Calcu

It's possible to autoadjust the width of the column "before" selecting anyhing ?

---

Yes, you may use the ColWidth property and the TextWidth function in the ComboBeforeDropDown or ComboCloseUp events.

https://www.vbforums.com/images/ieimages/2024/02/15.gif

Edited:
The code below is edited in accordance with the post #1283 by Krool.

Code:

---

Const COL_COMBO = 1
Private nOldColWidth As Long, bColWidthIsChanged As Boolean

Private Sub Command3_Click()
  With VBFlexGrid1
      .ColComboCue(COL_COMBO) = FlexComboCueDropDown
      .ColComboMode(COL_COMBO) = FlexComboModeDropDown
      .ColComboItems(COL_COMBO) = "Arnold|Bob|Charlie|David"
  End With
End Sub

Private Sub VBFlexGrid1_ComboBeforeDropDown( _
        ByVal Reason As VBFLXGRD17.FlexComboDropDownReasonConstants, _
              Cancel As Boolean)

  Dim nTextWidth  As Long, nNewTextWidth As Long
  Dim aComboItems As Variant, i As Long

  With VBFlexGrid1
      If (.EditCol = COL_COMBO) And (LenB(.ColComboItems(COL_COMBO))) Then
        aComboItems = Split(.ColComboItems(COL_COMBO), "|")
        For i = LBound(aComboItems) To UBound(aComboItems)
            nNewTextWidth = .TextWidth(aComboItems(i), .EditRow, .EditCol)
            If nTextWidth < nNewTextWidth Then nTextWidth = nNewTextWidth
        Next
        nOldColWidth = .ColWidth(COL_COMBO)
        .ColWidth(COL_COMBO) = nTextWidth \ 15 * 15
        bColWidthIsChanged = True
      End If
  End With
End Sub

Private Sub VBFlexGrid1_ComboCloseUp()
  If bColWidthIsChanged Then
      VBFlexGrid1.ColWidth(COL_COMBO) = nOldColWidth
      bColWidthIsChanged = False
  End If
End Sub

---

But this way the combo with is not changing before opening it, isn't it?

Add another item at the combo, for example: 'this is a long entry for combo'

can you see the entire item when you open the combo ?

Quote:

---

Originally Posted by Nouyana

The only problem is how to identify current drop-down column. In the code below I use the Col property (If .Col = COL_COMBO Then ... ), but this is a bad idea.

---

Instead of .Col use the .EditCol property. So just apply .EditRow and .EditCol into the .TextWidth function. Thanks for your assistance.

Quote:

---

Originally Posted by Calcu

But this way the combo width is not changing before opening it, isn't it?

---

Yes.

Quote:

---

Originally Posted by Calcu

Add another item at the combo, for example: 'this is a long entry for combo'
can you see the entire item when you open the combo ?

---

No. Maybe Krool can improve it.

You can also enlarge the drop-down list upon EditSetupWindow event via SetWindowPos (pass SWP_NOMOVE SWP_NOACTIVATE etc.)
Then you see the item while list is dropped down. After the edit you may run an .AutoSize.

Hi Krool,

I noticed that clicking the scrollbar of a grid doesn't set the focus to it, and thus doesn't trigger a lost focus event on a control that was selected before. Is this by design or a bug?

Regards,
Erwin

Quote:

---

Originally Posted by Erwin69

Hi Krool,

I noticed that clicking the scrollbar of a grid doesn't set the focus to it, and thus doesn't trigger a lost focus event on a control that was selected before. Is this by design or a bug?

Regards,
Erwin

---

That's by design. Why it should be different?

Thanks for the answers!

Will try the editsetupWindow.


Another question, what way is faster to find "a lot of items"? :-)

Let me explain.


I have 2 grids, one has 38566 rows, the other one 14957.

i want to cross information between them, just check if one value exists on the other grid (in both ways).

Actually i'm using:

Code:

---


For Linea = 1 To GR1.Rows - 1

        Linea2 = GR2.FindItem(GR1.TextMatrix(Linea, GR1.ColIndex("codifica")), 1, GR2.ColIndex("referencia"), FlexFindMatchExact, False, True, False, FlexFindDirectionDown)

        If Linea2 >= 1 Then
            GR1.TextMatrix(Linea, GR1.ColIndex("costeenaeu")) = GR2.TextMatrix(Linea2, GR2.ColIndex("costesumado"))
        Else
            GR1.TextMatrix(Linea, GR1.ColIndex("costeenaeu")) = 0
        End If

      DoEvents

Next Linea

---

it's working fine, but slow, the cells checked are text cells.

I sorted the results first, in order to be faster, but is still slow.

Any idea how to do this faster ?

Quote:

---

Originally Posted by Calcu

Thanks for the answers!

Will try the editsetupWindow.


Another question, what way is faster to find "a lot of items"? :-)

Let me explain.


I have 2 grids, one has 38566 rows, the other one 14957.

i want to cross information between them, just check if one value exists on the other grid (in both ways).

Actually i'm using:

Code:

---


For Linea = 1 To GR1.Rows - 1

        Linea2 = GR2.FindItem(GR1.TextMatrix(Linea, GR1.ColIndex("codifica")), 1, GR2.ColIndex("referencia"), FlexFindMatchExact, False, True, False, FlexFindDirectionDown)

        If Linea2 >= 1 Then
            GR1.TextMatrix(Linea, GR1.ColIndex("costeenaeu")) = GR2.TextMatrix(Linea2, GR2.ColIndex("costesumado"))
        Else
            GR1.TextMatrix(Linea, GR1.ColIndex("costeenaeu")) = 0
        End If

      DoEvents

Next Linea

---

it's working fine, but slow, the cells checked are text cells.

I sorted the results first, in order to be faster, but is still slow.

Any idea how to do this faster ?

---

Safe the various .ColIndex() results in a variable before the loop.

Quote:

---

Originally Posted by Krool

You can also enlarge the drop-down list upon EditSetupWindow event via SetWindowPos (pass SWP_NOMOVE SWP_NOACTIVATE etc.)
Then you see the item while list is dropped down. After the edit you may run an .AutoSize.

fethiye tours

---

Thanks for information

Quote:

---

Originally Posted by Calcu

Any idea how to do this faster ?

---

1. Use If Linea2 > 0 instead of If Linea2 >= 1
2. Use If Linea mod 1000 = 0 Then DoEvents instead of just DoEvents
3. Use With GR1 for the whole loop
4. TextArray should be faster then TextMatrix
5. Without "End If" will be a bit faster (If Linea2 > 0 Then ... Else ...)
6. Use correct datatypes. I mean:
GR1.TextMatrix(...) = "0"
If Linea2 > 0& Then
etc.
7. Krool's advice https://www.vbforums.com/images/ieimages/2024/03/1.gif

Thanks Nouyana !

Trying it.

Quote:

---

Originally Posted by Calcu

it's working fine, but slow, the cells checked are text cells.

I sorted the results first, in order to be faster, but is still slow.

Any idea how to do this faster ?

---

If you really want to make it fly, you can use a fast, hash-based lookup-container (e.g. a Dictionary):

Code:

---

  With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare ' or vbBinaryCompare if case-sensitivity is needed
   
    For Linea = 1 To GR2.Rows - 1 'pre-add the Key/Value-pairs into the Dictionary from Grid 2 in a pre-loop
        .Add GR2.TextMatrix(Linea, GR2.ColIndex("referencia")), Linea
    Next Linea
   
    For Linea = 1 To GR1.Rows - 1

        Linea2 = .Item(GR1.TextMatrix(Linea, GR1.ColIndex("codifica"))) 'superfast Lookup of Gr2-LineNrs via Gr1-Keys
       
        If Linea2 >= 1 Then
            GR1.TextMatrix(Linea, GR1.ColIndex("costeenaeu")) = GR2.TextMatrix(Linea2, GR2.ColIndex("costesumado"))
        Else
            GR1.TextMatrix(Linea, GR1.ColIndex("costeenaeu")) = 0
        End If
 
    Next Linea
  End With

---

Krools advice with the pre-buffering of Col-Indexes still applies -
but the above might already be entirely sufficient, to achieve a significant speedup.

Olaf

Thanks!

Trying it too, i was changing everything to work with temporal MySQL tables, because it seems they were faster, but I will try this too.

THANKS!

It's incredible fast!, i never used a dictionary... something new learned today!

thanks again

Public Function FlexFillFromRs(Rs As ADODB.Recordset, _
Flex As MSFlexGrid)

Can it bind the record machine of the database and support automatic updating?

like set datagrid1.datasource=recordset1

Quote:

---

Originally Posted by Krool

Included enum FlexDirectionAfterReturnEdit/FlexEditReasonReturn.

---

Krool, can you add an Excel-style return behaviour?

https://www.vbforums.com/images/ieimages/2024/03/2.gif

Quote:

---

Originally Posted by Krool

07-Nov-2023
- Included the ComboButtonNonClientWidth/ComboButtonNonClientHeight property.
- Exposed the GetGridLineOffsets method which retrieves the grid line offsets between cells. (optional row/col subscripts)
The internal method got renamed to GetGridLineOffsetsStruct.

---

Am I understand right that this equality is always correct?

ComboButtonWidth - ComboButtonClientWidth = ComboButtonNonClientWidth = ComboButtonNonClientHeight

Krool, can you somehow explain me where is the ClientWidth/Height, NonClientWidth/Height and GridLineOffsets here?

ComboButtonNonClientWidth = 90 (6px)
ComboButtonNonClientHeight = 90 (6px)
ComboButtonClientWidth = 195 (13px)

GetGridLineOffsets:
Left = 0
Top = 0
Right = 15 (1px)
Bottom = 15 (1px)

https://www.vbforums.com/images/ieimages/2024/03/4.png

Code:

---

Private Sub Command1_Click()
  Dim Picture As IPictureDisp
  Dim nLeft&, nRight&, nTop&, nBottom As Long
  With VBFlexGrid1
      .Cell(FlexCellComboCue, 1, 1) = FlexComboCueButton
      Debug.Print .ComboButtonNonClientWidth
      Debug.Print .ComboButtonNonClientHeight
      Debug.Print .ComboButtonClientWidth
      Call .GetGridLineOffsets(nLeft, nTop, nRight, nBottom)
      Debug.Print nLeft, nTop, nRight, nBottom
      Set Picture1.Picture = LoadPicture(MY_ICON)
      Set Picture = Picture1.Picture
      Set .ColComboButtonPicture(1) = Picture
      .ColComboButtonWidth(1) = .ComboButtonNonClientWidth + _
        Me.ScaleX(Picture.Width, vbHimetric, vbTwips)
  End With
End Sub

---

Quote:

---

Originally Posted by Nouyana

Am I understand right that this equality is always correct?

ComboButtonWidth - ComboButtonClientWidth = ComboButtonNonClientWidth = ComboButtonNonClientHeight

---

The below is correct:
ComboButtonWidth - ComboButtonClientWidth = ComboButtonNonClientWidth

However, NonClientWidth can in theory differ from the NonClientHeight. I have no control about the non-client metrics, it's MS.

Quote:

---

Originally Posted by softv

Great, krool. Some weeks back, when I needed this requirement, I stumbled upon our great Olaf's solution (cFlexSQLiteDS) here - https://www.vbforums.com/showthread....=1#post5514140 - it was very much for (y)our fabulous VBFlexGrid only. Olaf's readymade solution worked like a charm, as always. Its great now to see this functionality inbuilt in VBFlexGrid itself. I am yet to get time to download the latest VBFlexGrid and see what methodology you have adopted, to accomplish this 'CopyFromRecordSet' functionaltiy. The features you have specified for the CopyFromRecordSet function look wonderful. Thanks a TON. Looking forward to avail them soon, some time in the coming days.

Always remaining in gratitude to the wisdom of you, Olaf, LaVolpe, et al.

Kind Regards.

---

Dear Krool,

Today, I got time to check out the recently added functionality of "CopyFromRecordset". Thanks a TON!

Using dear Olaf's RC6, I could make it (CopyFromRecordset) to work when using it as follows. Hope this is the direct/shortest/fastest/right way, if I use "CopyFromRecordset" to fetch records from a SQLite database. If there is a better way, kindly let me know the same.
--

Quote:

---

Set Rs = Cnn.OpenRecordset("Select * from Table1")
cnt =VBFlexGrid1.CopyFromRecordset(Rs.GetADORsFromContent)

---

--

I take this opportunity to once again thank you in TONS for all your splendid controls. God Bless you! God Bless all!

Kind Regards.

Dear Krool,

1. Is it possible to give a functionality by which I can display the ScrollTipText even when thumb is not moved manually but otherwise? (for instance, when user navigates down the list of rows using arrow keys). If this functionality is already available, kindly let me know how to achieve it.

2. Is it possible to include a property which says that ScrollEvent will be generated even when there are no more rows to show? And, when it is set to True, ScrollEvent should get generated when the scroll box's bottom or top arrow is clicked (even when there are no more rows to show). I just felt that this would further simplify one's task of automatic/dynamic loading of further records when there are no more records to show in the current set of records loaded in the grid.

Thanks a TON.

Kind Regards.

Quote:

---

Originally Posted by softv

Using dear Olaf's RC6, I could make it (CopyFromRecordset) to work when using it as follows. Hope this is the direct/shortest/fastest/right way, if I use "CopyFromRecordset" to fetch records from a SQLite database. If there is a better way, kindly let me know the same.

---

Try to use the FlexDataSource property.

Example

Quote:

---

Originally Posted by Krool

That's by design. Why it should be different?

---

Well, I figured the scrollbar is part of the grid-control, hence there is change of focus from one control to the other. In my specific situation, the focus is on a textbox or combobox, which would have to be repositioned or made invisible when the user starts scrolling the grid.

It's not a showstopper for me, and there is probably a way to work around it, but I was just wondering.

Quote:

---

Originally Posted by Nouyana

Try to use the FlexDataSource property.

Example

---

Dear Nouyana,

Thanks a TON. I really do appreciate such immediate interest and an example program (that too, created afresh on 10-March-2024!!!) with header&inline documentations. Thank you so much for the interest shown. Really.

Initially, i could not run the project. But, I quickly realised that I need to download and give reference to Krool's VBSQLite12.Dll.

Since I am using RC6, I think I need to set Const RECORDSET_TYPE = "RC6" and use RC6.cGlobal. This part I will try later, when I get time. But, thank you once again for creating an example afresh, which it seems like you may update in the future##, as and when you get time.
(##) as you have given a release number of '0.2 aplha'

By the way, Krool, I was not aware of your sqlite wrapper at all, all these days. Thanks a TON for this contribution too, for the society.

Kind Regards.

Dear Krool,

A property like 'RowsVisible' has been extended to cover 'FlexVisibilityCompleteOnly' and FlexVisibilityPartialOK.

Based on properties like above, I have the following request. In case my request is incorrect (one way or the other) at the fundamental level itself, kindly bear with me and ignore my request.
--
I humbly feel that if 'Rows' property is extended to cover 'All', 'SansFixedFrozenHidden', 'SansFixed', 'SansFrozen', 'SansHidden', 'SansFixedFrozen', 'SansFixedHidden', 'SansFrozenHidden' and any other possibilities you can think of, that would be much helpful. Likewise, Cols property too, of course.

If extending Rows&Cols properties will mean that it will not be compatible with MSFlexGrid and hence cannot be done, then kindly consider having any other property (say 'RowsNeeded') which covers 'All', 'SansFixedFrozenHidden', 'SansFixed', 'SansFrozen', 'SansHidden', etc.
--

By the way, if I have missed any property or function which already provides information such as above straightforwardly, kindly bear with me and let me know the same.

Kind Regards.

Why do you need them? They are simple additions and subtractions of the current available properties

Quote:

---

Originally Posted by Arnoutdv

They are simple additions and subtractions of the current available properties

---

Yes. You are totally right, of course. But, as requested by me earlier, I felt that it would be "straightforward" if such info can be accessed directly as a "property" itself [preferably covered by .Rows() property itself] instead of by any other means and it is "part of Krool's control itself", by default. Personally, in my humble opinion, I felt that it makes it all easier and simpler for me since rows and cols info are accessed frequently by me and I use Krool's VBFlexGrid in more than one project of mine.

Kind Regards.

I use the vsFlexGrid and always use basic coding statements like:

Code:

---

For lRow = .FixedRows To .Rows - 1
'
NofActiveCols = .Cols - .FrozenCols - .FixedCols

---

Update released.

Included the LookupConv function which returns a converted lookup string of the current or a specified column.
Use either FlexLookupConvValue or FlexLookupConvKey. (new enum)
If no conversion occured a vbNullString (StrPtr() = 0) is returned.

This can be helpful in a paste operation to convert to or from an associated value.
For edit input operation this back and forth lookup conversion is done automatically.

The OCX VBFLXGRD17 was also updated. The internal type lib version is now 1.7.

Code:

---

Object={2DA70529-3366-414A-B408-46083BCD481B}#1.7#0; VBFLXGRD17.OCX

---

Quote:

---

Originally Posted by Krool

Update released.

Included the CopyFromRecordset function which copies the contents of an ADO or DAO Recordset onto the flex grid.
It works like the Excel function which returns the number of fetched records and copies from the current position to EOF.
The number of maximum rows and columns can be specified.
If omitted it is maximum remaining rows and columns that the grid can receive relative to the destination starting point.
The grid will not grow and it just ignores the overflow of rows and columns. (like Excel)
The destination row/col can be specified where the pasting onto the flex grid starts.
If omitted it is the first non-fixed row and first non-fixed column.

---

I think this is a very unpredictable and memory-intensive function.

1. The first question is why don't you limit the number of rows for calling the SetCellText? You limit cols, but not rows:

Code:

---

If (Col + (UBoundCols - LBoundCols)) > (Cols - 1) Then UBoundCols = LBoundCols + (Cols - 1)

---

2. The second one. In my opinion the function should clean the previous inserted records after the last row of currently inserted records if the EOF is reached.

3. I would prefer the MoveNext method instead of GetRows because of low memory consumption.

4. I have not tested it, but i think you may get a type mismatch error here if the ArrRows will contain an object:

Code:

---

Call SetCellText((iRow + (0 - LBoundRows)) + Row, (iCol + (0 - LBoundCols)) + Col, (ArrRows(iCol, iRow)))

---

Quote:

---

Originally Posted by Nouyana

I think this is a very unpredictable and memory-intensive function.

1. The first question is why don't you limit the number of rows for calling the SetCellText? You limit cols, but not rows:

Code:

---

If (Col + (UBoundCols - LBoundCols)) > (Cols - 1) Then UBoundCols = LBoundCols + (Cols - 1)

---

2. The second one. In my opinion the function should clean the previous inserted records after the last row of currently inserted records if the EOF is reached.

3. I would prefer the MoveNext method instead of GetRows because of low memory consumption.

4. I have not tested it, but i think you may get a type mismatch error here if the ArrRows will contain an object:

Code:

---

Call SetCellText((iRow + (0 - LBoundRows)) + Row, (iCol + (0 - LBoundCols)) + Col, (ArrRows(iCol, iRow)))

---

---

1.
I don't need to limit UBoundRows because it's limited already in Data.GetRows(Rows). (If Rows > (PropRows - Row) Then Rows = PropRows - Row)

2.
My intention was to imitate excel in this regard and excel's CopyFromRecordset does also no cleanup. You may just call .Clear prior to .CopyFromRecordset.
And normally you re-adjust the number of rows to the .RecordCount of the recordset. Thus any left over will be cut off anyway.

3.
Yes maybe. But GetRows is faster as it fetches everything in one swoop.

4.
Yes could be. But what sense does it make to query for an object and feed a grid ? You may convert it in the query itself to a text format or so..

Quote:

---

Originally Posted by Krool

3.
Yes maybe. But GetRows is faster as it fetches everything in one swoop.

---

Try to use the undocumented Collect property. It's 20% faster. Works with both - ADO and DAO. Or you may use GetRows getting by 10 rows step by step.

Quote:

---

Originally Posted by Krool

4.
Yes could be. But what sense does it make to query for an object and feed a grid ? You may convert it in the query itself to a text format or so..

---

You may don't now what is in the table. Maybe you just want to SELECT * FROM unknown_table. If it's an object then you can insert the "object" word. In the Excel nothing is inserted from fields with objects. But with no errors of course.

In addition, I think that cutting rows and cols should be optional. Say, you have an dbOpenForwardOnly DAO recordset (which consumes low memory) and therefore you cannot determine the number of records, but you want to get them all.

Ultimately, the CopyFromRecordset function only makes sense if it is more than just a wrapper for the GetRows method.

Quote:

---

Originally Posted by Nouyana

Try to use the undocumented Collect property. It's 20% faster. Works with both - ADO and DAO. Or you may use GetRows getting by 10 rows step by step.



You may don't now what is in the table. Maybe you just want to SELECT * FROM unknown_table. If it's an object then you can insert the "object" word. In the Excel nothing is inserted from fields with objects. But with no errors of course.

In addition, I think that cutting rows and cols should be optional. Say, you have an dbOpenForwardOnly DAO recordset (which consumes low memory) and therefore you cannot determine the number of records, but you want to get them all.

Ultimately, the CopyFromRecordset function only makes sense if it is more than just a wrapper for the GetRows method.

---

With the object you need to explain what exact datatype the problematic field has so to check for it.
Of course it should be improved in that regard.

With your dbOpenForwardOnly DAO is problematic.
Here you better do a .moveNext and .AddItem each time until .EOF.
Or alternatively you make first a SELECT COUNT(*) and then adjust the grid rows and then feed via CopyFromRecordset from your dbOpenForwardOnly DAO.

Quote:

---

Originally Posted by Arnoutdv

I use the vsFlexGrid and always use basic coding statements like:

Code:

---

For lRow = .FixedRows To .Rows - 1
'
NofActiveCols = .Cols - .FrozenCols - .FixedCols

---

---

Yes, I code in a similar manner.

However, I humbly felt, that calling .cols(Active) [and perhaps even .rows(Lower), .rows(Upper)] will not only make things easier for people like me but also improve code readability and understanding for people like me.

By .cols(Active), I mean something that would be equal to ".Cols - .FrozenCols - .FixedCols". Similarly, .rows(Upper) would mean ".rows - 1".

The thing is that existing code of mine, yours or anybody's can remain intact (and can continue to remain intact, if one wishes to) since calling .rows without parameters would always default to .rows(All), similar to how calling .RowsVisible without parameters defaults to .RowsVisible(FlexVisibilityCompleteOnly).

So, no harm in making .rows and .cols to cover extra parameters, was/is my personal humble feeling (Or, am I wrong? Will there be any issues?). If no issues, then, once such extra parameters are introduced, I feel that people like me can readily start availing them while those who do not wish to use them can continue to code as usual in their regular manner. So, Krool can give a thought for the extra parameters, I humbly felt, and hence placed that earlier request of mine.

The names of the parameters can always be suitably chosen by Krool, I thought. I just suggested Active, All, SansFixed, Lower, Upper, etc. That's all.

Kind Regards.

Dear Krool,

For me, in ScrollTip event [ScrollTip(ByVal Row As Long, ByVal Col As Long)], the value of 'Col' is always -1. What mistake am I doing?

Note: For checking purpose, I set a value for .Col in the Scroll event. Then also, Col is always -1 in the ScrollTip event.

Just now I remembered that I can check the above in your demo too. So, I just now did the same. Only the Row value gets shown always in your demo. The Col value is never shown since in your code## you are showing the Col value only when Row is not greater than -1. So, what exactly is the role of Col value in the ScrollTip event? I am not able to understand clearly. Kindly let me know.

(##)

Quote:

---

Private Sub VBFlexGrid1_ScrollTip(ByVal Row As Long, ByVal Col As Long)
If Row > -1 Then
VBFlexGrid1.ScrollTipText = "Row " & VBFlexGrid1.TextMatrix(Row, 0)
ElseIf Col > -1 Then
VBFlexGrid1.ScrollTipText = "Column " & VBFlexGrid1.TextMatrix(0, Col)
End If
End Sub

---

Kind Regards.

Quote:

---

Originally Posted by softv

Dear Krool,

For me, in ScrollTip event [ScrollTip(ByVal Row As Long, ByVal Col As Long)], the value of 'Col' is always -1. What mistake am I doing?

Note: For checking purpose, I set a value for .Col in the Scroll event. Then also, Col is always -1 in the ScrollTip event.

Just now I remembered that I can check the above in your demo too. So, I just now did the same. Only the Row value gets shown always in your demo. The Col value is never shown since its always -1 only and in your code## you are showing the Col value only when it is not -1. So, what should be done so that Col is not -1 in the ScrollTip event?

(##)


Kind Regards.

---

On vertical scroll the col is -1.
So just perform a horiz. Scroll. See demo.

Thank you so much for your swift reply, Krool. Got it.

Kind Regards.

Note:
Even before I could see your reply, I had edited my original question, somewhat. Just informing you this. Nothing else. Because, your answer holds good of course even after I edited my question. I was not expecting "such a quick reply" from you! Thanks Krool, once again.

Dear Krool,

Now and then, I have read that VBFlexGrid can be used as a ListBox. Is it straightforward through some property or something like that? Or, have I to implement that functionality? If so, is there any readymade code snippet available which shows the ListBox implementation of VBFlexGrid? If so, kindly share.

Same way, if a readymade implementation of using VBFlexGrid as a ComboBox (incl. the 'Search' facility, as in a ComboBox) is available, kindly share the same. Will remain thankful.

If no readymade implementations available, I shall attempt them myself, of course, when time permits.

I take this opportunity to once again thank you for all your controls.

Kind Regards.

Quote:

---

Originally Posted by PietroV

Krool,
I have debugged going through the source code of the OCX and noted that for some reason the problem is caused by a TinyInt field:
although the ADODB recordset can handle the field correctly the ADODB Recordset.Getrows() function used to fill the ArrRows array fails to convert it correctly and create the problem.
Converting the field to a smallint solved it.
Thanks again for the great control.
Pietro

---

I tried to reproduce and TinyInt works. Which provider do you use? I tested with "sqloledb".