[RESOLVED] MSHFlex grid Problem

**maheshkanda** · Feb 6th, 2009, 12:52 AM

Hi friends i would like to know how to get a result in to the text box when i am going to click on any record in the grid the record set has to be shown in to the textboxes just doing double clicking on it i am having the following code which is fetching the records and showing them but when i am clicking on any record its not showing in the textboxes please help me

Code:

Dim cnGlobal As New ADODB.Connection
Dim RsAccess As New ADODB.Recordset
Dim sConnect As String
Private sSQL As String
Private Sub Combo2_Click()
  Dim bItemSelected As Byte
  bItemSelected = Me.Combo2.ListIndex
  Select Case bItemSelected
    Case 0
      Me.Label8.Caption = "Enter The TerminalID"
      Text3.Visible = True
      DTPicker2.Visible = False
    Case 1
      Me.Label8.Caption = "Enter the date "
      Text3.Visible = False
      DTPicker2.Visible = True
    Case 2
      Me.Label8.Caption = "Enter last 4 Digits"
      Text3.Visible = True
      DTPicker2.Visible = False
    Case 3
      Me.Label8.Caption = "Enter last 4 Digits"
      Text3.Visible = True
      DTPicker2.Visible = False
    Case 4
      Me.Label8.Caption = "Enter the Authcode"
      Text3.Visible = True
      DTPicker2.Visible = False
  End Select
End Sub

Code:

Private Sub Command1_Click()
End  ' closes the window
End Sub

Private Sub Command2_Click()
Export MSHFlexGrid1, "Export of Data"
End Sub

Private Sub Command4_Click()
If Option1.Value = False And Option2.Value = False Then
MsgBox "Enter your choice"
Else

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
    '---> Change your DSN <-----'
        .ActiveConnection = "Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=worldspace;User=root; Password=password;Option=10;"
        .LockType = adLockOptimistic
        .CacheSize = 1
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        If Option1.Value = True Then
                .Source = "Select * from user where address= '" & Combo1.Text & "' and subdate ='" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' "
        End If
        
            If Option2.Value = True Then
            .Source = "select * from localcard"           
          
        
                If Combo2.Text = "Terminalid" Then
                .Source = "Select * from localcard "
                End If

                If Combo2.Text = "trxndate" Then
                .Source = "Select * from localcard  where trxndate='" & Format(DTPicker2.Value, "yyyy-mm-dd") & "'"
                End If

                If Combo2.Text = "FTNumber" Then
                .Source = "Select * from localcard where FTNumber like '%" & Text3.Text & "' "
                End If

                If Combo2.Text = "SessionID" Then
                .Source = "Select * from localcard  where SessionID like '%" & Text3.Text & "'"
                End If

                If Combo2.Text = "Authcode" Then
                .Source = "Select * from localcard "
                End If
           
            End If
            
       .Open
        Call Fill_Grid(rs, Me.MSHFlexGrid1)
        Set .ActiveConnection = Nothing
        Set rs = Nothing
'        rs.Cancel
        
    End With  
End If
End Sub

Code:

Private Sub Command5_Click()
Text1(0).Visible = True
Dim gridRow As Long

    gridRow = IIf(MSHFlexGrid1.Row > 1, MSHFlexGrid1.Row - 1, MSHFlexGrid1.Rows - 1)
    ShowDetails gridRow

End Sub

Private Sub Command6_Click()
Text1(0).Visible = True
Dim gridRow As Long

    gridRow = IIf(MSHFlexGrid1.Row < MSHFlexGrid1.Rows - 1, MSHFlexGrid1.Row + 1, 0)
    ShowDetails gridRow
End Sub

Code:

Private Sub Form_Load()
'Establish Connection
    sConnect = "Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=worldspace;User=root; Password=password;Option=10;"
    cnGlobal.Open sConnect
    Label6.Caption = "Database has been Connected..."
   Timer1.Interval = 100
     Label5.Caption = ""
     Label2.Caption = ""
Me.Label8.Caption = ""
  With Me.Combo2
    .AddItem ("Terminalid")
    .AddItem ("trxndate")
    .AddItem ("FTNumber")
    .AddItem ("SessionID")
    .AddItem ("Authcode")
    .ListIndex = 0
  End With
     sSQL = "SELECT DISTINCT address FROM user" 'Filter Fields with a unique Name
     RsAccess.Open sSQL, cnGlobal
While Not RsAccess.EOF
  Combo1.AddItem RsAccess("address")
  RsAccess.MoveNext ' View all Field Content
Wend
end Sub
Private Sub Timer1_Timer()
Label5.Caption = Time
Label2.Caption = Date
End Sub

Code:

Function Fill_Grid(rs As ADODB.Recordset, _
                   ctr As MSHFlexGrid)
    Dim lCols As Long
    Dim lRows As Long
    Dim lCol As Long
    Dim lRow As Long
    Dim sColor As String
   
    'Check for BOF
    If (Not (rs.BOF)) Then
        'Num of Columns
        lCols = rs.Fields.Count
        With ctr
            .Cols = lCols
            .Row = 0
            'Fill Columns Headers
            For lCol = 0 To lCols - 1
                .Col = lCol
                .Text = rs(lCol).Name
                .ColWidth(lCol) = 1500
            Next
            'Get data
            lRow = 1
        
            Do While Not rs.EOF
                For lCol = 0 To lCols - 1
                    .Col = lCol
                    .Row = lRow
                    .Text = rs.Fields(lCol).Value
                                        
                Next
                 rs.MoveNext
                 lRow = lRow + 1
                .Rows = lRow + 1
            Loop
        End With
    End If
End Function

Code:

'Create a module and write following code :
Public Function Export(FG As MSHFlexGrid, Sname As String)
Static objexc As Object
Static objexcwkb As Excel.Workbook
Static objexcwst As Excel.Worksheet
Dim P As Long
Dim q As Long
Dim newCell As String

Set objexc = CreateObject("Excel.Application")
If Err.Number <> 0 Then
Set objexc = New Excel.Application
Err.Clear
End If
On Error Resume Next
objexc.Visible = False
objexc.DisplayAlerts = False
Set objexcwkb = objexc.Workbooks.Add
Set objexcwst = objexc.ActiveSheet
For P = o To FG.Rows - 1
For q = o To FG.Cols - 1
FG.Row = P
FG.Col = q
newCell = Chr(q + 65) & P + 1
objexcwst.Range(newCell).Value = FG.Text
If Option1.Value = True Then
objexcwst.Range("G:I").NumberFormat = "[$-409]d-mmm-yy;@"
End If
If Option2.Value = True Then

objexcwst.Range("A:A").NumberFormat = "[$-409]d-mmm-yy;@" 'sets the date in the format 24-nov-2008
objexcwst.Range("C:D").NumberFormat = "0"
objexcwst.Range("G:H").NumberFormat = "0.00" 'sets the cost in the format of indian rupees like 34.50
objexcwst.Columns("C:D").ColumnWidth = 18 'sets the column width as 18
End If
Next
Next
objexc.Visible = True
 objexc.DisplayAlerts = True
Set objexc = Nothing
 Set objexcwrk = Nothing
 Set objexcwst = Nothing
End Function

Code:

Public Sub ShowDetails(currentRow As Long)
Dim iCol As Integer
Dim iRow As Integer

 If Label10.UBound > 0 Then
        For iCol = 1 To Label10.UBound
           Unload Label10(iCol)
           Unload Text1(iCol)
        Next iCol
    End If
    
    With MSHFlexGrid1
        For iCol = 0 To .Cols - 1
            If iCol = 0 Then
                Label10(iCol).Caption = .TextMatrix(0, iCol)
                Text1(iCol).Text = .TextMatrix(currentRow, iCol)
            Else
                Load Label10(iCol)
                Load Text1(iCol)
                Label10(iCol).Move Label10(0).Left, _
                                  Text1(iCol - 1).Top + Text1(iCol - 1).Height + 60, _
                                  Label10(0).Width, _
                                  Label10(0).Height
                Text1(iCol).Move Text1(0).Left, _
                                  Text1(iCol - 1).Top + Text1(iCol - 1).Height + 60, _
                                  Text1(0).Width, _
                                  Text1(0).Height
                
                Label10(iCol).Caption = .TextMatrix(0, iCol)
                Text1(iCol).Text = .TextMatrix(currentRow, iCol)
                Label10(iCol).Visible = True
                Text1(iCol).Visible = True
            End If
        Next iCol
        .Row = currentRow
    End With

End Sub

**maheshkanda** · Feb 9th, 2009, 04:03 AM

Some one please help me

**VBFnewcomer** · Feb 10th, 2009, 06:03 AM

Try this methodology.

MS(H)Flexgrid has click event.
when a row is clicked you can get the row rumber.
call a sub to retrieve the records in the click event and pass the row number
loop through all the cols while placing the values into the textboxes

getting the idea!

**maheshkanda** · Feb 10th, 2009, 06:22 AM

i know it but i don't know how to assign the things please guide me or give me a sample one i will change acc. to me

**VBFnewcomer** · Feb 10th, 2009, 06:59 AM

Lets assume MSF has one fixed col and 3 normal cols.
place 3 textboxes (Text1, Text2, Text13) on the form.
place the below code in the declarations section of the form and run.
click on any row and see the values tansferred to the textboxes..

Code:

Private Sub Form_Load()
  Dim iCol As Integer
  Dim iRow As Integer
  With Me.MSFlexGrid
    For iRow = 0 To .Rows - 1
      For iCol = 1 To .Cols - 1
        .TextMatrix(iRow, iCol) = iRow & iCol
      Next
    Next
  End With
  
  Me.Text1.Text = ""
  Me.Text2.Text = ""
  Me.Text3.Text = ""
  
End Sub

Private Sub MSFlexGrid_Click()
  Dim igrdRow As Integer
  Dim i As Integer
  
  Me.Text1.Text = ""
  Me.Text2.Text = ""
  Me.Text3.Text = ""
  
  igrdRow = Me.MSFlexGrid.Row
  
  Me.Text1.Text = Me.MSFlexGrid.TextMatrix(igrdRow, 1)
  Me.Text2.Text = Me.MSFlexGrid.TextMatrix(igrdRow, 2)
  Me.Text3.Text = Me.MSFlexGrid.TextMatrix(igrdRow, 3)
End Sub

This only a simple code which needs to be improved and customised as per your needs
Happy programming

**maheshkanda** · Feb 10th, 2009, 07:42 AM

Here in this i used the only one text box with index and i am getting error at the following

Text1(iCol).Text = ""

and here i changed the code in this manner

Code:

Private Sub Form_Load()
  Dim iCol As Integer
  Dim iRow As Integer
  With Me.MSHFlexGrid1
    For iRow = 0 To .Rows - 1
      For iCol = 1 To .Cols - 1
        .TextMatrix(iRow, iCol) = iRow & iCol
      Next
    Next
  End With
  
 Text1(iCol).Text = ""
  'Me.Text2.Text = ""
  'Me.Text3.Text = ""
  
End Sub

Private Sub MSHFlexGrid1_Click()
  Dim igrdRow As Integer
  Dim i As Integer
  
  Me.Text1(iCol).Text = ""
  'Me.Text2.Text = ""
  'Me.Text3.Text = ""
  
  igrdRow = Me.MSHFlexGrid1.Row
  
  Me.Text1(iCol).Text = Me.MSHFlexGrid1.TextMatrix(igrdRow, iCol)
'  Me.Text2.Text = Me.MSHFlexGrid1.TextMatrix(igrdRow, 2)
 ' Me.Text3.Text = Me.MSHFlexGrid1.TextMatrix(igrdRow, 3)
End Sub

**VBFnewcomer** · Feb 10th, 2009, 08:05 AM

simple

iCol has value 4 but the cmd butn control has an index no. 0 (zero) Change the index to 4 it will work.

**maheshkanda** · Feb 11th, 2009, 01:45 AM

Well i tried that still i am facing the same problem here iam attaching the code if any changes i have to do please say to me and for the text box index i given as 0 please its urgent

Code:

Private Sub Form_Load()
  Dim iCol As Integer
  Dim iRow As Integer
  With Me.MSHFlexGrid1
    For iRow = 0 To .Rows - 1
      For iCol = 1 To .Cols - 1
        .TextMatrix(iRow, iCol) = iRow & iCol
      Next
    Next
  End With
  Me.Text1(iCol).Text = ""
  End Sub

Private Sub MSHFlexGrid1_Click()
  Dim igrdRow As Integer
  Dim i As Integer
  Dim iCol As Integer
    Me.Text1.Text = ""
  igrdRow = Me.MSHFlexGrid1.Row
  Me.Text1(iCol).Text = Me.MSHFlexGrid1.TextMatrix(igrdRow, iCol)
   
End Sub

Private Sub Option1_Click()
Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
    '---> Change your DSN <-----'
        .ActiveConnection = "Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=worldspace;User=root; Password=password;Option=10;"
        .LockType = adLockOptimistic
        .CacheSize = 1
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Source = "Select * from user "
    
        .Open
        Call Fill_Grid(rs, Me.MSHFlexGrid1)
        Set .ActiveConnection = Nothing
        Set rs = Nothing
'        rs.Cancel
           End With
End Sub
Function Fill_Grid(rs As ADODB.Recordset, _
                   ctr As MSHFlexGrid)

    Dim lCols As Long
    Dim lRows As Long
    Dim lCol As Long
    Dim lRow As Long
    Dim sColor As String
   

    'Check for BOF
    If (Not (rs.BOF)) Then
        'Num of Columns
        lCols = rs.Fields.Count
        With ctr
            .Cols = lCols
            .Row = 0
            'Fill Columns Headers
            For lCol = 0 To lCols - 1
                .Col = lCol
                .Text = rs(lCol).Name
                .ColWidth(lCol) = 1500
            Next
            'Get data
            lRow = 1
        
            Do While Not rs.EOF
                For lCol = 0 To lCols - 1
                    .Col = lCol
                    .Row = lRow
                    .Text = rs.Fields(lCol).Value
                                        
                Next
                 rs.MoveNext
                 lRow = lRow + 1
                .Rows = lRow + 1
            Loop
        End With
    End If
End Function

**VBFnewcomer** · Feb 11th, 2009, 02:09 AM

When you get an error don't simply say error occured, pls state what was the error message you got, so that members can pinpoint the error.
Now wats the error you get.
Also check if you get error when you use normal text box (remember to remove that iCol though!)
if that works, then delete the texbox and add a new one. copy paste the same, say yes to warning, and see if it works

**maheshkanda** · Feb 11th, 2009, 02:32 AM

Here i did a little bit change to the code but still i am facing the problem and i am attaching the screen shot of that error

Code:

Private Sub Form_Load()
  Dim iCol As Integer
  Dim iRow As Integer
  With Me.MSHFlexGrid1
    For iRow = 0 To .Rows - 1
      For iCol = 1 To .Cols - 1
        .TextMatrix(iRow, iCol) = iRow & iCol
      Next
    Next
  End With
'  Me.Text1(iCol).Text = ""
  Me.Text2.Text = ""
  Me.Text3.Text = ""
  
  End Sub

Private Sub MSHFlexGrid1_Click()
  Dim igrdRow As Integer
  Dim i As Integer
  Dim iCol As Integer
    'Me.Text1.Text = ""
  igrdRow = Me.MSHFlexGrid1.Row
With Me.MSHFlexGrid1
For iRow = 0 To .Rows - 1
For iCol = 0 To .Cols - 1
  Me.Text1(iCol).Text = Me.MSHFlexGrid1.TextMatrix(igrdRow, iCol)
  
   Next
Next
   End With
End Sub

Private Sub Option1_Click()
Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
    '---> Change your DSN <-----'
        .ActiveConnection = "Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=worldspace;User=root; Password=password;Option=10;"
        .LockType = adLockOptimistic
        .CacheSize = 1
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Source = "Select * from user "
    
        .Open
        Call Fill_Grid(rs, Me.MSHFlexGrid1)
        Set .ActiveConnection = Nothing
        Set rs = Nothing
'        rs.Cancel
           End With
End Sub
Function Fill_Grid(rs As ADODB.Recordset, _
                   ctr As MSHFlexGrid)

    Dim lCols As Long
    Dim lRows As Long
    Dim lCol As Long
    Dim lRow As Long
    Dim sColor As String
   

    'Check for BOF
    If (Not (rs.BOF)) Then
        'Num of Columns
        lCols = rs.Fields.Count
        With ctr
            .Cols = lCols
            .Row = 0
            'Fill Columns Headers
            For lCol = 0 To lCols - 1
                .Col = lCol
                .Text = rs(lCol).Name
                .ColWidth(lCol) = 1500
            Next
            'Get data
            lRow = 1
        
            Do While Not rs.EOF
                For lCol = 0 To lCols - 1
                    .Col = lCol
                    .Row = lRow
                    .Text = rs.Fields(lCol).Value
                                        
                Next
                 rs.MoveNext
                 lRow = lRow + 1
                .Rows = lRow + 1
            Loop
        End With
    End If
End Function

**VBFnewcomer** · Feb 11th, 2009, 03:03 AM

I believe you are mixing up too many things.

First read post #3 & #5. Understand what we did here.
you are simply employing copy-paste routine
start a new proj do exactly as stated in #5 and post the feedback

Also use MSPaint to attach images, MSword is not reqd.

**maheshkanda** · Feb 12th, 2009, 11:39 PM

i did full of ground work but i am not will you give me step by step process for me thats i know i mixed full of things will u first read my first post once

then u will understand way i mixed this things i hope u will replay to me soon please

**VBFnewcomer** · Feb 13th, 2009, 12:26 AM

PM me when you have some time to spare.
I'll be sticking to MSflexgrid and not MSHflexgrid

**maheshkanda** · Feb 13th, 2009, 12:54 AM

k you just do it on the msflexgrid and send it to me i will change it according to me i hope u will soon waiting for ur replay

.....

**VBFnewcomer** · Feb 13th, 2009, 12:59 AM

don't go offline for another few mins..
Open a new proj and keep it ready...

**VBFnewcomer** · Feb 13th, 2009, 01:02 AM

Now add a MSflexgrid (MSFlexgrid1) you should find it in Project->components MS flexgrid control 6.0.
Now add 3 text boxes (not control array) Text1, Text2, Text3
confirm you have done these

**maheshkanda** · Feb 13th, 2009, 01:04 AM

ok i am ready with new project you can gohead and i will available for u today at any time this is my mail id ur's please

**VBFnewcomer** · Feb 13th, 2009, 01:09 AM

I believe no one is permitted to post their email in the forums. use edit button and delete it.
in future use PM for posting it.
OK i'll meet u in google chat....
login there

**maheshkanda** · Feb 13th, 2009, 06:28 AM

Here i solved the problem here is the code i changed and thanks VBFnewcomer
just add this code to the grid click event every thing is given above first post is same

Code:

Private Sub MSHFlexGrid1_Click()
 Dim igrdRow As Integer
 Dim iRow As Integer
  Dim i As Integer
Dim iCol As Integer
 Text1(iCol).Text = MSHFlexGrid1.TextMatrix(igrdRow, iCol)
  
  igrdRow = Me.MSHFlexGrid1.Row
  
Dim gridRow As Long

    gridRow = IIf(MSHFlexGrid1.Row, MSHFlexGrid1.Row, MSHFlexGrid1.Rows)
    ShowDetails gridRow
 Text1(iCol).Text = Me.MSHFlexGrid1.TextMatrix(igrdRow, iRow)
End Sub

Thread: [RESOLVED] MSHFlex grid Problem

Thread Tools

Display

[RESOLVED] MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Re: MSHFlex grid Problem

Posting Permissions