[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Using MS DataGrid in Microsoft Access 2002/2003

Posted on 2004-08-07
7
Medium Priority
?
8,909 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 18

Accepted Solution

by:
Data-Man earned 1000 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

650 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