R
RWise
I have a subform which gets its data from an external recordset using ODBC,
this on its own works fine allowing me to manually scroll through all the
records. However when I came to link the subform to the main form (which also
gets its data from a recordset) 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?
The ADO code used to create the recordsets is listed below:
Code created in separate module:
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:
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:
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?
this on its own works fine allowing me to manually scroll through all the
records. However when I came to link the subform to the main form (which also
gets its data from a recordset) 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?
The ADO code used to create the recordsets is listed below:
Code created in separate module:
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:
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:
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?