set Rs problem

[fredy]

my problem is in the Set RS
if i type :
Set RS = DB.OpenRecordset("select * FROM tbl_companies where [company]='Bombardier'" , dbOpenDynaset)
it works fine
but i want it to fetch the current company
so i type :
Set RS = DB.OpenRecordset("select * FROM tbl_companies where [company]=" & comp, dbOpenDynaset)
and it gives me an error on that line
what am i doing wrong?

heres my code

Private Sub Form_Current()
Dim RS As DAO.Recordset
Dim DB As DAO.Database
Dim ressourcerie1 As String

comp= Me![company].Value
Set DB = CurrentDb
Set RS = DB.OpenRecordset("select * FROM tbl_companies where [company]=" & comp, dbOpenDynaset)


If RS.RecordCount = 0 Then Exit Sub
Check0 = False: Check2 = False: Check4 = False
While Not RS.EOF
Select Case RS("Matières recupérées")
Case "bombardier"
Check0 = True
Case "air canada"
Check2 = True
Case "nortel"
Check4 = True
End Select
RS.MoveNext
Wend
RS.Close

End Sub

 

[Tim K.]

Change this line from

Set RS = DB.OpenRecordset("select * FROM tbl_companies where [company]=" & comp, dbOpenDynaset)

to

Set RS = DB.OpenRecordset("select * FROM tbl_companies where [company]='" & comp & "'", dbOpenDynaset)