Why there is an error in my Sub in VBA

[rslowin]

I have three tables



TStudents ( idStudent PK, Name, Languages )

TALanguagesStudents( idStudent, idLanguage )

TLanguages ( idLanguage PK, Language)



I need to concatenate all languages known by a Student in a field
TStudents.Languages



Here is code in VBA



Sub pre()



Dim dbs As Database

Dim rst, rst0, rst1 As Recordset

Dim ids, idl, str_pom As String



Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("TStudents")

With rst

.MoveFirst

While Not .EOF

str_pom = ""

ids = !idStudent

Set rst0 = dbs.OpenRecordset("SELECT * FROM TALanguagesStudents
WHERE idStudent = " & Trim(Str(ids)))

With rst0

.MoveFirst

While Not .EOF

idl = !idLanguage

Set rst1 = dbs.OpenRecordset("SELECT
TLanguages.Language FROM TLanguages INNER JOIN
TALanguagesStudents ON TLanguages.idLanguage =
TALanguagesStudents.idLanguage WHERE
(((TALanguageStudents.idStudent)=" & Trim(Str(ids)) &
") AND ((TALanguageStudents.idLanguage)=" &
Trim(Str(idl)) & "))")

str_pom = str_pom + " " + rst1!Language

.MoveNext

Wend

End With

.Edit

!Languages = str_pom

.Update

.MoveNext

Wend

End With



End Sub



The problem is in a line Set rst1 = dbs......



An error #13 Type mismatch



Can you help me, please.



Regards



Rafi

 

[SteveS]

Rafi,

The problem is in a line Set rst1 = dbs......

An error #13 Type mismatch

You didn't say but I would guess you are using A2K or XP
in which case you need to set a reference to the

Microsoft DAO 3.6 Object Library

(Alt-F11, Tools/References, scroll down to Microsoft area
and put a check in front of the DAO 3.6 line)

Dim rst, rst0, rst1 As Recordset

Dim ids, idl, str_pom As String

Two things here...
rst and rst0 are defined as type Variant. Change this line
to

Dim rst As DAO.Recordset, rst0 As DAO.Recordset, rst1 As
DAO.Recordset


(ids and idl are also of type Variant, not long)

-----------
Other things I noticed:

While Not .EOF

EOF for *which* recordset??? Change to: While Not rst.EOF
or While Not rst0.EOF.


-----------

Every recordset you open, you should close:

rst.Close
Set rst = Nothing

rst0.Close
Set rst0 = Nothing

---------------

ids = !idStudent
Set rst0 = dbs.OpenRecordset("SELECT * FROM TALanguagesStudents
WHERE idStudent = " & Trim(Str(ids)))

It appears that !idStudent is numeric and that ids is a
number. Then you convert it to a string?? To find a
numeric student Id??????
--------------

Set rst1 = dbs.OpenRecordset("SELECT
TLanguages.Language FROM TLanguages INNER JOIN
TALanguagesStudents ON TLanguages.idLanguage =
TALanguagesStudents.idLanguage WHERE
(((TALanguageStudents.idStudent)=" & Trim(Str(ids)) &
") AND ((TALanguageStudents.idLanguage)=" &
Trim(Str(idl)) & "))")

Same thing for the WHERE part for both ids and idl
(numeric compared to string).

And this is in the wrong place. As is, you would reopen
the recordset every loop. I don't know where the current
record would be, but you wouldn't loop thru the recordset.

HTH

Good luck

Steve