Invalid use of New keyword error???

[spinkung]

Hi all,

I'm trying to get a field value through a DAO.Recordset but keep getting and error message when i load the form.

the error is : Invalid use of New keyword

..and the code stops where i've highlighted in red.

Dim str1 As String
[COLOR="Red"]Dim rs As New DAO.Recordset[/COLOR]Dim db As Database
Set db = CurrentDb

lby = Mid(Me.refNo.Value, 4, 3)


str1 = "SELECT dbo_tbl_RetRANRequest_USERS.userName " & _
    "FROM dbo_tbl_RetRANRequest_USERS " & _
    "WHERE dbo_tbl_RetRANRequest_USERS.userID = " & lby & ""
    
    Set rs = db.OpenRecordset(str1, dbOpenDynaset, dbSeeChanges)
    
    Me.logBy.Value = db.Fields("userName").Value

Anyone help?

Cheers,
Spinkung

 

[gemma-the-husky]

just

dim rs as recordset

you are setting the openrecordset lower down

 

[modest]

You don't need the New key word and you should be more explicit when definining your objects.

Dim str1 As String
Dim rs As DAO.Recordset
Dim db As [b]DAO.[/b]Database

New is used for auto-instancing, which is not necessary in this case, since you are initializing the variable with the Set statement.

 

[spinkung]

cool, problem fixed.

Thanks all.