In my Access 2010 database there are 6 tables:
tblSelectBox1 - options to be shown in selectbox1 on the main form
tblSelectBox2 - options to be shown in selectbox2 on the main form
tblSelectBox3 - options to be shown in selectbox3 on the main form
tblFormFields - a table that holds a record for each available field on the form, except for the select boxes - these are references to the form fields in tblData
tblForms - a table that contains one records for each possible form combination that can be derived based upon the combinations of select boxes
tblData - where the final data is stored from all the form field combinations
I am creating a database that will contain three select boxes on a form. On the form, there will also be a matching form field for every entry in tblFormFields, that is initially set to frm.visible = false. I then set the form field Tag Property value to match that of the IDFormField autonumber in tblFormFields.
When the user selects a value in the first box, it changes the options in second select box, and similarly when a user selects a value in the second box, the options in the third box change. There values in the select boxes are populated by the tblSelectBox1, tblSelectBox2, and tblSelectBox3, by using a ParentID field. This is all working fine.
Once a value has been selected in the third select box, a “Create Form” button appears. This button will then run a subroutine that looks at the value in the third select box. Based upon that value, a lookup will be done in tblForms. In tblForms, there will be a field called strFormFieldTagValue that will contain all of the form fields that should be shown on the form, by the Tag Property value, separated by a semicolon. A subroutine will then run, looping through those values, and making only those form fields VISIBLE.
All of this is working fine; however, if there are 50 fields on a form (25 in each column) and the select box combination is to display a form that uses, say, 6 form fields, when these 6 fields are made visible, they are almost always gaurenteed to be displayed randomly across the form. This being said, my question is:
Is there a way to put form fields into some sort of container and have only the visible ones move to the top of the container? This would be similar to when displaying object on a webpage and using display = none or block or inline, vs. visibility = hidden or visible. I hope I’ve given a fairly clear explanation of what I’m trying to do.
One thought was to take the number of form fields to be displayed and divide by two, to determine the number to display in each column, and then place them in a container (a Rectangle object) and try to place them by X,Y coordinates. But some fields will be different types and sizes, so I won’t be able to calculate where to place them.