Create Datesheet based on Any Query or Table (1 Viewer)

[MLUCKHAM]

I am not sure if this is the right place for this post, but I wanted to share some cool code that I use a lot in my Access apps.

Almost every app I write the users need some form of on screen report. Normally they want loads of them and cranking out a way of displaying the queries so that I can control user access can be a pain. I normally use a two form set-up - one master form and then a sub-form set in a datasheet mode that displays the query result set.

Anyway, I wrote the below function which will take any table or query, create a subform and return the new form name to the calling procedure.

You can then extend this functionality by providing on the master form a drop list of all queries or tables. The user can then select a query from the list and "run it" producing the form for editing or readOnly viewing.

One other thing - the function needs a form template. This is a blank form, but it needs its default display set to Datasheet Only. In my function it is called "__TemplateDataSetForm".

Public Function CreateDataModificationForm(ByVal ObjectName As String, ByVal ObjectType As String) As String
Dim Tbl As TableDef
Dim fld As Field
Dim ctl As Control
Dim frm As Form

    Set frm = CreateForm(, "__TemplateDataSetForm")
    
    frm.RecordSource = ObjectName
    
    If ObjectType = "Table" Then
        For i = 0 To CurrentDb.TableDefs(ObjectName).Fields.Count - 1
            If CurrentDb.TableDefs(ObjectName).Fields(i).Type = 1 Then ' Boolean type - use checkbox
                Set ctl = CreateControl(frm.Name, acCheckBox, acDetail, , CurrentDb.TableDefs(ObjectName).Fields(i).Name)
            Else
                Set ctl = CreateControl(frm.Name, acTextBox, acDetail, , CurrentDb.TableDefs(ObjectName).Fields(i).Name)
            End If
            ctl.Name = CurrentDb.TableDefs(ObjectName).Fields(i).Name
        Next i
    Else
        For i = 0 To CurrentDb.QueryDefs(ObjectName).Fields.Count - 1
    
           Set ctl = CreateControl(frm.Name, acTextBox, acDetail, , CurrentDb.QueryDefs(ObjectName).Fields(i).Name)
            ctl.Name = CurrentDb.QueryDefs(ObjectName).Fields(i).Name
        Next i
    End If
    
    CreateDataModificationForm = frm.Name
    DoCmd.Close acForm, frm.Name, acSaveYes

End Function