Compile Error on Module Function 1

[BV874]

Hello,
I have the following code that gives me a compile error: invalid use of new keyword. I have a form that reps can edit if they are in the table tblHOUSING_REPS. They can edit the contents of the form by clicking an edit button. When they do that I get the compile error which higlights the FUNCTION line in yellow and DIM RS As New Recordset in gray. I have checked my spelling all over and cannot find any errors.

Function InHOUSING(Value As Variant) As Boolean
Dim Good As Boolean
Dim User
Dim MySQL As String
Dim RS As New Recordset

User = fOSUserName()
Good = False

MySQL = "select [HOUSING_REP] from [tblHOUSING_REPS] where [HOUSING_REP] > '' order by [HOUSING_REP]"
RS.Open MySQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
If Not RS.EOF Then RS.MoveFirst
While Not RS.EOF
If User = Trim(RS.Fields("HOUSING_REP")) Then
Good = True
RS.MoveNext
Else
RS.MoveNext
End If
Wend
InHOUSING = Good

End Function

 

[PHV]

Have you tried this ?
Dim RS As New DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, I meant this:
Dim RS As New ADODB.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BV874]

Thanks PHV! That worked perfectly!