Category: vba

MS Access: when was my form changed? Is ServerFilter set?

What forms have I changed recently?

Sub CheckForms()
    'checks forms in db


    Dim s As String
    Dim d0 As Date
    
    d0 = DateAdd("d", -7, Date)
    
    Dim ao As AccessObject
    
    s = "Forms modified in past 7 days:" & vbCrLf

    For Each ao In Application.CurrentProject.AllForms
        If (ao.DateModified > d0) Then
            s = s & ao.Name & " modified " & ao.DateModified & vbCrLf
        End If
    Next
    MsgBox s
End Sub

What forms have ServerFilter set? You can accidentally leave this set to a value if you make a small change to the design of the form. Write a sub to check your forms before you publish.

For Each ao In Application.CurrentProject.AllForms
        fn = ao.Name

        DoCmd.openForm fn, acDesign
        Set f = Forms(fn)
        filt = "" & f.Properties("ServerFilter")
        If Len(filt) > 0 Then
            s = s & fn & " Server Filter " & filt & vbCrLf
        End If
        DoCmd.Close acForm, fn

    Next

VBA function to join strings

Building SQL in VBA? BAD programmer. BAD. me too.

'join strings using joiner. a can be list of string params, or a single collection, or a single array
Function Join(joiner As String, ParamArray a() As Variant) As String
    'examples
    'w = join(" and ","customer=12","color=red")
    'w=join(" and ",array("length=3","width=2"))
    'dim col as new collection: col.add("height=77")
    'col.add("material=steel")
    'w=join(" and ",col)

    Dim s As String
    Dim i As Integer
    Dim c As Variant

    If Not IsArray(a) Then
        s = ""
    ElseIf UBound(a) = 0 Then
        If TypeOf a(0) Is Collection Or IsArray(a(0)) Then
            For Each c In a(0)
                If Len(c) > 0 Then
                    If Len(s) > 0 Then s = s & joiner
                    s = s & c
                End If
            Next
        Else
            s = ""
        End If
    Else
        For i = LBound(a) To UBound(a)
            If TypeOf a(i) Is Collection Then
                For Each c In a(i)
                    If Len(c) > 0 Then
                        If Len(s) > 0 Then s = s & joiner
                        s = s & a(i)
                    End If
                Next
            ElseIf Len(a(i)) > 0 Then
                If Len(s) > 0 Then s = s & joiner
                s = s & a(i)
            End If
        Next i
    End If
    Join = s

End Function

Sub TestJoin()
    Dim s As String
    Dim t As String
    Dim arr As New Collection
    Dim a As Variant

    t = "apples,pears,oranges"

    a = Split(t, ",")

    s = Join(",", Split(t, ","))
    If s <> t Then
        MsgBox "the sword has not been mended"
    End If

    s = Join(" ", "hello", "access", "world")
    If s <> "hello access world" Then
        MsgBox "I'm sorry Dave I can't do that"
    End If

    arr.Add "hello"
    arr.Add "access"
    arr.Add "world"

    s = Join(" ", arr)
    If s <> "hello access world" Then
        MsgBox "i only wanted to say hello"
    End If

End Sub