selecting from a grid

[PJB]

any suggestions on how i can setup an ms flex grid so that when i select a record that specific record populates some text boxes i have on the same page.
right now they are all hooked to the same data control.

Pete Butler(terminally confused)

[Paul Warren]

This populates the flexgrid from a recordset, sizing the column widths based upon the data and then when the user clicks on a cell it populates a control array of text boxes with the values. It assumes that the flexgrid has no fixed columns.

Private Const SCALE_FAC As Integer = 12
Private cnTemp As New Connection
Private rsTemp As New Recordset


Private Sub Fill_Grid()

Dim intNumber As Integer
Dim intCount As Integer
Dim RowNum As Integer
Dim intLen As Integer
Dim intRows As Integer

cnTemp.ConnectionString = "Provider = Microsoft.Jet.OLEDB.3.51; Data Source = C:\db1.mdb"
cnTemp.Open
rsTemp.Open "Select * FROM [Table]", cnTemp, adOpenStatic, adLockOptimistic

rsTemp.MoveLast

If rsTemp.RecordCount = 0 Then
    Exit Sub
End If

intRows = rsTemp.RecordCount
intNumber = rsTemp.Fields.Count

' Set the dimensions of the grid based on the recordset
grdFields.Cols = intNumber
grdFields.Rows = 1

rsTemp.MoveFirst

intLen = 0

' Get the headings from the information recordset
For intCount = 0 To intNumber - 1
    
    intLen = Len(rsTemp.Fields(intCount).Name) + 1
    grdFields.ColWidth(intCount) = (intLen * (grdFields.FontSize * SCALE_FAC) + 200)
    grdFields.TextMatrix(0, intCount) = rsTemp.Fields(intCount).Name
    grdFields.Col = intCount
    grdFields.CellFontBold = True
    rsTemp.MoveNext

Next intCount

rsTemp.MoveFirst

RowNum = 0

Do Until rsTemp.EOF

    RowNum = RowNum + 1
    grdFields.AddItem " " ' Add a blank row to populate
    
    For intCount = 0 To intNumber - 1 ' Loop through the records, inserting the data
        If Not IsNull(rsTemp.Fields(intCount).Value) Then
            intLen = Len(rsTemp.Fields(intCount).Value) + 1
            If grdFields.ColWidth(intCount) < (intLen * (grdFields.FontSize * SCALE_FAC) + 200) Then
                grdFields.ColWidth(intCount) = (intLen * (grdFields.FontSize * SCALE_FAC) + 200)
            End If
            grdFields.TextMatrix(RowNum, intCount) = rsTemp.Fields(intCount).Value
        Else
            grdFields.TextMatrix(RowNum, intCount) = " "
        End If
        grdFields.ColAlignment(intCount) = 0
    Next intCount
    
    rsTemp.MoveNext
    
Loop

End Sub

Private Sub Form_Load()

Fill_Grid

End Sub

Private Sub grdFields_Click()

Dim iRow As Integer
Dim iCount As Integer

iRow = grdFields.Row

' If you start from zero for the cols you'll get the blank f
For iCount = 0 To grdFields.Cols - 1
    txtFields(iCount) = grdFields.TextMatrix(iRow, iCount)
Next iCount

End Sub

If you have any more questions simply post them back here and I'll try to answer them.

[PJB]

Thanx Paul I'll give it a try

Pete

[PJB]

Private cnTemp As New Connection


This line keeps giving me a User Type Not Defined Error

[Paul Warren]

You'll need to add reference in your project to 'Microsoft ActiveX Data Objects 2.5 Library'. Also, this code works for Access 9x, to change to to cope with Access 2000 change the OLDB.3.51 to OLEDB.4.0 in the .ConnectionString.

The references are added from the projects menu.

[PJB]

Thanx again Paul, I've got it working now, i just need to get my search function to work properly and i should be free and clear

Pete