[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8919
  • 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
        .Open
    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
    dgClaimsManagement.Requery

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,

WenyonK
0
wenyonk
Asked:
wenyonk
2 Solutions
 
Data-ManCommented:
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.

Thanks,
Mike
0
 
flavoCommented:
I know with DAO you had to "populate" the rs  before it "knew" how many records were in there

Adding
rstClaimsManagement.Movefirst
rstClaimsManagement.Movelast

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.

Dave
0
 
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.
0
 
realraelCommented:

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

    Set dgClaimsManagement.DataSource = rstClaimsManagement
    dgClaimsManagement.Requery

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
0
 
flavoCommented:
>>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.

done.

Dave
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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