Auto generated / flexible controls in a form

[agust]

Can controls in a form be made flexibly or auto-generated based on parameters set in another form?

Supposed I have a continuous form with 10 textboxes, while at some other time I need to have 12 textboxes or 5 textboxes only. Total textboxes needed would be set from another form. The maximum number of textboxes should be unlimited. Is it possible?

The backend table actually can be created flexibly using SQL based commands. And to link the fields to the textboxes is another problem.

Any clue?

 

[Uncle Gizmo]

What you're proposing isn't possible, however the general solution is to use combo boxes or list boxes or a sub form. I should have a look at those and see if they will do....

 

[agust]

I am developing a database with monthly records of aggregated data of diagnosed cases handled in health center by age group (not individual). So the table defined by ID of Health Center, month, year, ID of diagnose, ID of age group and number of cases found. Age group is developed with ID to anticipate that in the future there might be changes of age-grouping.

The problem is the front-end should contains the fields/textboxes of ID of diagnose and number of cases found on each group in one row. For example:
Diagnose Case Found on 0-10 yr old Case Found on 11 - 20 yr old ...
Influenza 5 3

The plan is to use a flexible temporary table (defined with SQL) and flexible form for the data entry. After updated, the data will be stored from the temporary table to the real table.

 

[allan57]

Hi August, I use the following function to create a dynamic sub form based on a table structure, which again is created on the fly.

Public Function CreateSubFormBasedOnExportData()

Dim frm As Form

Dim ctlText As Control

dim dbs as database
Dim rstGetFieldDefinitions As Recordset

Dim intRecordcounter As Integer
Dim intFieldColumnWidth As Integer

Dim strGetFormName As String
Dim strGetDataOrderCriteria As String

Const intTwips As Integer = 567

On Error Resume Next

Set dbs = currentdb

Application.Echo False

DoCmd.DeleteObject acForm, "1_" & strHoldTableName

Set frm = CreateForm(, "1_" & strHoldTableName)

frm.RecordSource = "1_" & strHoldTableName
frm.Caption = "1_" & strHoldTableName
frm.AllowAdditions = False
frm.AllowDeletions = False
frm.AllowEdits = False
frm.DefaultView = 2
frm.ViewsAllowed = 2
frm.DataEntry = False
frm.ScrollBars = 0
frm.RecordSelectors = False
frm.NavigationButtons = False
frm.DividingLines = False
frm.AutoResize = True
frm.AutoCenter = True
frm.PopUp = False
frm.Modal = False
frm.BorderStyle = 0
frm.ControlBox = False
frm.MinMaxButtons = 0
frm.CloseButton = False

Set rstGetFieldDefinitions = dbs.OpenRecordset("SELECT Application_Get_Field_Data_Export.txtField_Name, Application_Get_Field_Data_Export.txtField_Type, Application_Get_Field_Data_Export.intField_Length, Application_Get_Field_Data_Export.fExport_Field_Data " & _
"FROM Application_Get_Field_Data_Export " & _
"WHERE Application_Get_Field_Data_Export.fExport_Field_Data=True " & _
"ORDER BY Application_Get_Field_Data_Export.intExport_Field_Sequence_Order;")

rstGetFieldDefinitions.MoveLast
rstGetFieldDefinitions.MoveFirst

For intRecordcounter = 1 To rstGetFieldDefinitions.RecordCount

If intRecordcounter < 4 Then

strGetDataOrderCriteria = strGetDataOrderCriteria & rstGetFieldDefinitions!txtField_Name & "],"

End If

Set ctlText = CreateControl(frm.Name, acTextBox, acDetail, , rstGetFieldDefinitions!txtField_Name, 0, 0, 0, 0)

ctlText.Name = rstGetFieldDefinitions!txtField_Name
ctlText.Locked = True

If rstGetFieldDefinitions!intField_Length < Len(rstGetFieldDefinitions!txtField_Name) Then

intFieldColumnWidth = Len(rstGetFieldDefinitions!txtField_Name)

Else

intFieldColumnWidth = rstGetFieldDefinitions!intField_Length

End If

If intFieldColumnWidth > 30 Then intFieldColumnWidth = 30

ctlText.Properties("ColumnWidth") = (intFieldColumnWidth / 4) * intTwips

rstGetFieldDefinitions.MoveNext

Next intRecordcounter

Echo True

strGetFormName = frm.Name

frm.OrderBy = "[" & Left(strGetDataOrderCriteria, Len(strGetDataOrderCriteria) - 1)
frm.OrderByOn = True

DoCmd.Save acForm, strGetFormName

DoCmd.Close acForm, strGetFormName

DoCmd.Rename "1_" & strHoldTableName, acForm, strGetFormName

End Function

 

[agust]

Hi Allan!

Thanks for the hints! Actually I have not tried it yet, but I already checked the CreateForm and CreateControl function. So I am convinced that it is possible to create dynamic form and controls on the fly. I should have digged deeper in the Access documentation, because it is there!