Solved

Dynamically Move Form Fields in Access?

Posted on 2011-09-18
5
470 Views
Last Modified: 2012-06-21
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.

Any suggestions?
0
Comment
Question by:EndOfLine
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 250 total points
ID: 36559149
You could try using Create form button to reset the top and left values of each selected control - you cm each - or some other might left aligh all at say 1 cm and increment the top values by 1 cm or some other appropriate value.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 36560281
FWIW,....

Not relay a solution but some thoughts...

Whenever a question this "complex" comes up, the question becomes...
Why is the system designed in this way?
Usually a design like this is indicative of a an even deeper design issue.

Under normal circumstances you should not have to go through all of these machinations just to create a workable form.

You have to admit, ...if you read your own post from an outsiders point of view, it seems very convoluted...

Again, not a criticism, just an observation.

What is your skill level in MS Access, database deign, normalization, form design, VBA, ...etc?
I see bu this statement , that you have a web background:
<This would be similar to when displaying object on a webpage and using display = none or block or inline, vs. visibility = hidden or visible.>
But Access form design an vba are a totally different animal than Web forms.

You could investigate creating controls on the fly (in design time), but this adds even more complexity...

If it seems like it would help, I did something similar in a report system where certain fields were selected to be displayed based on checkboxes.
The report would actually contain all the fields, (and the order of the fields could not be changed), but only the selected fields would be visible.

It used logic to "shrink and move over" controls that were not needed.
But again, this will hide/display fields, not "move" them.

JeffCoachman
0
 

Author Comment

by:EndOfLine
ID: 36571673
I just ended up creating multiple forms and loading them into the main form as a Subform. Thanks, all, for your feedback and suggestions.
0
 

Author Closing Comment

by:EndOfLine
ID: 36595675
Seems there is no complete solution to this problem.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36596799
You can see these samples for something similar I did a while ago,
So you see something like this can be done, just not in th same way as it is in a web form
Access-EEQ24166542ExpandCollapse.mdb
Access--EEQ23106366SelectHideSho.mdb
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question