Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    OpenRecordset (2003/2007)

    The following code bombs out with the message:runtime error 2466.
    If I just open the recordset without specifying dynaset I get: error 3464 data type mismatch in expression
    Any ideas?
    Thanks and great to be back

    strSql = "SELECT tblPhoneEmail.TypeID, tblStudents.txtLastName, tblYear.txtYear, tblPhoneEmail.txtInfo FROM (tblStudents LEFT JOIN tblPhoneEmail ON tblStudents.StudentID = tblPhoneEmail.StudentId) RIGHT JOIN (tblYear RIGHT JOIN tblStudentYear ON (tblYear.YearId = tblStudentYear.lnkIdYearStud) AND (tblYear.YearId = tblStudentYear.lnkIdYearStud)) ON tblStudents.StudentID = tblStudentYear.IdStudent WHERE (((tblPhoneEmail.TypeID) = 5) And ((tblYear.txtYear) =" & [Forms]![frmMAin]![list] & "))ORDER BY tblStudents.txtLastName;"

    Debug.Print strSql
    Set rst = CurrentDb.OpenRecordset(strSql, Dynaset)

    This is the SQL that I get which works:
    SELECT tblPhoneEmail.TypeID, tblStudents.txtLastName, tblYear.txtYear, tblPhoneEmail.txtInfo FROM (tblStudents LEFT JOIN tblPhoneEmail ON tblStudents.StudentID = tblPhoneEmail.StudentId) RIGHT JOIN (tblYear RIGHT JOIN tblStudentYear ON (tblYear.YearId = tblStudentYear.lnkIdYearStud) AND (tblYear.YearId = tblStudentYear.lnkIdYearStud)) ON tblStudents.StudentID = tblStudentYear.IdStudent WHERE (((tblPhoneEmail.TypeID) = 5) And ((tblYear.txtYear) =2008)) Or (((tblPhoneEmail.TypeID) = 6) And ((tblYear.txtYear) = 2008)) Or (((tblPhoneEmail.TypeID) = 7) And ((tblYear.txtYear) =2008))ORDER BY tblStudents.txtLastName

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordset (2003/2007)

    What happens if you use

    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

    i.e. dbOpenDynaset instead of just Dynaset, which is not a defined constant?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OpenRecordset (2003/2007)

    still getting error 3464. (BTW I had used dbopendynaset- just forgot to type that)
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordset (2003/2007)

    The instructions that set strSQL can be simplified to
    <code>
    strSQL = "SELECT tblPhoneEmail.TypeID, tblStudents.txtLastName, tblYear.txtYear, tblPhoneEmail.txtInfo " & _
    "FROM (tblStudents LEFT JOIN tblPhoneEmail ON tblStudents.StudentID = tblPhoneEmail.StudentId) " & _
    "RIGHT JOIN (tblYear RIGHT JOIN tblStudentYear ON tblYear.YearId = tblStudentYear.lnkIdYearStud) " & _
    "ON tblStudents.StudentID = tblStudentYear.IdStudent WHERE tblPhoneEmail.TypeID In (5,6,7) And " & _
    "tblYear.txtYear = " & [Forms]![frmMain]![list] & Chr(34) & "ORDER BY tblStudents.txtLastName"</code>

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OpenRecordset (2003/2007)

    it was the Char34 that did it. That was dumb!

Posting Permissions

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