CODEWITHSUNDEEP
vbams-access
MortenSickel
MortenSickel

Reputation: 2200

Object Required Error When Declaring String Variable

I am fetching a set of names from a database query and then reformatting it to a comma separated list. As I am using this functionallity a few Places in my app, I try to write it as a function getting the sql-query and returning the string. 

Public Function String_from_query(StrSQL As String) As String

 Dim dbs As Database
 Dim rs As Recordset
 Set dbs = CurrentDb
 Dim results As String
 results = ""
 Set rs = dbs.OpenRecordset(StrSQL)
 If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do While Not rs.EOF
        If results > "" Then
            results = results & ", "
        End If
        results = results & rs("Navn")
        rs.MoveNext
    Loop
 End If
 Set String_from_query = results
End Function

This is then called from an event handler:

Private Sub Detalj_Format(Cancel As Integer, FormatCount As Integer)
 Dim StrSQL As String
 StrSQL = "SELECT Personer.Navn FROM Personer INNER JOIN Personoppgaver ON Personer.Initialer = Personoppgaver.Initialer WHERE Personoppgaver.Oppgaveid =" & Me.Oppgaveid.Value
 Me.Tekst52.Value = String_from_query(StrSQL)
End Sub

If I have the code from the String_from_query function within the event handler and then directly assigns Me.Tekst52 to results, everything works fine. When I refactor the code as shown, I get a "Compile Error, Object required" when I try to run it and a marker on the last line in the sub. (Set String_from_query = results). I am not able to see what is wrong. Any help?

Upvotes: 4

Views: 9797

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

The keyword Set is only required when assigning variables to an Object. For Access, this would be Forms, Reports, Recordsets, etc. Or other Objects outside of Access (FileSystemObject, for example).

When setting strings, dates, numbers, etc, you do not need Set.

You can surmise this from the error message as well, Object required.

Upvotes: 10

Related Questions