Object Required Error When Declaring String Variable

Question

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?

Bah, too stupid... After reading around a bit, I got the impression that it was too few set that got you into problems... Worked fine, if you rewrite it as an answer, I can accept it. — MortenSickel
– MortenSickel, Commented Jan 21, 2016 at 18:57
Answered. If you edit your title to something like "Object Required Error When Declaring String Variable", it may be more easily searchable. Just a thought ... — Scott Holtzman
– Scott Holtzman, Commented Jan 21, 2016 at 19:01

Scott Holtzman · Accepted Answer · 2016-01-21 18:59:55Z

10

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.

answered Jan 21, 2016 at 18:59

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Object Required Error When Declaring String Variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related