I recently had a project where I had to configure an account-level datagrid which displayed a list of related opportunities. Aside from standard opportunity level fields, I also needed to include a simple count of products for each opportunity.
In order to include the product count, I used a relatively simple process to define and populate the datagrid programatically. There are basically two steps: First I used a recordset to define the columns that would be displayed in the datagrid. Then, I wrote a SQL statement and set the datagrids recordset property to display the data returned from that SQL Statement.
- Step 1: While you could technically skip this step, you need to configure the columns in the datagrid in order to apply specific formatting to various datatypes returned in the SQL Statement.
Dim objRS, i, col, strSql
First, I created a blank recordset containing the fields I needed in the datagrid
Set objRS = Application.CreateObject(“ADODB.Recordset”)
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockOptimistic
Fields are added using the syntax: .Fields.Append {fieldname}, {datatype}, {fieldlength}. For the text, numeric and currency field I used the standard datatypes. For dates, it was easier to format as date only by configuring them as 8 character varchar fields.
With objRS
This code is required to strip out the columns for the grid prior to reading them. The datagrid in this example is named “dgOpportunities”.
If (dgOpportunities.Columns.Count > 0) Then
End If
Now I go through and add each column to the columns collection of the datagrid. This is where formatting is defined. Each column is defined with an integer value which defines what type of data is displayed. Any column where you want to use the FormatType and FormatString properties, you need to define the column as a Text column. Without defining the width property of each column, each column’s length is determined by the length of the column returned in the SQL Statement.
The column for OpportunityID was needed to allow me to double click a row in the grid to move to the opportunity. Columns would be visible by default, so for this column I set the Visible property to False.
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “OPPORTUNITIYID”
col.Visible = False
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “DESCRIPTION”
col.Caption = “Name”
col.Width = 150
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “STATUS”
col.Caption = “Status”
col.width = 75
Currency fields can be set easily by defining the column’s datatype as currency (Integer value 13)
Set col = dgOpportunities.Columns.Add(13)
col.FieldName = “SALESPOTENTIAL”
col.Caption = “Sales Potential”
col.Width = 100
Since I wanted to display the date only, I returned the columns as text (0) and set the FormatType = 3 (DateTime) and the FormatString to “ddddd”.
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “DATEOPENED”
col.Caption = “Opened”
col.FormatType = 3
col.FormatString = “ddddd”
col.Width = 75
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “ESTIMATEDCLOSE”
col.Caption = “Est. Close Date”
col.FormatType = 3
col.FormatString = “ddddd”
col.Width = 75
The # of Products was set as a text field as well, setting the format type = 2 (Integer)
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “NUMPRODUCTS”
col.Caption = “# Products”
col.FormatType = 2
Finally, I set the grids recordset property to the newly defined blank recordset.
dgOpportunities.Recordset = objRS
First, I created a blank recordset containing the fields I needed in the datagrid
Set objRS = Application.CreateObject(“ADODB.Recordset”)
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockOptimistic
Fields are added using the syntax: .Fields.Append {fieldname}, {datatype}, {fieldlength}. For the text, numeric and currency field I used the standard datatypes. For dates, it was easier to format as date only by configuring them as 8 character varchar fields.
With objRS
.Fields.Append “OPPORTUNITYID”, adVarChar, 12
.Fields.Append “DESCRIPTION”, adVarChar, 64
.Fields.Append “STATUS”, adVarChar, 64
.Fields.Append “SALESPOTENTIAL”, adCurrency, 12
.Fields.Append “DATEOPENED”, adVarChar, 8
.Fields.Append “ESTIMATEDCLOSE”, adVarChar, 8
.Fields.Append “NUMPRODUCTS”, adDouble, 4
End With.Fields.Append “DESCRIPTION”, adVarChar, 64
.Fields.Append “STATUS”, adVarChar, 64
.Fields.Append “SALESPOTENTIAL”, adCurrency, 12
.Fields.Append “DATEOPENED”, adVarChar, 8
.Fields.Append “ESTIMATEDCLOSE”, adVarChar, 8
.Fields.Append “NUMPRODUCTS”, adDouble, 4
This code is required to strip out the columns for the grid prior to reading them. The datagrid in this example is named “dgOpportunities”.
If (dgOpportunities.Columns.Count > 0) Then
For i = 0 to dgOpportunities.Columns.Count – 1
dgOpportunities.Columns.Item(0).Delete
NextEnd If
Now I go through and add each column to the columns collection of the datagrid. This is where formatting is defined. Each column is defined with an integer value which defines what type of data is displayed. Any column where you want to use the FormatType and FormatString properties, you need to define the column as a Text column. Without defining the width property of each column, each column’s length is determined by the length of the column returned in the SQL Statement.
The column for OpportunityID was needed to allow me to double click a row in the grid to move to the opportunity. Columns would be visible by default, so for this column I set the Visible property to False.
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “OPPORTUNITIYID”
col.Visible = False
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “DESCRIPTION”
col.Caption = “Name”
col.Width = 150
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “STATUS”
col.Caption = “Status”
col.width = 75
Currency fields can be set easily by defining the column’s datatype as currency (Integer value 13)
Set col = dgOpportunities.Columns.Add(13)
col.FieldName = “SALESPOTENTIAL”
col.Caption = “Sales Potential”
col.Width = 100
Since I wanted to display the date only, I returned the columns as text (0) and set the FormatType = 3 (DateTime) and the FormatString to “ddddd”.
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “DATEOPENED”
col.Caption = “Opened”
col.FormatType = 3
col.FormatString = “ddddd”
col.Width = 75
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “ESTIMATEDCLOSE”
col.Caption = “Est. Close Date”
col.FormatType = 3
col.FormatString = “ddddd”
col.Width = 75
The # of Products was set as a text field as well, setting the format type = 2 (Integer)
Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “NUMPRODUCTS”
col.Caption = “# Products”
col.FormatType = 2
Finally, I set the grids recordset property to the newly defined blank recordset.
dgOpportunities.Recordset = objRS
- Step 2: In order to get the data into the grid, I simply build a SQL Statement to return the required data, and applied that to the Recordset property of the grid as well.
strSql = “SELECT O.OPPORTUNITYID, O.DESCRIPTION, O.STATUS, ” & _
“O.SALESPOTENTIAL, O.DATEOPENED, O.ESTIMATEDCLOSE, ” & _
“COUNT(OP.OPPPRODUCTID) AS NUMPRODUCTS O.MODIFYDATE ” & _
“FROM OPPORTUNITY O LEFT OUTER JOIN OPPORTUNITY_PRODUCT OP ” & _
“ON (O.OPPORTUNITYID = OP.OPPORTUNITYID) WHERE ” & _
“O.ACCOUNTID = ‘” & Application.BasicFuntions.CurrentAccountID & “‘ ” & _
“GROUP BY O.OPPORTUNITYID, O.DESCRIPTION, O.STATUS, ” & _
“O.SALESPOTENTIAL, O.DATEOPENED, O.ESTIMATEDCLOSE, ” & _
“O.MODIFYDATE ORDER BY O.MODIFYDATE DESC”
“COUNT(OP.OPPPRODUCTID) AS NUMPRODUCTS O.MODIFYDATE ” & _
“FROM OPPORTUNITY O LEFT OUTER JOIN OPPORTUNITY_PRODUCT OP ” & _
“ON (O.OPPORTUNITYID = OP.OPPORTUNITYID) WHERE ” & _
“O.ACCOUNTID = ‘” & Application.BasicFuntions.CurrentAccountID & “‘ ” & _
“GROUP BY O.OPPORTUNITYID, O.DESCRIPTION, O.STATUS, ” & _
“O.SALESPOTENTIAL, O.DATEOPENED, O.ESTIMATEDCLOSE, ” & _
“O.MODIFYDATE ORDER BY O.MODIFYDATE DESC”
Set dgOpportunities.Recordset = Application.GetNewConnection.Execute(strSql)
That’s it! You should now have a properly formatted, programmatically defined datagird.
I don’t think you will be able to set the Grid to Rowselect = True and have the Hyperlink work. You’ll have to leave the Rowselect false, and set the Readonly property of each column that you add to False to prevent editing of individual cells.
Add this line to each column as you define them…
col.Readonly = “False”