05 – Switchboard Forms – Calling Functions

In this post, we will be showing you how to call a function from a Swtichboard.

Being able to call code from a Switchboard is very useful as it gives us the flexibility to determine how we open our forms and reports. In the video, we show you how to use a filter to open up the Supplier’s form. This enables us to only view records that are relevant to what we are looking for and is a much more efficient way of working.

Please download 05_dbTedsTranslations_1

Code Behind The Filter

Private Sub cmdSubmit_Click()
On Error Resume Next
Dim strFilter As String
Dim strFilterType As String
Dim strSql As String
'loop through all the combo-boxes and create a simple filter

strFilter = ""

Dim ctrl As Control
For Each ctrl In Me.Controls
    If ctrl.ControlType = acComboBox Then
        If Not IsNull(Controls(ctrl.Name).Value) Then
            'string or integer
            If IsNumeric(Controls(ctrl.Name).Value) Then
                strFilter = strFilter & "[" & ctrl.Name & "]=" & _
                    Controls(ctrl.Name).Value & " AND "
            Else
                strFilter = strFilter & "[" & ctrl.Name & "]='" & _
                    Controls(ctrl.Name).Value & "' AND "
            End If
        End If
    End If
Next
strFilter = Left(strFilter, Len(strFilter) - 5)
strSql = "UPDATE settings SET Filters ='" & strFilter & "' WHERE ID=1"
CurrentDb.Execute strSql, dbFailOnError
DoCmd.Close acForm, "frmSuppliersFilter", acSaveNo
End Sub

Code For The Function

Public Function filterSupplier()
Dim strFilter As String
Dim strSql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
'open filter form. The form will compose a string that can be used as a filter
'the filter will be saved to settings>filters
DoCmd.OpenForm "frmSuppliersFilter", , , , , acDialog

'get filter
strFilter = Nz(DLookup("[Filters]", "settings"), "[ID] LIKE '*'")

'open up qrySuppliersExtended using the filter
strSql = "SELECT DISTINCT ID FROM qrySuppliersExtended WHERE " & strFilter

Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)

'set default filter
strFilter = ""

With rs
    'if the recordset is populated create a second filter...
    If Not .BOF And Not .EOF Then
        .MoveLast
        .MoveFirst
        
        strFilter = "[ID] IN("
        While (Not .EOF)
            strFilter = strFilter & .Fields("ID") & ", "
            .MoveNext
        Wend
        
        strFilter = Left(strFilter, Len(strFilter) - 2)
        strFilter = strFilter & ")"
    Else
        'if not records go to new supplier
        strFilter = "[ID] IN(-1)"
    End If
    .Close
End With

DoCmd.OpenForm "frmSuppliersDataEntry", , , strFilter

ExitSub:
    Set rs = Nothing
    Set db = Nothing

End Function

In this video, we have shown you how to use a Switchboard form to call a VBA function. This will help your Switchboard forms to be more dynamic and give you greater control over your database.