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

    Unanswered: ADO recordset returning identical records

    I have the following code on a forms onopen event, the recordset appears to open with no errors and returns the correct number of records according to the forms record navigation buttons (in this case 861) however all 861 records contain exactly the same data, ie: the first record. I am using the forms record navigation buttons to move through the records. Please tell me where I am going wrong.

    Code:
        Dim cnn As ADODB.Connection
        Dim fPupilData As ADODB.Recordset
        Dim strSQL As String
        
        Set cnn = New ADODB.Connection
        Set fPupilData = New ADODB.Recordset
        
        cnn.ConnectionString = "Dsn=INTACCESS;"
        cnn.Open
        
        strSQL = "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 WHERE fStudent.StuRollStatus = 'C'"
        
        fPupilData.CursorLocation = adUseClient
        fPupilData.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
     
        Set Me.Recordset = fPupilData
        Me.Surname = fPupilData.Fields("fStudent.StuSurname")
        Me.FirstName = fPupilData.Fields("fStudent.StuFirstName")
        Me.Class = fPupilData.Fields("fClass.ClsDesc")
        Me.Gender = fPupilData.Fields("fStudent.StuSex")
        Me.DOB = fPupilData.Fields("fStudent.StuDOB")
        Me.SENStage = fPupilData.Fields("fStudent.StuSENStage")
        Me.StuRecId = fPupilData.Fields("fStudent.StuRecId")
              
        Set fPupilData.ActiveConnection = Nothing
        cnn.Close
        Set cnn = Nothing
        
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are only populating the controls once, so now matter how many times you navigate through the recorset the controls are not refreshed - you wll always see the same data.

    suggest you move your control refresh / paint to the forms on current event instead

  3. #3
    Join Date
    Jan 2004
    Posts
    58
    I have tried changing it so the text boxes requery using the oncurrent event but the same things happens.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the code you are populating the controls from
    are you populating fromt eh recordset or the ado connection object

  5. #5
    Join Date
    Oct 2005
    Location
    Hertfordshire, UK
    Posts
    9
    Are the controls on your form bound to the respective recordset fields? If not, your code sets the controls once to the first record of the recordset. Thereafter, although you may step through the recordset, if the controls are not bound to the set, the control values will not be refreshed.

  6. #6
    Join Date
    Jan 2004
    Posts
    58
    Thanks for the help, I have finaly managed to find some code that seems to work correctly.

    Code entered in to new Module:

    Code:
    Public Function fPupilData() As ADODB.Recordset
        Dim cnn As New ADODB.Connection
        Dim strSQL As String
        Set fPupilData = New ADODB.Recordset
        cnn.ConnectionString = "Dsn=INTACCESS;"
        cnn.Open
        strSQL = "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 WHERE fStudent.StuRollStatus = 'C'"
        fPupilData.CursorLocation = adUseClient
        fPupilData.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        Set fPupilData.ActiveConnection = Nothing
        cnn.Close
        Set cnn = Nothing
    End Function
    Code entered in to the forms onopen event:

    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Set Me.Recordset = fPupilData()
        Me.Surname.ControlSource = Me.Recordset.Fields(0).Name
        Me.FirstName.ControlSource = Me.Recordset.Fields(1).Name
        Me.Class.ControlSource = Me.Recordset.Fields(2).Name
        Me.Gender.ControlSource = Me.Recordset.Fields(3).Name
        Me.DOB.ControlSource = Me.Recordset.Fields(4).Name
        Me.SENStage.ControlSource = Me.Recordset.Fields(5).Name
        Me.StuRecId.ControlSource = Me.Recordset.Fields(6).Name
    
    End Sub

Posting Permissions

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