Solved

Using MS DataGrid in Microsoft Access 2002/2003

Posted on 2004-08-07
7
8,809 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

757 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

16 Experts available now in Live!

Get 1:1 Help Now