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