Solved

Using MS DataGrid in Microsoft Access 2002/2003

Posted on 2004-08-07
7
8,827 Views
Last Modified: 2012-08-13
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
Comment
Question by:wenyonk
7 Comments
 
LVL 18

Accepted Solution

by:
Data-Man earned 250 total points
ID: 11744482
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
 
LVL 34

Expert Comment

by:flavo
ID: 11744758
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
 

Author Comment

by:wenyonk
ID: 11746167
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
 
LVL 3

Expert Comment

by:realrael
ID: 11749681

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
 
LVL 34

Assisted Solution

by:flavo
flavo earned 250 total points
ID: 11750109
>>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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now