Anything Access

When working on multiple tables to create simple Access UI design forms the process to create each UI can become tiresome.

To simplify the process I now take all the fields from the table and place them on the form in the desired position and then call the following code on open event.

---

Access UI Design Code Snippet

On Error Resume Next 
' I know its lazy but for this particular purpose its reasonable as I dont want fail 
' notifications when they are expected

Dim tmpTag, tmpSource
tmpTag = Split(Me.Tag, ";") ' I tag the form with the name of the table required for the Access UI Design
tmpSource = Me.Tag

Call colCtrlReq(Me.Form, "REPEATEDNAME_", 8, "Segoe UI Semibold", "Segoe UI")

Call CheckEnable(Me.Form)
Call SetEmptyCombo(Me.Form, tmpTag(0))

The first function colCtrlReq sets the font size , removes the underscores on the labels and removes the repeated table name from fields. For example if this was a supplier table , a field might be called SUPPLIER_NAME – on the UI we only want Name as we know we are dealing with the given table Supplier.

It puts the text in proper case and sets the fint and colour depending on the object type.

Public Sub colCtrlReq(frm As Form, tmpRemove As String, tmpFontSize, tmpFontLabel, tmpFontOther)
On Error Resume Next
'Access UI Design
Dim ctl As control, setColour
setColour = vbBlack
For Each ctl In frm
        If InStr(1, ctl.Tag, "Heading", vbTextCompare) = 0 Then ' ignore if tagged as heading
            'If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
            If ctl.ControlType = acLabel Then
                ctl.ForeColor = setColour
                ctl.Caption = StrConv(Replace(Replace(ctl.Caption, tmpRemove, "", 1), "_", " "), vbProperCase)
                ctl.FontName = tmpFontLabel
                ctl.FontSize = tmpFontSize
            ElseIf ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
                ctl.ForeColor = setColour
                ctl.FontName = tmpFontLabel
                ctl.FontSize = tmpFontSize
            End If
        End If
Next ctl
Set ctl = Nothing
End Sub

The function CheckEnable will search a table for any fields which are enabled and set a colour and lock the field if the user cant make any changes.

Public Sub CheckEnable(frm As Form, Optional tmpCheckRequired)
On Error Resume Next
'Access UI Design
Dim ctl As control, setColour, rst As Recordset, tmpTag
If IsMissing(tmpCheckRequired) Then
    tmpCheckRequired = True
End If
tmpTag = Split(frm.Tag, ";")
setColour = 12713215
Set rst = CurrentDb.OpenRecordset("select * from tblEnabledControls where formname='" & frm.Name & "'")
If rst.RecordCount = 0 Then
    GoTo SkipCheck
End If
rst.MoveFirst
Do While Not rst.EOF
    For Each ctl In frm
            If ctl.Name = rst!FieldNameonForm Then '
                If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
                    ctl.BackColor = setColour
                    ctl.TabStop = True
                    ctl.Locked = False
                    'ctl.Tag = ctl.Value
                    'moved to on current event
                End If
            End If
    Next ctl
    rst.MoveNext
Loop
SkipCheck:
If tmpCheckRequired Then
    Call CheckRequired(frm, tmpTag(0))
End If
Set rst = Nothing
Set ctl = Nothing
End Sub

The function SetEmptyCombo will set any combo box that has no values set to use the current values of the table. It will use the field name and take the table name from the tag on the form.

Public Sub SetEmptyCombo(frm As Form, tmpTag)
'Access UI Design
On Error Resume Next
Dim ctl As control
For Each ctl In frm
        If ctl.ControlType = acComboBox Then
            If ctl.RowSource = "" Then
                ctl.RowSource = "Select " & ctl.Name & " From " & tmpTag & " Group by " & ctl.Name & " Having " & ctl.Name & " is not null Order by " & ctl.Name
            End If
        End If
Next ctl
Set ctl = Nothing
End Sub

In addition we can add detials to the lable to define the data type and a symbol to define whether the field is a required field. This is called from the check enable function.

Public Sub CheckRequired(frm As Form, tmpTag)
'Access UI Design
On Error Resume Next
Dim ctl As control, setColour, rst As Recordset
setColour = 12713215
Set rst = CurrentDb.OpenRecordset("select * from tblCreateTable where Table_Name='" & tmpTag & "'")
If rst.RecordCount = 0 Then
    MsgBox "No Setting found for form tag :" & tmpTag
    Exit Sub
End If
rst.MoveFirst
Do While Not rst.EOF
    For Each ctl In frm
        If ctl.Name = rst!Column_name Then
            ctl.Controls(0).Caption = ctl.Controls(0).Caption & " " & IIf(rst!Nullable = "N", ChrW(&H2713), "") & GetShortType(rst!Data_Type)
            If rst!Nullable = "N" Then ' set the colour
                ctl.Controls(0).BackColor = 14079228
            End If
            If Len(rst!Data_Default & "") > 0 Then
                ctl.ControlTipText = Replace(Replace(rst!Data_Default, Chr(39), ""), Chr(34), "")
                ctl.Controls(0).Caption = ctl.Controls(0).Caption & " !D"
                ctl.StatusBarText = "Default Value for a new record  :" & Replace(Replace(rst!Data_Default, Chr(39), ""), Chr(34), "")
            End If
            GoTo Skip ' we found our value no need to loop the remaining form controls
        End If
    Next ctl
Skip:
    rst.MoveNext
Loop

Set ctl = Nothing
End Sub

Links

Other Access Posts on this site

Using the Access form Wizard