Hi all,

I have a query as to whether something is possible with DataGrids in SLX 7.5.4, I think I'm almost there but there are still issues. Basically, I want to set up several DataGrids with Recordsets that do not point to a Table, and are instead just fully set in code. What I have right now in Form startup to set up the Grids:

<p>Sub CreateGridRecordSet(objGrid)
    Dim objRS  ' Empty Recordset to return
    Dim col    ' Column setup
    Dim iLoop  ' Loop through columns
 
    ' Create Recordset object
    Set objRS = Application.CreateObject("ADODB.Recordset")
    objRS.CursorLocation = adUseClient
    objRS.CursorType = adOpenStatic
    objRS.LockType = adLockOptimistic
 
   ' Blank out SQL for Grid?
    objGrid.SQL.Text = ""
 
    ' Add columns to Recordset definition
    With objRS
        .Fields.Append "EVXCOURSEFEEID", adChar, 12, adFldIsNullable
        .Fields.Append "GRIDSELECT", adBoolean, , adFldIsNullable
        .Fields.Append "DESCRIPTION", adVarChar, 64, adFldIsNullable
        .Fields.Append "AMOUNT", adDouble, , adFldIsNullable
        .Fields.Append "STARTDATE", adDate, , adFldIsNullable
        .Fields.Append "ENDDATE", adDate, , adFldIsNullable
    End With
 
    ' Remove any preexisting column settings
    If (objGrid.Columns.Count > 0) Then
        For iLoop = 0 to objGrid.Columns.Count - 1
            objGrid.Columns.Item(0).Delete
        Next
    End If

    ' Set columns for Grid
    Set col = objGrid.Columns.Add(0)
    col.FieldName = "EVXCOURSEFEEID"
    col.Visible = False
 
    Set col = objGrid.Columns.Add(0)
    col.FieldName = "GRIDSELECT"
    col.Caption = "Selected"
    col.Width = 100
 
    Set col = objGrid.Columns.Add(0)
    col.FieldName = "DESCRIPTION"
    col.Caption = "Description"
    col.width = 100
 
    Set col = objGrid.Columns.Add(0)
    col.FieldName = "AMOUNT"
    col.Caption = "Amount"
    col.Width = 100
 
    Set col = objGrid.Columns.Add(0)
    col.FieldName = "STARTDATE"
    col.Caption = "From"
    col.FormatType = 3
    col.FormatString = "dd-MM-yyyy"
    col.Width = 75
 
    Set col = objGrid.Columns.Add(0)
    col.FieldName = "ENDDATE"
    col.Caption = "To"
    col.FormatType = 3
    col.FormatString = "dd-MM-yyyy"
    col.Width = 75
 
    ' Set Recordset for Grid
    objRS.Open
    Set objGrid.Recordset = objRS
    objGrid.Refresh
End Sub

I am uncertain if the Grid's SQL property is interfering with this as things are, and if I need to blank that somehow; I do know that without opening the recordset before setting it to be the DataGrid's recordset, I get an error complaining that 'the recordset is not open'

Here is the code for adding a record to the recordset, once the ID has been obtained:

<p>...

	    MsgBox "Adding Fee record"
            With Sender.Recordset
                .AddNew
                .Fields("EVXCOURSEFEEID").Value = Lookup.ID
                .Fields("GRIDSELECT").Value = True
                .Fields("DESCRIPTION").Value = GetField("DESCRIPTION","EVXCourseFee","EVXCOURSEFEEID = '" & Lookup.ID & "'")
                .Fields("AMOUNT").Value = GetField("AMOUNT","EVXCourseFee","EVXCOURSEFEEID = '" & Lookup.ID & "'")
                .Fields("STARTDATE").Value = GetField("STARTDATE","EVXCourseFee","EVXCOURSEFEEID = '" & Lookup.ID & "'")
                .Fields("ENDDATE").Value = GetField("ENDDATE","EVXCourseFee","EVXCOURSEFEEID = '" & Lookup.ID & "'")
                .Update
            End With
            Sender.Refresh
            MsgBox "Fees in Grid: " & Sender.Recordset.RecordCount
...

The MsgBox messages here appear to confirm that the recordcount in the Grid's recordset is updating, and the datagrid appears to visibly update, but no data is visible. I have also verified that the GetField() calls are indeed pulling out the expected values. I feel that there are probably a few small steps I am missing here, and any insight would be much appreciated. 

Code updated, sorry. I'd pasted across an earlier version that wasn't working at all. The above is what I have now, with the Column headings set to be what I expect, and rows apparently being added, but no data visible in the cells. 

Have you searched any of this forum for recordsets and data grids? I know I've posted a ton of stuff on this.

Basically get a recordset and set the grid to the recordset no need to .Refresh, no need to change SQL.Text

So

rs.Open

Set grid.recordset = rs

and that's it. (don't mess with SQL.TEXT, don't do a Grid.Refresh, don't close down the rs until you are done using the grid!)

The grid column names are automagically mapped to your recordset column names.....if the data is blank then you're not mapping the recordset column names to the grid column names.

That's why SLX does all of the A4_Account stuff......the Select Statement\recordset maps the WhateverTable.CompanyName field to A4_Account.....so you have a Grid column that refers to A4_Account and the data appears in your grid.

If I get a chance I'll post some actual code....have done this a few thousand times.....

Change your code to this 

    With objRS
        .Fields.Append "EVXCOURSEFEEID", adChar, 12, adFldIsNullable
        .Fields.Append "GRIDSELECT", adBoolean, , adFldIsNullable
        .Fields.Append "DESCRIPTION", adVarChar, 64, adFldIsNullable
        .Fields.Append "AMOUNT", adDouble, , adFldIsNullable
        .Fields.Append "STARTDATE", adDate, , adFldIsNullable
        .Fields.Append "ENDDATE", adDate, , adFldIsNullable
    End With
 
    objRS.Open
    Set objGrid.Recordset = objRS

    ' Remove any preexisting column settings

    If (objGrid.Columns.Count > 0) Then

        For iLoop = 0 to objGrid.Columns.Count - 1

            objGrid.Columns.Item(0).Delete

        Next

    End If