Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    58

    Unanswered: Populating Form with ADO Recordset

    I have a form which has the following code on its onopen event to get data from an external database. The code works in that it gets the data, however it will only display the first record and will not allow me to use the record selector buttons on the bottom of the page to scroll through all the other records. I am a complete novice with ADO so please tell me where I am going wrong.

    Code:
        Dim cnn As ADODB.Connection
        Dim fPupilData As New ADODB.Recordset
        
        Set cnn = New ADODB.Connection
        Set fPupilData = New ADODB.Recordset
        
        cnn.ConnectionString = "Dsn=INTACCESS;"
        cnn.Open
        
        fPupilData.ActiveConnection = cnn
        fPupilData.CursorLocation = adUseClient
        fPupilData.CursorType = adOpenStatic
        fPupilData.LockType = adLockReadOnly
        fPupilData.Open ("SELECT fStudent.StuSurname, fStudent.StuFirstName, fClass.ClsDesc, fStudent.StuSex, fStudent.StuDOB, fStudent.StuSENStage, fStudent.StuRecId FROM fStudent LEFT JOIN fClass ON fStudent.StuClsRecID=fClass.ClsRecID")
        fPupilData.MoveFirst
        Me.Surname = fPupilData.Fields("fStudent.StuSurname").Value
        Me.FirstName = fPupilData.Fields("fStudent.StuFirstName").Value
        Me.Class = fPupilData.Fields("fClass.ClsDesc").Value
        Me.Gender = fPupilData.Fields("fStudent.StuSex").Value
        Me.DOB = fPupilData.Fields("fStudent.StuDOB").Value
        Me.SENStage = fPupilData.Fields("fStudent.StuSENStage").Value
        Me.StuRecId = fPupilData.Fields("fStudent.StuRecId").Value
            
        cnn.Close
        Set cnn = Nothing
    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Your code only provides the first record because that is exactly what you have told it to do.

    The whole idea here is to obviously Bind a ADO Recordset to your form and therefore your Form must meet two general requirements so as to maintain updateability while it is bound to that ADO recordset. The general requirements are:

    - The underlying ADO recordset must be updateable via ADO.
    - The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.

    For general use in MS Access, you may be Binding your Form to data sources using the Microsoft SQL Server in which case the following code will carry out the task for you when placed into the OnOpen event of your Form:

    Note: Your Form's Record Source property must be blank.

    Code:
    Private Sub Form_Open(Cancel As Integer)
       Dim con As ADODB.Connection
       Dim rst As ADODB.Recordset
     
       ' Trap Errors...
       On Error GoTo Error_FormOpen
     
       ' Use the ADO connection that Access uses
       Set con = CurrentProject.AccessConnection
       ' Create an instance of the ADO Recordset class,
       ' and set its properties
       Set rst = New ADODB.Recordset
     
       With rst
    	  Set .ActiveConnection = con
    	  .Source = "SELECT fStudent.StuSurname, fStudent.StuFirstName, fClass.ClsDesc, fStudent.StuSex, fStudent.StuDOB, fStudent.StuSENStage, fStudent.StuRecId FROM fStudent LEFT JOIN fClass ON fStudent.StuClsRecID=fClass.ClsRecID"
    	  .LockType = adLockOptimistic
    	  .CursorType = adOpenKeyset
    	  .Open
       End With
     
    Exit_FormOpen:
       ' Set the form's Recordset property to the ADO recordset
       Set Me.Recordset = rst
       Set rst = Nothing
       Set con = Nothing
       Exit Sub
     
    Error_FormOpen:
       ' Display if encountered...
       MsgBox Err.Description, vbCritical
       Resume Exit_FormOpen
    End Sub



    For Binding your Form to data sources using a Jet provider, you may use this code within the OnOpen Event of your Form. Allthough it looks very similar to the code above you will notice the addtion of the CursorLocation property:






    Note: Your Form's Record Source property must be blank.



    Code:
    Private Sub Form_Open(Cancel As Integer)
    Dim con As ADODB.Connection Dim rst As ADODB.Recordset ' Trap Errors... On Error GoTo Error_FormOpen ' Use the ADO connection that Access uses Set con = CurrentProject.AccessConnection ' Create an instance of the ADO Recordset class, ' and set its properties Set rst = New ADODB.Recordset With rst Set .ActiveConnection = con .Source = "SELECT fStudent.StuSurname, fStudent.StuFirstName, fClass.ClsDesc, fStudent.StuSex, fStudent.StuDOB, fStudent.StuSENStage, fStudent.StuRecId FROM fStudent LEFT JOIN fClass ON fStudent.StuClsRecID=fClass.ClsRecID" .LockType = adLockOptimistic .CursorType = adOpenKeyset .CursorLocation = adUseServer .Open End With Exit_FormOpen: ' Set the form's Recordset property to the ADO recordset Set Me.Recordset = rst Set rst = Nothing Set con = Nothing Exit Sub Error_FormOpen: ' Display if encountered... MsgBox Err.Description, vbCritical Resume Exit_FormOpen End Sub



    For Binding your Form to data sources using ODBC, or Oracle OLE DB providers, you need to use altogether different code.

    The first code example should fullfill your needs. However, you will run into difficulties if your SQL string used in the ADO Source property is flawed.

    Hope this helps.....












    .
    Last edited by CyberLynx; 10-30-05 at 15:19.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •