Populating an MS Access 2003 form dynamically?

  • How feasible is it to create the content of a form dynamically in MS Access?

    The reason for asking this is as follows.

    I have a MS Access 2003 Project as a front-end to an Sql Server 2005 database/application.

    Most the logic reside in sql server.

    I would like to be able to deploy "reports" (aka simple front-end to a TSQL Stored Procedures) without having to redeploy the Access front-end.

    To do that, I'd have 2 tables describing the "report" and its parameters and amend my Access form on the fly to show the parameters relevant to the report.

    Is this asking too much to MS Access?



  • Hi,

    If I understand correctly you want to create the controls on a form on the the fly using some "meta" data about the form from a table?

    Check this: http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=259

    This explains how to create a dynamic report: http://bytes.com/topic/access/insights/696050-create-dynamic-report-using-vba

    Another one from MS: http://support.microsoft.com/default.aspx?scid=kb;en-us;185774

    An example I found:

    ' Creates a text box of the name "tbxTest" using the control source "txtTest"

    ' The text box is 1 in wide, 2 inches high, placed 0.5 inches from the

    ' top and left of the detail

    Private Sub cmdMakeTBX_Click()

    Const TWIPS As Integer = 1440 ' text box dimensions are in twips

    Dim ctl As Control

    Dim intLeft As Integer

    Dim intTop As Integer

    Dim intWidth As Integer

    Dim intHeight As Integer

    intLeft = 0.5 * TWIPS

    intTop = 0.5 * TWIPS

    intWidth = 1 * TWIPS

    intHeight = 2 * TWIPS

    DoCmd.OpenForm "frmMyForm", acDesign

    Set ctl = CreateControl("frmMyForm", acTextBox, acDetail, , "txtDesc1", intLeft, intTop, intWidth, intHeight)

    ctl.Name = "tbxTest"

    DoCmd.Close acForm, "frmMyForm", acSaveYes

    End Sub

    I general it is possible but depending on the detail requirement it can be tricky πŸ˜‰

    Cheers RenΓ©

  • Actually that was not exactly what I had in mind but I might be able to use your idea too...

    I was more thinking of building a page capable of adapting itself to various reports rather than building it before hand.

  • Interesting! Do you think a dynamic pivot form would do the trick?:unsure:

  • My MS Access knowledge dates back from 2000... so I might need to refresh a little bit.

    I need to look into the Pivot form.

    To display the result of the report (aka Stored Proc) I could probably have a set of Text controls ready for use and make the right number visible and change their bound control.

    I suspect my biggest problem is how to display a diverse set of parameters, some of which could even be combo boxes..

    On the other hand, the business side is relatively simple so far so maybe I could work out the max number of parameters and types and have a set of controls ready for use too...

  • this should give you some hints. This is a function that I wrote several years ago to dynamically create a form based on a cross-tab query. written for Access 2002 I think.


    'create and display a form for the crosstab query - we have to do it dynamically to

    ' allow for the fields to change each time the query is run

    Function ShowCrossTab()

    Dim rs As Recordset, c As Control, f As Field, fm As Form, n&

    Const cheight = 200

    Dim cwidth%

    On Error GoTo E

    Set rs = CurrentDb.QueryDefs("qfStudentQueriesCrossTab").OpenRecordset

    'because some queries may result in a large # of columns,

    ' we have to make sure they fit in the 22" max width of an access form

    If rs.Fields.Count > 38 Then

    cwidth = 31680 \ (rs.Fields.Count + 1)


    cwidth = 900

    End If

    Set fm = CreateForm

    'the form draws funny on big queries if we set the source here

    ' fm.RecordSource = "qfStudentQueriesCrossTab"

    fnew = fm.NAME

    DoCmd.SelectObject acForm, fnew

    fm.Caption = "Student Count Grid"

    fm.PopUp = True

    fm.Modal = True

    fm.DefaultView = 1 '2

    fm.ViewsAllowed = 1 '2 1= form, 2=datasheet

    fm.RecordsetType = 2 ' snapshot

    'fm.RecordSelectors = False

    fm.MinMaxButtons = 2 'maximum only

    fm.CloseButton = True

    fm.AutoCenter = True

    'this seems to be the only way to dynamically add header/footer to the form

    DoCmd.RunCommand acCmdFormHdrFtr

    fm.Section(acHeader).Visible = True

    fm.Section(acFooter).Visible = True

    fm.Section(acDetail).Height = cheight + 5

    fm.Section(acHeader).Height = cheight + 5

    fm.Section(acFooter).Height = cheight + 40

    fm.DividingLines = False

    'step through each field in the dataset and create a lable in the form header

    For Each f In rs.Fields

    Set c = CreateControl(fnew, acLabel, acHeader, , "", n, 2, cwidth, cheight)

    If Mid$(f.NAME, 4, 1) = "_" Then

    c.Caption = Right$(f.NAME, Len(f.NAME) - 4)


    c.Caption = f.NAME

    End If

    c.FontWeight = 600

    c.Width = cwidth

    c.TextAlign = 2 'center

    'create the textboxes in the detail section

    Set c = CreateControl(fnew, acTextBox, acDetail, , f.NAME, n, 2, cwidth, cheight)

    c.TextAlign = 2

    'this is a field we don't want to total, so use it as a lable

    'otherwise create textboxes for totals

    If InStr(f.NAME, "MSFN") > 0 Then

    Set c = CreateControl(fnew, acLabel, acFooter, , "", n, 25, cwidth, cheight)

    c.Caption = "Totals:"

    c.FontWeight = 600

    c.Width = cwidth


    Set c = CreateControl(fnew, acTextBox, acFooter, , "", n, 25, cwidth, cheight)

    If InStr(f.NAME, "RERP") > 0 Then

    c.Visible = False


    c.ControlSource = "=SUM([" & f.NAME & "])"

    c.TextAlign = 2 'center

    End If

    End If

    n = n + (cwidth + 4)


    Set c = Nothing

    Set rs = Nothing

    DoCmd.SetWarnings False

    'save/close the form then display and resize it

    DoCmd.Close acForm, fnew, acSaveYes

    DoCmd.OpenForm fnew ', acFormDS - datasheet can't be pop-up modal

    Forms(fnew).InsideHeight = (25 * Forms(fnew).Section(acDetail).Height) + Forms(fnew).Section(acHeader).Height

    Forms(fnew).RecordSource = "qfStudentQueriesCrossTab"

    DoCmd.SetWarnings True

    Exit Function


    If IsFormOpen(fnew) Then

    DoCmd.SetWarnings False

    DoCmd.Close acForm, fnew, acSaveNo

    DoCmd.SetWarnings True

    End If

    errCatch "ShowCrossTab()", "Form could not be created"

    End Function


  • I like the idea! I'll have to try that

    How was the performance?

  • I didn't put any counters on it but the program is still in use and the form definately opens sub 2 secs.

    edit: I guess I should also say that the cross-tab query is build dynamically with several different arguments possible for the where clause - the unpredicability of the number of columns was the reason I had to take this route.

  • I'll have to give that a shot.

    Thanks πŸ˜›

  • not a problem... I've been lurcking in this forum for quite awhile and people usually beat me to the draw when I see a post I can actually contribute to πŸ˜€

  • I feel very much the same...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply