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

    Unanswered: Linking Subform to form when using ADO Recordset

    I have a subform which gets its data from an ADO recordset which on its own works fine allowing me to manually scroll through all the records. However when I came to link the subform to a main form using the link child and master fields for the subform the subform does not change when the main form is changed. The subform is set to requery when the main form is changed and the linking fields contain the same data. To test I imported the subform data in to a new table, bound the form to this table and set up the linking fields and this time it worked how it should. Any ideas why it won't work with the ADO recordset?

  2. #2
    Join Date
    Jan 2004
    Posts
    58
    I am still having this problem, any ideas why the link child/master fields aren't working with ADO recordsets, or is there a work around I can use to get the same effect.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm guessing - its the same basic problem you had with your other recent posting.

    I'd check to make ceetain that the link master & child fields are pointing to the correct current values - ie the values you are navigating.

    probably the easiest is to, temporarily display what your link fields are and compare them to what they should be.

  4. #4
    Join Date
    Jan 2004
    Posts
    58
    Looking at the link fields they appear identical (all are numerical and automatically appear right justified so they are being recognised as a number).

    The ADO code used to create the recordsets is listed below:

    Code created in separate 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, fClass WHERE fClass.ClsRecID=fStudent.StuClsRecID AND fStudent.StuRollStatus = 'C'"
        fPupilData.CursorLocation = adUseClient
        fPupilData.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        Set fPupilData.ActiveConnection = Nothing
        cnn.Close
        Set cnn = Nothing
    End Function
    
    Public Function fKeyStage() As ADODB.Recordset
        Dim cnn As New ADODB.Connection
        Dim strSQL As String
        Set fKeyStage = New ADODB.Recordset
        cnn.ConnectionString = "Dsn=INTACCESS;"
        cnn.Open
        strSQL = "SELECT fKeyStage.KSStuRecID, fKeyStage.KS2Year, fKeyStage.KS2_ENG_TT_SUB_NL, fKeyStage.KS2_MAT_TT_SUB_NL, fKeyStage.KS2_SCI_TT_SUB_NL, fKeyStage.KS3Year, fKeyStage.KS3_ENG_TT_SUB_NL, fKeyStage.KS3_MAT_TT_SUB_NL, fKeyStage.KS3_SCI_TT_SUB_NL, fStudent.StuRollStatus FROM fKeyStage, fStudent WHERE fKeyStage.KSStuRecID=fStudent.StuRecId AND fStudent.StuRollStatus = 'C'"
        fKeyStage.CursorLocation = adUseClient
        fKeyStage.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        Set fKeyStage.ActiveConnection = Nothing
        cnn.Close
        Set cnn = Nothing
    End Function
    Code attached to mainforms onopen event:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Set Me.Recordset = fPupilData()
        Set Me.KS2_subfrm.Form.Recordset = fKeyStage()
    
    End Sub

    I have also tried using a DAO recordset instead, with the same results:

    Code attached to mainforms onopen event:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim db As DAO.Database
        Dim fPupilData As DAO.Recordset
        Dim fKeyStage As DAO.Recordset
    
        Set db = OpenDatabase("", False, True, "ODBC;Dsn=INTACCESS;")
    
        Set fPupilData = db.OpenRecordset("SELECT fStudent.StuSurname, fStudent.StuFirstName, fClass.ClsDesc, fStudent.StuSex,fStudent.StuDOB, fStudent.StuSENStage, fStudent.StuRecId FROM fStudent, fClass WHERE fClass.ClsRecID=fStudent.StuClsRecID AND fStudent.StuRollStatus = 'C'"
        Set fKeyStage = db.OpenRecordset("SELECT fKeyStage.KSStuRecID, fKeyStage.KS2Year, fKeyStage.KS2_ENG_TT_SUB_NL, fKeyStage.KS2_MAT_TT_SUB_NL, fKeyStage.KS2_SCI_TT_SUB_NL, fKeyStage.KS3Year, fKeyStage.KS3_ENG_TT_SUB_NL, fKeyStage.KS3_MAT_TT_SUB_NL, fKeyStage.KS3_SCI_TT_SUB_NL, fStudent.StuRollStatus FROM fKeyStage, fStudent WHERE fKeyStage.KSStuRecID=fStudent.StuRecId AND fStudent.StuRollStatus = 'C'")
        
        Set Me.Recordset = fPupilData
        Set Me.KS2_subfrm.Form.Recordset = fKeyStage
    
    End Sub

    In both cases the forms recordsource's are unbound, but the controls on the forms are bound using the same field names the recordsets use. Both types of code return their data correctly, ie: same number of records and the link fields contain the same data in the same order and apparently in the same format. The correct data is displayed in each field on both the main form and subform and I can scroll through all the records on each form using the navigation buttons at the bottom of each form, but I cannot get them to synchronise with each other so the subform changes when the mainforms current record is changed.

    Just to clarify (although I have tried it all possible ways), the link master and child fields are to be filled in using the forms control name, not the recordsets field name, and child = subform, master = mainform. Is this correct?

    Any ideas on where I am going wrong?

    Please note, I am a bodger when it comes to writing code, I have limited knowledge of what each part of the code actually means, I just tend to copy pre created code from other people and poke at it blindly until it does what I want it to. To this end please describe any advice in detail to aid my understanding, which is limited at the best of times, and is seriously degraded after using MSAccess for long periods

    Thanks

  5. #5
    Join Date
    Jan 2004
    Posts
    58
    Anyone?

    If I can't link them the usual way is there an alternative way I can try? Idealy I want to modify the existing recordsets without having to reconnect to the data source (which is slow) each time the mainform changes.

  6. #6
    Join Date
    Jan 2004
    Posts
    58
    One last time, any ideas?

Posting Permissions

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