Hi all,

I'm trying to link two tables in a MDB. Tables student and course, the corresponding field is coursecode

My problem is I keep getting an error on the following lines. When I remark the lines the program works.
The message is Run Time Error '3265''Item cannot be found in the collection corresponding to the requested name or ordinal.

Lines where error occur

cboCourse.Text = rsStudent!coursecode
txtCourseTitle.Text = rsCourse!Title
txtDuration.Text = rsCourse!duration


The entire code is

Dim cn As ADODB.Connection
Dim rsStudent As ADODB.Recordset
Dim rsCourse As ADODB.Recordset


'Define the ADODB Connection

Option Explicit


Private Sub Form_Load()

Dim sqlStudent, sqlCourse As String

Me.Left = 0
Me.Top = 0
Me.Height = Screen.Height
Me.Width = Screen.Width

Set cn = New ADODB.Connection


Set rsStudent = New ADODB.Recordset 'this will be the STUDENT
Set rsCourse = New ADODB.Recordset 'this will be the COURSE

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\Austins\Desktop\VB Assignment III\college.mdb"
cn.Open '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\Austins\Desktop\VB Assignment II\college.mdb"

sqlStudent = "Select * From student, course where student.coursecode = course.coursecode" 'Get all records from tblStudent and link through course code
rsStudent.Open sqlStudent, cn, adOpenStatic, adLockBatchOptimistic, adCmdText

If rsStudent.EOF = False Then
txtFirstname.Text = rsStudent!firstname
txtSurname.Text = rsStudent!surname
cboCourse.Text = rsStudent!coursecode
txtStudentID.Text = rsStudent!studentID
txtAddress1.Text = rsStudent!address1
txtAddress2.Text = rsStudent!address2
txtAddress3.Text = rsStudent!address3
txtAddress4.Text = rsStudent!address4
txtCourseTitle.Text = rsCourse!Title
txtDuration.Text = rsCourse!duration


Else

End If


End Sub


Can anyone spot my mistake.
Thanks Lawdie

hi ya

i had the same run-time error. the reason was that i tried to execute dbs.Fields("someColumn").Value, without having executed

dbs.Open "SELECT someColumn " & _
"FROM ParamVarInput WHERE True", _
CurrentProject.Connection

i hope this helps

Regards

g

cn.Open '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\Austins\Desktop\VB Assignment II\college.mdb"

You put a ' before the quote, which cancels out the entire file path... might not cause the runtime error/might have been intended, just pointing it out of thin air. :P