Solved

using grid control in vb6

Posted on 2007-03-17
14
1,953 Views
Last Modified: 2013-12-26
Hi,
      I am working on a billing system and for that I am using vb6 as front end and access at back end. For the main billing form (Gui) I want to let user enter as many entries of commodities sold. I have tried using DBGrid and MSFlexGrid.
DBGrid: I cant find a way to make the grid editable at run time, so that user can enter the information. How to make the grid editable at runtime?
MSFlexGrid: Here it lets user manipulate it at run time but i want as soon as the user finishes the first row, another new row should be automatically added to the grid. I have got hold of the event but I am unable to manipulate the rows. How the enter a new row?
Kindly suggest the solutions.
Thanking in anticipation
0
Comment
Question by:fatimao
  • 8
  • 5
14 Comments
 
LVL 22

Expert Comment

by:danaseaman
ID: 18743668
To add a new row use Flex1.Rows = Flex1.Rows + 1
0
 

Author Comment

by:fatimao
ID: 18744040
yup as i have wriiten in case of MSFlexGrid i am able to add rows but i cant make it editable (enable user to enter data in it at run time) ... where as in case of DBGrid i can't add rows

please do see to it
0
 
LVL 5

Accepted Solution

by:
mah8473 earned 175 total points
ID: 18754152
Both the Gridview and MSHFlexgrid control are not editable at runtime BUT.....you can make them appear to be editable by using a floating text box (or a number of floating textboxes,combobox etc) that you position over the grid cell, allow data to be entered and then on Lost_focus update the contents of the loating textbox to the grid

It sounds a little more complicated than it is and although there's a bit of work involved to get it working properly it's actually quiet an easy concept

Below is some basic sample code.....just put in a few break points and follow it through and then modify it to fit your usage and follow these steps.

1. Create a new form
2.  add a textbox to your form name txtEdit_CellOne, set properties as follows:
      backColor = &H00C0FFFF&   ' This is just to higlight which cell  txtEdit_CellOne is floating over on your grid
      Visible = False
      TabIndex = 0
3.  add a textbox to your form name txtEdit_CellTwo, set properties as follows:
      backColor = &H00FF00FF&   ' This is just to higlight which cell  txtEdit_CellTwo is floating over on your grid
      Visible = False
      TabIndex = 1
4. add an MSHFlexgrid to your form and name it fgdMyEditableGrid, Right-Click and SEND TO BACK (very important as the text boxes need to float on top of the grid), set Properties as follows:
     FixedCols = 0
     FixedRows = 1
     TabIndex = 2
     TabStop = True
5. Copy and paste the code below into your form
6. Now...........simply click a grid cell (it should appear higleghted yellow or pink, depending on col1/2), type a value and TAB to the next cell....and viola you should have text appearing in your grid cell
*** VERY IMPORTANT ***  if you have not clicked or Tabbed to another cell the Text entered will not update to the grid......likewise if you can't see the Yellow or pink floating textboxes in the cells SEND THE GRID TO BACK and try again

'===========================
Option Explicit
'Constants for the Grid Columns (you should give these more useful names based on the data fields, and add as many cols as you like)
Const g_COL_1 = 0
Const g_COL_2 = 1

Private Sub Form_Load()
 Call DesignGrid
End Sub

Private Sub DesignGrid()
On Error Resume Next
    With fgdMyEditableGrid
        .FormatString = "<Column1|<Column2"
        .Rows = 2
        .Cols = 2
        .ColWidth(g_COL_1) = 1000
        .ColWidth(g_COL_2) = 1000
   End With
End Sub

Private Sub fgdMyEditableGrid_Scroll()
   
    Call fgdMyEditableGrid_LeaveCell
   
End Sub

Private Sub fgdMyEditableGrid_GotFocus()
   
On Error Resume Next

    If txtEdit_CellOne.Visible Or txtEdit_CellTwo.Visible Then

        Call fgdMyEditableGrid_LeaveCell

        With fgdMyEditableGrid
       
            If .col = g_COL_1 Then
                If .TextMatrix(.row, g_COL_1) <> "" Then
                    'if grid receiving focus from Column1 then move Textbox to col column2
                    .col = g_COL_2
                End If
            ElseIf .col = g_COL_2 Then
                If .TextMatrix(.row, g_COL_1) <> "" Then
                    'if grid receiving focus from Column2 add a new Row
                    .row = .row + 1
                    .col = g_COL_1
                End If
            End If
        End With
       
        Call fgdMyEditableGrid_Click

    End If

End Sub

'set to contents of the Grid Cell to equal that of the Floating text box
Private Sub fgdMyEditableGrid_LeaveCell()
    Dim tempCol As Integer
     
On Error GoTo Err_LeaveCell
           
    If fgdMyEditableGrid.Enabled Then
        DoEvents
        fgdMyEditableGrid.Enabled = False

        With fgdMyEditableGrid
            tempCol = .col

            If txtEdit_CellOne.Visible Then
           
                .col = g_COL_1
                .Text = txtEdit_CellOne
                txtEdit_CellOne.Text = ""
                txtEdit_CellOne.Visible = False
                Call UpdateGridCell
               
            ElseIf txtEdit_CellTwo.Visible = True Then
               
                .col = g_COL_2
                .Text = txtEdit_CellTwo.Text
                txtEdit_CellTwo.Text = ""
                txtEdit_CellTwo.Visible = False
                Call UpdateGridCell
   
            End If

        End With

        DoEvents

        fgdMyEditableGrid.Enabled = True

    End If

Err_LeaveCell:
    If Err.Number <> 0 Then
        MsgBox Err.Description, , Err.Source
    End If

End Sub

'Set the Properties of the Floating Textbox to match the Cell Properties of the Grid
'so it appears that you are actually editing the Grid
Private Sub fgdMyEditableGrid_Click()
   
On Error Resume Next

    If fgdMyEditableGrid.col = g_COL_1 Then
               
        txtEdit_CellOne.Height = fgdMyEditableGrid.CellHeight
        Call EditGrid(fgdMyEditableGrid, txtEdit_CellOne)
        txtEdit_CellOne.Alignment = vbLeftJustify
        txtEdit_CellOne = fgdMyEditableGrid.Text
        txtEdit_CellOne.SelStart = 0
        txtEdit_CellOne.SelLength = Len(txtEdit_CellOne)
        txtEdit_CellOne.Visible = True
        txtEdit_CellOne.SetFocus
   
        Call UpdateGridCell
   
    ElseIf fgdMyEditableGrid.col = g_COL_2 Then
   
        txtEdit_CellTwo.Height = fgdMyEditableGrid.CellHeight
        Call EditGrid(fgdMyEditableGrid, txtEdit_CellTwo)
        txtEdit_CellTwo.Alignment = vbLeftJustify
        txtEdit_CellTwo = fgdMyEditableGrid.Text
        txtEdit_CellTwo.SelStart = 0
        txtEdit_CellTwo.SelLength = Len(txtEdit_CellTwo)
        txtEdit_CellTwo.Visible = True
        txtEdit_CellTwo.SetFocus
   
        Call UpdateGridCell
     
    End If

End Sub

'move to the next Grid Cell or new line
Private Sub UpdateGridCell()
    Dim iCounter As Integer
       
On Error GoTo Err_UpdateGridCell
   
    With fgdMyEditableGrid
        If .Rows > 1 Then
            For iCounter = 1 To .Rows - 1
                If .TextMatrix(iCounter, g_COL_1) <> "" Then
                    If iCounter = .Rows - 1 Then
                        .row = iCounter
                        .Rows = .Rows + 1
                    End If
                End If
            Next iCounter
        End If
    End With

Err_UpdateGridCell:
    If Err.Number <> 0 Then
        MsgBox Err.Description, , Err.Source
    End If

End Sub

Public Function EditGrid(ByVal objEditGrid As Object, ByVal objEditTextBox As Object)
Dim a As Integer
    MsFlexGridEdit objEditGrid, objEditTextBox, 32, a
    objEditTextBox.Top = objEditGrid.Top + objEditGrid.CellTop
    objEditTextBox.Left = objEditGrid.Left + objEditGrid.CellLeft
    objEditTextBox.Width = objEditGrid.CellWidth
End Function

Public Function EditGridFrame(ByVal objEditGrid As Object, ByVal objEdit As Object)
    Dim a As Integer
On Error Resume Next
   
    MsFlexGridEdit objEditGrid, objEdit, 32, a
    objEdit.Top = objEditGrid.Top + objEditGrid.CellTop
    objEdit.Left = objEditGrid.Left + objEditGrid.CellLeft
    objEdit.Width = objEditGrid.CellWidth
   
End Function

Private Sub MsFlexGridEdit(MsFlexGrid As Control, Edit As Control, KeyAscii As Integer, a As Integer)
   
    On Error GoTo ErrTrap
   
    'Use the character that was typed
    Select Case KeyAscii
        'A space means edit the currenct text
        Case 0 To 32
            Edit = MsFlexGrid
            Edit.SelStart = 1000
        'Enything else means replace the current text
        Case Else
            Edit = Chr(KeyAscii)
            Edit.SelStart = 1
    End Select
    'Show edit at the right place
    Edit.Visible = True
   
    'And let it work
    Edit.SetFocus
   
    Exit Sub
ErrTrap:
           MsgBox Err.Description, , Err.Source
End Sub

Good luck......Dunno how many points you have available but this is a fairly complex answer if you wanna UP the points some I'd certainly appreciate them :)
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18754183
PS: when you want to write the data entered into the grid back to your DB tables simply cycle through the grid and read out the values of each cell and write them to the db

Something like :

dim iCounter as integer
dim sCell1 as string
dim sCell2 as string

'loop through grid
with fgdMyEditableGrid
        For iCounter = 1 To .Rows - 1
            sCell1  = .TextMatrix(iCounter, g_COL_1)
            sCell2 = .TextMatrix(iCounter, g_COL_2)
                   
            'Write some code to Insert those values into your DB Tbles
        Next iCounter
end with
0
 

Author Comment

by:fatimao
ID: 18757571
thank you so much ... i have modified the code according to my requirement and i have few queries

1) how to automatically set the cursor at first cell?
2) how to add tab backwards?
3) In my modified code the row is added when i move from first col to second (from 2nd row) can you please mention the place where i am going wrong ... i have extended the code to incorporate 6 columns and i want new row to be added when the tab is moved from last column of previous row ...

thanks again for all the help ... the code is really cool and well commented
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18760545
Glad to be of assistance......and good questions......I've not tried the first 2....I'll see what I can come up with

And I've just gone back to my code and it seems I have a bug somewhere....it's adding the new row after leaving the first column but not tabbing to the new row after the last column.....gimme a sec to sort it out and I'll modify it to have more than 2 columns also.
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18760593
That's better :)

I'll start with your 3rd question coz it's the easiest....To add the new row after the last column change the UpdateGridCell procedure to test that all Grid Cols are <> "" before adding a new row, see below

'move to the next Grid Cell or new line
Private Sub UpdateGridCell()
    Dim iCounter As Integer
On Error GoTo Err_UpdateGridCell
    With fgdMyEditableGrid
        If .Rows > 1 Then
            For iCounter = 1 To .Rows - 1
'    ***** test all columns have a value before adding the new line*****
                If .TextMatrix(iCounter, g_COL_1) <> "" And .TextMatrix(iCounter, g_COL_2) <> "" And .TextMatrix(iCounter, g_COL_3) <> "" And .TextMatrix(iCounter, g_COL_4) <> "" And .TextMatrix(iCounter, g_COL_5) <> "" Then
                    If iCounter = .Rows - 1 Then
                        .Row = iCounter
                        .Rows = .Rows + 1
                    End If
                End If
            Next iCounter
        End If
    End With
Err_UpdateGridCell:
    If Err.Number <> 0 Then
        MsgBox Err.Description, , Err.Source
    End If
End Sub
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 5

Expert Comment

by:mah8473
ID: 18760921
OK your 2nd question re Tabbing backwards....to be honest I've not ever had a need to do this and remember being that we are "SIMULATING" an editable gird I'm tipping it's not going to be 100% straightforward.....but as yet none of my users have requested it......but it would be nice to have wouldn't it :)

Basically the fgdMyEditableGrid_GotFocus event is coded so that that cursor moves left to right as the grid receives focus back from the floating textbox
EG:
With fgdMyEditableGrid        
            If .Col = g_COL_1 Then
                If .TextMatrix(.Row, g_COL_1) <> "" Then
 ' **** if grid receiving focus from Column1 then move to column2 *****
                    .Col = g_COL_2
                End If
            ElseIf .Col = g_COL_2 Then
                If .TextMatrix(.Row, g_COL_2) <> "" Then
 ' **** if grid receiving focus from Column2 then move to column3 ***** etc, etc, etc
                    .Col = g_COL_3
            ElseIf .Col = g_COL_3 Then
                If .TextMatrix(.Row, g_COL_3) <> "" Then
' **** if grid receiving focus from Column3 move to new row column1 *****
                    .Row = .Row + 1
                    .Col = g_COL_1
                End If
            End If
        End With

Unfortunately I haven't the time at the moment to work this out as will definately be a bit of a headache....I might have to leave that one for you to work out yourself.....or if I find time I'll post back later
0
 

Author Comment

by:fatimao
ID: 18765802
thanks you so so very much for all the help and explained answers :)
1) you havent answered this part yet ... please do that too
2) i will definaltely try this out my self as i am getting hold of your code now
3) thanks the problem is solved ... BUT there is little problem i am having. You have written the function with 5 columns whereas my form have 6 columns ... if i dont change the code it works fine, but if i add an additional check  And .TextMatrix(iCounter, g_COL_6) <> "" in the main if ... then i get error msg twice on each tab ... the message is "TYPE MISMATCH" except that it works absolutely fine.

thanks again for all the help and well written code.
I dont have much points left but still i have increased the points a bit :)
0
 

Author Comment

by:fatimao
ID: 18766011
i just came across another problem :( ,,, when i add few more controls in the same form like buttons and text boxes ... the tab is disturbed. e.g. if i add three buttons then i have to press tab four times to move to next column  ... PLEASE DO solve this problem

thank you so much
waiting for reply
regards
fa
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18768380
1. Sorry I had a full day yesterday and wasn't able to get back to you.....still haven't had a chance to look at this as yet.....but you could propably just position the flaoting textbox over the grid (in line1 Cell1)and set it to visible on form load...its the first Tabstop so it should receive focus...try it and let me know

3. I only had 5 cols to illustrate the concept with more columns.....post your code in full so I can have a look at it with 6 columns.....in addition can you highlight where the "Type Mismatch" is occuring?

4. If you have other controls on the form just ensure that your tab order is correct....floating textboxes first, then the MSHFlexGrid, then all other controls....I usually set TabStop = false on buttons so they are not clicked in error, ie: if they receive focus the enter key will fire their click event.....So basically by ensuring your tab order is correct because the Floating Textboxes are not visible on page load the focus will normally drop to the first visible control....which would normally not be those used with the grid.....and should be the first "other" control in the TabIndex....Does make sense?

PS: Just to let you know I have also used this concept with floating ComboBoxes and DatePickers so you have more control over the type of data entered.....and with CheckBoxes to select multiple rows to update etc........
I mainly use this concept with StockControl for receipting/despatching goods, particularly with a handheld scanner so our stockroom can just scan serialNo after serialNo into a grid to fill an order......like I said originally it sounds more complicated than it is but it has proved (for me anyway) to be a reeeeeally handy little routine.......once the headache of getting it to work went away :)
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18768540
oh yeah I assume you have modified the DesignGrid routine to be 6 cols wide?

Private Sub DesignGrid()
On Error Resume Next
    With fgdMyEditableGrid
        .FormatString = "<Column1|<Column2|<Column3|<Column4|<Column5|<Column6"
        .Rows = 2
' *** .cols should match the number of cols required
        .Cols = 6
        .ColWidth(g_COL_1) = 1000
        .ColWidth(g_COL_2) = 2000 ' set these to whatever width you want really
        .ColWidth(g_COL_3) = 1000
        .ColWidth(g_COL_4) = 2000
        .ColWidth(g_COL_5) = 1000
        .ColWidth(g_COL_6) = 2000
   End With
End Sub

and make sure your fgdMyEditableGrid_GotFocus, fgdMyEditableGrid_LeaveCell and fgdMyEditableGrid_Click events and the UpdateGridCell procedure all have IF tests for your 6 columns
0
 

Author Comment

by:fatimao
ID: 18773020
thank you so very much ... your solution of making cursor visible at first cell worked :)
and i think i shouldn't bother you on tiny miny problems ... i will try to solve them myself ... thanks again

i have also posted few other questions under the domain of vb so if you find time ... kindly do look into those also.

best regards
0
 
LVL 5

Expert Comment

by:mah8473
ID: 18775514
No worries glad i could help :)

Good luck
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now