March 28, 2010 at 2:31 am
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?
Thanks
Eric
March 29, 2010 at 2:37 am
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é
March 29, 2010 at 3:13 am
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.
March 29, 2010 at 3:41 am
Interesting! Do you think a dynamic pivot form would do the trick?:unsure:
March 29, 2010 at 4:49 am
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...
March 29, 2010 at 2:40 pm
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)
Else
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)
Else
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
Else
Set c = CreateControl(fnew, acTextBox, acFooter, , "", n, 25, cwidth, cheight)
If InStr(f.NAME, "RERP") > 0 Then
c.Visible = False
Else
c.ControlSource = "=SUM([" & f.NAME & "])"
c.TextAlign = 2 'center
End If
End If
n = n + (cwidth + 4)
Next
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
E:
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
''''''''''''''
March 30, 2010 at 2:29 am
I like the idea! I'll have to try that
How was the performance?
March 30, 2010 at 6:55 am
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.
March 30, 2010 at 8:47 am
I'll have to give that a shot.
Thanks 😛
March 30, 2010 at 9:12 am
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 😀
March 30, 2010 at 9:15 am
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