• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8936
  • Last Modified:

Using MS DataGrid in Microsoft Access 2002/2003

I am tyring to use the Microsoft DataGrid Control on a MS Access 2002 or 2003 Database.

I have included the reference: Microsoft DataGrid Control 6.0 (SP5) (OLEDB)

In My form I placed a MS DataGrid named: dgClaimsManagement   its Data properties are as follows:
OLE CLASS: DataGrid    
CLASS:  MSDataGridLib.DataGrid.1

' Module Level Variables:

' Declare Global Variables
Dim strAppPath As String
Dim strConnection As String
Dim strCMSQLStatement As String
' Create RMSTrac Connection Object.
Dim cnRMSTrac As ADODB.Connection
' Create Claims Management Connection and Recordset objects.
Dim rstClaimsManagement As ADODB.Recordset

' Form Load Method.
Private Sub Form_Load()

' Initialize global work variables
    strDBPath = Application.CurrentProject.Path
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & "\USIRiskManagementServices.mdb;Persist Security Info=False"

    ' Instantiation of New RMSTrac Connection Object.
    Set cnRMSTrac = New ADODB.Connection
    With cnRMSTrac
        .ConnectionTimeout = 15
        .CommandTimeout = 30
        .CursorLocation = adUseClient
        .ConnectionString = strConnection
    End With
    ' Instantiation of New Claims Management Recordset.
    Set rstClaimsManagement = New ADODB.Recordset
    ' SQL Statement for Claims Management Service Providers.
    strCMSQLStatement = "SELECT tblEmployeeMaster.Name, " & _
        "Sum(tblServiceSummaryMaster.TravelTime) AS SumOfTravelTime, " & _
        "Sum(tblServiceSummaryMaster.ConsultTime) AS SumOfConsultTime, " & _
        "Sum(tblServiceSummaryMaster.ReportTime) AS SumOfReportTime, " & _
        "Sum(tblServiceSummaryMaster.AdminTime) AS SumOfAdminTime, " & _
        "Sum([TravelTime]+[ConsultTime]+[ReportTime]+[AdminTime]) AS TotalTime " & _
        "FROM tblEmployeeMaster " & _
        "LEFT JOIN tblServiceSummaryMaster " & _
        "ON tblEmployeeMaster.tblEmployeeInternalReference = tblServiceSummaryMaster.lkupEmployeeID " & _
        "WHERE (((tblServiceSummaryMaster.ServiceType)=0) AND ((tblServiceSummaryMaster.ServiceDate) Between Date() And (Date()-90))) " & _
        "GROUP BY tblEmployeeMaster.Name " & _
        "ORDER BY tblEmployeeMaster.Name;"
    ' Initialze and Fill the Claims Management Recordset
    rstClaimsManagement.Open strCMSQLStatement, cnRMSTrac
    MsgBox rstClaimsManagement.RecordCount, vbInformation, "Claims Management"
    ' Populate the Claims Management Service Provider Data in the Claims Management DataGrid
    Set dgClaimsManagement.DataSource = rstClaimsManagement

End Sub

Currently the DataGrid is not populating with data,   The rstClaimsManagement.RecordCount shows 1 record.

Please help!!  500 points will be awarded for a complete and functioning answer.

Kindest Regards,

2 Solutions
Not trying to sound critical, but why are you using a grid control?  You could build a form with the fields on it.  Set it to display in datasheet view and make it a sub form.  You can still use your ADO recordset to set the data source of the subform

Set Me.sfmSubFormName.Form.Recordset = rstClaimsManagement

No need to requery after assigned the recordset

I haven't heard of someone using the datagrid in Access.  We have that intrinsic functionaltiy in the forms themselves.

I know with DAO you had to "populate" the rs  before it "knew" how many records were in there


just before this line

MsgBox rstClaimsManagement.RecordCount, vbInformation, "Claims Management"

Will show the correct record count.

As Mike (data-man) said, why are you trying to use a datagird control, the form controls will do the same thing, just line the textboxes up to look like a dg.

wenyonkAuthor Commented:
I wanted to use a datagrid because I am having trouble with  the data sheet view not auto-sizing in height based on the number of rows returned.  I have the autosize property set to yes and the Can Shrink and Can Grow Properties set to yes for both the subform and subform container.

I can not use a row of textboxes because the number of rows will vary.

i'm a little skeptic about this part of your code:

    Set dgClaimsManagement.DataSource = rstClaimsManagement

i don't think datagrids have a .Requery method. i think that's for the recordset. if you need to refresh your datagrid, use .Refresh.

- rael
>>I can not use a row of textboxes because the number of rows will vary.

Sure you can, use a continuos form (Right click the outside of the deatil section, properties and select Continuos from the "Defaul View" property.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now